Calculate Product Final Price - Practice Coding Problems

Calculate Product Final Price - Problem

Database Medium

You're building a smart pricing system for an e-commerce platform! 🛒

Given two database tables - Products and Discounts - you need to calculate the final price of each product after applying category-based discounts.

Your mission:

🔍 Join products with their category discounts
💰 Calculate final price = original price - (discount% × original price)
🏷️ Handle products with no discounts (price remains unchanged)
📊 Return results ordered by product_id

Tables Structure:

Products: Contains product_id (unique), category, and original price
Discounts: Contains category (primary key) and discount percentage (0-100)

Think of it like a smart cashier that automatically applies the right discount based on what category each item belongs to!

Input & Output

example_1.sql — Basic Scenario

$ Input: Products: [(1,'Electronics',1000), (2,'Clothing',50), (3,'Electronics',1200), (4,'Home',500)] Discounts: [('Electronics',10), ('Clothing',20)]

› Output: [(1,900,'Electronics'), (2,40,'Clothing'), (3,1080,'Electronics'), (4,500,'Home')]

💡 Note: Electronics gets 10% off (1000→900, 1200→1080), Clothing gets 20% off (50→40), Home has no discount (500→500)

example_2.sql — No Discounts Available

$ Input: Products: [(1,'Books',25), (2,'Sports',100)] Discounts: []

› Output: [(1,25,'Books'), (2,100,'Sports')]

💡 Note: When no discounts exist, all products retain their original prices

example_3.sql — All Products Have Discounts

$ Input: Products: [(1,'Electronics',2000), (2,'Clothing',80)] Discounts: [('Electronics',25), ('Clothing',15)]

› Output: [(1,1500,'Electronics'), (2,68,'Clothing')]

💡 Note: All categories have matching discounts: Electronics 25% off (2000→1500), Clothing 15% off (80→68)

Constraints

1 ≤ number of products ≤ 10⁴
0 ≤ number of discount categories ≤ 100
1 ≤ product_id ≤ 10⁶
1 ≤ price ≤ 10⁶
0 ≤ discount ≤ 100 (percentage)
Category names are non-empty strings with max length 50
Each product_id is unique
Each discount category appears at most once

Visualization

Tap to expand

Understanding the Visualization

Customer's Cart

Products from different categories arrive at checkout

Discount Lookup

System checks if each category has an active discount

Price Calculation

Applies discount percentage to original price

Final Receipt

Shows final prices ordered by product ID

Key Takeaway

🎯 Key Insight: LEFT JOIN is perfect for optional relationships - every product gets processed, but discounts are applied only when available!

Asked in

a Amazon 45 S Shopify 38 e eBay 32 W Walmart 28

The optimal solution uses LEFT JOIN to efficiently combine Products and Discounts tables. Key insight: LEFT JOIN ensures all products appear in results, even those without discounts. Use COALESCE to handle NULL discounts and calculate final price as price × (1 - discount/100).

Common Approaches

Approach	Time	Space	Notes
✓ Nested Subqueries (Brute Force)	O(n×m)	O(1)	Use correlated subquery to find discount for each product individually
LEFT JOIN (Optimal)	O(n + m)	O(n)	Use SQL LEFT JOIN to efficiently match products with discounts in one operation

Nested Subqueries (Brute Force) — Algorithm Steps

For each product, run a subquery to find matching discount
Use COALESCE to handle missing discounts (default to 0)
Calculate final price using discount percentage
Order results by product_id

Visualization

Tap to expand

Step-by-Step Walkthrough

Process Product 1

Query entire Discounts table to find Electronics discount

Process Product 2

Query entire Discounts table again to find Clothing discount

Process Product 3

Query Discounts table once more for Electronics (redundant!)

Process Product 4

Query Discounts table for Home category (not found)

Code -

solution.c — C

-- SQL Solution
SELECT 
    product_id,
    price * (1 - COALESCE(
        (SELECT discount FROM Discounts d 
         WHERE d.category = p.category), 0
    ) / 100.0) as final_price,
    category
FROM Products p
ORDER BY product_id;

/*
// Alternative C implementation
#include <stdio.h>
#include <stdlib.h>
#include <string.h>

typedef struct {
    int id;
    char category[50];
    double price;
} Product;

typedef struct {
    char category[50];
    int rate;
} Discount;

typedef struct {
    int id;
    double final_price;
    char category[50];
} Result;

int compare(const void *a, const void *b) {
    Result *ra = (Result *)a;
    Result *rb = (Result *)b;
    return ra->id - rb->id;
}

Result* calculateFinalPrice(Product* products, int productCount,
                           Discount* discounts, int discountCount) {
    Result* result = malloc(productCount * sizeof(Result));
    
    for (int i = 0; i < productCount; i++) {
        int discount = 0;
        
        // Find discount for this category
        for (int j = 0; j < discountCount; j++) {
            if (strcmp(discounts[j].category, products[i].category) == 0) {
                discount = discounts[j].rate;
                break;
            }
        }
        
        result[i].id = products[i].id;
        result[i].final_price = products[i].price * (1 - discount / 100.0);
        strcpy(result[i].category, products[i].category);
    }
    
    // Sort by product_id
    qsort(result, productCount, sizeof(Result), compare);
    return result;
}
*/

Time & Space Complexity

Time Complexity

⏱️

O(n×m)

For each product (n), we scan entire Discounts table (m)

✓ Linear Growth

Space Complexity

O(1)

No additional space needed, just temporary variables

✓ Linear Space

42.0K Views

High Frequency

~15 min Avg. Time

1.3K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Nested Subqueries (Brute Force) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler