Product Sales Analysis II - Practice Coding Problems

Product Sales Analysis II - Problem

Database Easy

You're managing a product sales database for a growing e-commerce company! 📊 Your goal is to analyze sales data to understand which products are performing best by calculating the total quantity sold for each product across all years.

You have two tables to work with:

Sales Table: Contains sales records with columns for sale_id, product_id, year, quantity, and price. Each row represents a single sale transaction.

Product Table: Contains product information with product_id and product_name.

Your Task: Write a SQL query that calculates the total quantity sold for every product. The result should show product_id and the sum of all quantities sold for that product across all years.

💡 Think of this as creating a sales summary report that helps business analysts understand product performance at a glance!

Input & Output

example_1.sql — Basic Sales Data

$ Input: Sales table: | sale_id | product_id | year | quantity | price | |---------|------------|------|----------|-------| | 1 | 100 | 2008 | 10 | 5000 | | 2 | 100 | 2009 | 12 | 5000 | | 7 | 200 | 2011 | 15 | 9000 | Product table: | product_id | product_name | |------------|-------------| | 100 | Nokia | | 200 | Apple |

› Output: | product_id | total_quantity | |------------|----------------| | 100 | 22 | | 200 | 15 |

💡 Note: Product 100 (Nokia) sold 10 units in 2008 + 12 units in 2009 = 22 total units. Product 200 (Apple) sold 15 units in 2011.

example_2.sql — Multiple Years Same Product

$ Input: Sales table: | sale_id | product_id | year | quantity | price | |---------|------------|------|----------|-------| | 1 | 300 | 2020 | 5 | 1000 | | 2 | 300 | 2021 | 8 | 1000 | | 3 | 300 | 2022 | 12 | 1200 | | 4 | 400 | 2021 | 3 | 800 |

› Output: | product_id | total_quantity | |------------|----------------| | 300 | 25 | | 400 | 3 |

💡 Note: Product 300 has sales across three years: 5 + 8 + 12 = 25 total units. Product 400 has only one sale of 3 units.

example_3.sql — Single Product Multiple Sales

$ Input: Sales table: | sale_id | product_id | year | quantity | price | |---------|------------|------|----------|-------| | 1 | 500 | 2023 | 1 | 100 | | 2 | 500 | 2023 | 1 | 100 | | 3 | 500 | 2023 | 1 | 100 |

› Output: | product_id | total_quantity | |------------|----------------| | 500 | 3 |

💡 Note: Product 500 has three separate sales in the same year, each for 1 unit, totaling 3 units.

Visualization

Tap to expand

Understanding the Visualization

Scan Sales Records

Go through each sales record in the database table

Group by Product

Organize records into groups based on product_id

Sum Quantities

Add up all quantity values within each product group

Return Results

Output product_id and total_quantity for each group

Key Takeaway

🎯 Key Insight: GROUP BY with SUM() is the most efficient way to aggregate data in SQL, leveraging database optimizations for superior performance compared to manual approaches.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all sales records, database handles grouping efficiently

✓ Linear Growth

Space Complexity

O(k)

Space for k distinct product groups, typically much smaller than total records

✓ Linear Space

Constraints

1 ≤ sales table rows ≤ 1000
1 ≤ product table rows ≤ 100
1 ≤ product_id ≤ 100
2000 ≤ year ≤ 2023
1 ≤ quantity ≤ 10⁴
1 ≤ price ≤ 10⁶
Primary key constraint: (sale_id, year) is unique in Sales table

Asked in

a Amazon 45 ⊞ Microsoft 35 G Google 28 f Meta 22

The optimal solution uses GROUP BY with SUM() aggregation to efficiently calculate total quantities for each product in a single table scan. This approach leverages SQL's built-in aggregation capabilities to achieve O(n) time complexity while being database-optimized and following standard SQL patterns.

Common Approaches

Approach	Time	Space	Notes
✓ GROUP BY Aggregation (Optimal)	O(n)	O(k)	Use GROUP BY with SUM to aggregate quantities efficiently in one pass
Brute Force (Correlated Subquery)	O(n²)	O(1)	Calculate total for each product using individual subqueries

GROUP BY Aggregation (Optimal) — Algorithm Steps

Scan through the Sales table once
Group records by product_id
Calculate SUM(quantity) for each group
Return product_id and total_quantity

Visualization

Tap to expand

Step-by-Step Walkthrough

Single Table Scan

Read through Sales table once

Group by Product

Database automatically groups records by product_id

Calculate Sums

SUM(quantity) computed for each group simultaneously

Code -

solution.c — C

#include <stdio.h>

int main() {
    char query[] = 
        "SELECT \n"
        "    product_id,\n"
        "    SUM(quantity) AS total_quantity\n"
        "FROM Sales\n"
        "GROUP BY product_id\n"
        "ORDER BY product_id;\n";
    
    printf("%s", query);
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through all sales records, database handles grouping efficiently

✓ Linear Growth

Space Complexity

O(k)

Space for k distinct product groups, typically much smaller than total records

✓ Linear Space

Constraints

1 ≤ sales table rows ≤ 1000
1 ≤ product table rows ≤ 100
1 ≤ product_id ≤ 100
2000 ≤ year ≤ 2023
1 ≤ quantity ≤ 10⁴
1 ≤ price ≤ 10⁶
Primary key constraint: (sale_id, year) is unique in Sales table

28.4K Views

High Frequency

~8 min Avg. Time

892 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

GROUP BY Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler