Product Sales Analysis IV - Practice Coding Problems

Product Sales Analysis IV - Problem

Database Medium

You're building a customer analytics dashboard for an e-commerce platform! Given two database tables - Sales and Product - you need to identify each customer's most valuable product purchases.

Your mission: For each user, find the product(s) they spent the most money on. If a user spent equal amounts on multiple products, include all of them in your results.

Sales Table
Column	Type
sale_id	int (unique)
product_id	int
user_id	int
quantity	int

Product Table
Column	Type
product_id	int (unique)
price	int

Example: If User 1 spent $100 on laptops and $100 on phones (their highest amounts), both products should appear in the results.

Goal: Write a SQL query that returns user_id and product_id pairs representing each user's top spending products.

Input & Output

example_1.sql — Basic Case

$ Input: Sales: [(1,1,1,2), (2,2,1,1), (3,3,2,3)], Product: [(1,10), (2,25), (3,15)]

› Output: [(1,2), (2,3)]

💡 Note: User 1 spent $20 on product 1 and $25 on product 2 (maximum). User 2 spent $45 on product 3 (only product). Results show each user's highest spending product.

example_2.sql — Tie Case

$ Input: Sales: [(1,1,1,5), (2,2,1,2), (3,1,2,1)], Product: [(1,10), (2,25)]

› Output: [(1,1), (1,2), (2,1)]

💡 Note: User 1 spent $50 on product 1 and $50 on product 2 (tied for maximum). User 2 spent $10 on product 1. Both tied products for User 1 are included in results.

example_3.sql — Multiple Sales Same Product

$ Input: Sales: [(1,1,1,3), (2,1,1,2), (3,2,1,1)], Product: [(1,20), (2,30)]

› Output: [(1,1)]

💡 Note: User 1 has multiple sales for product 1: total spending = (3+2)*$20 = $100 on product 1, vs $30 on product 2. Product 1 has maximum spending.

Visualization

Tap to expand

Understanding the Visualization

Collect All Receipts

JOIN sales data with product prices to get complete purchase information

Calculate Customer Totals

Sum up how much each customer spent on each product type

Rank by Spending

For each customer, rank their products from highest to lowest spending

Extract Top Products

Select only the products with rank 1 (highest spending) for each customer

Key Takeaway

🎯 Key Insight: Window functions with PARTITION BY allow us to rank within groups efficiently, avoiding expensive correlated subqueries while handling ties automatically.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass through data with sorting for ranking within groups

⚡ Linearithmic

Space Complexity

O(n)

Space for intermediate results and ranking

⚡ Linearithmic Space

Constraints

1 ≤ sale_id, product_id, user_id ≤ 1000
1 ≤ quantity ≤ 100
1 ≤ price ≤ 1000
Each sale_id and product_id is unique in their respective tables
At least one sale exists for each user in the result

Asked in

a Amazon 45 G Google 38 f Meta 32 ⊞ Microsoft 28

The optimal solution uses window functions with RANK() OVER (PARTITION BY user_id ORDER BY spending DESC) to efficiently identify each user's top spending products. This approach handles ties automatically and processes data in a single pass, making it much faster than nested subqueries for large datasets.

Common Approaches

Approach	Time	Space	Notes
✓ Window Functions (Optimal)	O(n log n)	O(n)	Use RANK() window function to identify top products per user in single pass
Brute Force (Nested Subqueries)	O(n²)	O(n)	Calculate spending for each user-product, then use correlated subqueries to find maximum

Window Functions (Optimal) — Algorithm Steps

JOIN Sales and Product tables to access prices
Calculate total spending per user-product combination
Use RANK() OVER (PARTITION BY user_id ORDER BY spending DESC) to rank products
Filter results where rank = 1 to get top spending products

Visualization

Tap to expand

Step-by-Step Walkthrough

JOIN and Aggregate

Calculate total spending per user-product combination

Partition by User

Group data by user_id for ranking within each user

Rank by Spending

Assign ranks based on spending amount (DESC order)

Filter Rank 1

Select only products with highest spending per user

Code -

solution.c — C

/* SQL solution using window functions (optimal) */
char* query = """
WITH user_product_spending AS (
    SELECT 
        s.user_id,
        s.product_id,
        SUM(s.quantity * p.price) as total_spending
    FROM Sales s
    JOIN Product p ON s.product_id = p.product_id
    GROUP BY s.user_id, s.product_id
),
ranked_spending AS (
    SELECT 
        user_id,
        product_id,
        total_spending,
        RANK() OVER (
            PARTITION BY user_id 
            ORDER BY total_spending DESC
        ) as spending_rank
    FROM user_product_spending
)
SELECT user_id, product_id
FROM ranked_spending
WHERE spending_rank = 1
ORDER BY user_id, product_id;
""";

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Single pass through data with sorting for ranking within groups

⚡ Linearithmic

Space Complexity

O(n)

Space for intermediate results and ranking

⚡ Linearithmic Space

Constraints

1 ≤ sale_id, product_id, user_id ≤ 1000
1 ≤ quantity ≤ 100
1 ≤ price ≤ 1000
Each sale_id and product_id is unique in their respective tables
At least one sale exists for each user in the result

67.4K Views

High Frequency

~25 min Avg. Time

1.8K 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

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Window Functions (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler