Product Sales Analysis V - Practice Coding Problems

Product Sales Analysis V - Problem

Database Easy

Imagine you're building an analytics dashboard for an e-commerce platform! 📊 You have access to two crucial tables: Sales (tracking every transaction) and Product (containing pricing information).

Your mission is to calculate the total spending for each user by combining sales data with product prices. This is a common business intelligence task that helps companies understand customer purchasing behavior.

Tables:

Sales table contains:

sale_id: Unique transaction identifier
product_id: References the product bought
user_id: The customer who made the purchase
quantity: How many units were purchased

Product table contains:

product_id: Unique product identifier
price: Price per unit of the product

Goal: Calculate each user's total spending (quantity × price for all their purchases) and return results ordered by spending (descending), with ties broken by user_id (ascending).

Input & Output

example_1.sql — Basic Sales Analysis

$ Input: Sales: [[1,101,1,2],[2,102,1,1],[3,101,2,3]] | Product: [[101,10],[102,25]]

› Output: [[1,45],[2,30]]

💡 Note: User 1 spent: 2×10 + 1×25 = 45. User 2 spent: 3×10 = 30. Results ordered by spending DESC.

example_2.sql — Tie Breaking by User ID

$ Input: Sales: [[1,101,1,3],[2,102,2,1],[3,101,3,3]] | Product: [[101,10],[102,30]]

› Output: [[1,30],[3,30],[2,30]]

💡 Note: All users spent 30. Tie-breaking by user_id ASC: user 1, then 3, then 2 (if user 2 also had 30).

example_3.sql — Single User Multiple Products

$ Input: Sales: [[1,101,1,1],[2,102,1,2],[3,103,1,1]] | Product: [[101,15],[102,20],[103,5]]

› Output: [[1,60]]

💡 Note: User 1 bought multiple products: 1×15 + 2×20 + 1×5 = 60 total spending.

Visualization

Tap to expand

Understanding the Visualization

Data Collection

Sales transactions are recorded with product IDs and quantities

Price Lookup

JOIN with Product table to get current prices

Calculate Spending

Multiply quantity × price for each transaction

User Aggregation

GROUP BY user_id to sum total spending per customer

Ranking

ORDER BY spending DESC, user_id ASC for final results

Key Takeaway

🎯 Key Insight: SQL JOINs allow us to combine related data from multiple tables, while GROUP BY aggregates information per category, making complex business analytics queries both efficient and readable.

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

JOIN operation is O(n×m) where n,m are table sizes, plus O(k log k) for sorting where k is number of users

⚡ Linearithmic

Space Complexity

O(k)

Space for k unique users in the result set

✓ Linear Space

Constraints

1 ≤ sales.length ≤ 10⁵
1 ≤ products.length ≤ 10⁴
1 ≤ sale_id, product_id, user_id ≤ 10⁸
1 ≤ quantity ≤ 10³
1 ≤ price ≤ 10⁶
All product_ids in Sales table exist in Product table

Asked in

a Amazon 85 G Google 72 f Meta 68 ⊞ Microsoft 54 🍎 Apple 41

The optimal solution uses a SQL INNER JOIN to combine the Sales and Product tables, then GROUP BY user_id to aggregate spending per customer. The key insight is multiplying quantity by price and using proper ORDER BY with multiple criteria for tie-breaking.

Common Approaches

Approach	Time	Space	Notes
✓ Basic JOIN without Optimization	O(n log n)	O(k)	Simple JOIN and GROUP BY without considering performance optimizations

Basic JOIN without Optimization — Algorithm Steps

JOIN Sales table with Product table on product_id
Calculate spending for each sale (quantity × price)
GROUP BY user_id to aggregate spending per user
ORDER BY spending DESC, user_id ASC

Visualization

Tap to expand

Step-by-Step Walkthrough

Join Tables

Connect Sales and Product tables on product_id

Calculate Spending

Multiply quantity × price for each row

Group by User

Aggregate spending per user_id

Sort Results

Order by spending DESC, user_id ASC

Code -

solution.c — C

// SQL solution concept in C
#include <stdio.h>
#include <stdlib.h>

typedef struct {
    int userId;
    int spending;
} UserSpending;

int compare(const void* a, const void* b) {
    UserSpending* ua = (UserSpending*)a;
    UserSpending* ub = (UserSpending*)b;
    if (ua->spending != ub->spending) {
        return ub->spending - ua->spending;
    }
    return ua->userId - ub->userId;
}

UserSpending* solution(int salesData[][4], int salesSize, int productData[][2], int productSize, int* returnSize) {
    // This is a simplified version - in practice, you'd use hash tables
    UserSpending* result = malloc(salesSize * sizeof(UserSpending));
    *returnSize = 0;
    
    // Process sales (simplified logic)
    for (int i = 0; i < salesSize; i++) {
        int userId = salesData[i][2];
        int productId = salesData[i][1];
        int quantity = salesData[i][3];
        
        // Find product price
        int price = 0;
        for (int j = 0; j < productSize; j++) {
            if (productData[j][0] == productId) {
                price = productData[j][1];
                break;
            }
        }
        
        // Add to result (simplified - should handle duplicates)
        result[*returnSize].userId = userId;
        result[*returnSize].spending = quantity * price;
        (*returnSize)++;
    }
    
    qsort(result, *returnSize, sizeof(UserSpending), compare);
    return result;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

JOIN operation is O(n×m) where n,m are table sizes, plus O(k log k) for sorting where k is number of users

⚡ Linearithmic

Space Complexity

O(k)

Space for k unique users in the result set

✓ Linear Space

Constraints

1 ≤ sales.length ≤ 10⁵
1 ≤ products.length ≤ 10⁴
1 ≤ sale_id, product_id, user_id ≤ 10⁸
1 ≤ quantity ≤ 10³
1 ≤ price ≤ 10⁶
All product_ids in Sales table exist in Product table

89.2K Views

High Frequency

~15 min Avg. Time

2.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

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Basic JOIN without Optimization — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler