Product Sales Analysis II - Problem

Given two tables Sales and Product, write a SQL solution to report the total quantity sold for every product id.

The Sales table contains sales records with columns:

  • sale_id (int): Sale identifier
  • product_id (int): Product identifier (foreign key)
  • year (int): Sale year
  • quantity (int): Units sold
  • price (int): Price per unit

The Product table contains product information with columns:

  • product_id (int): Product identifier (primary key)
  • product_name (varchar): Product name

Return the resulting table in any order.

Table Schema

Sales
Column Name Type Description
sale_id PK int Sale identifier
product_id int Product identifier (foreign key to Product table)
year PK int Year of the sale
quantity int Number of units sold
price int Price per unit
Primary Key: (sale_id, year)
Product
Column Name Type Description
product_id PK int Product identifier (primary key)
product_name varchar Name of the product
Primary Key: product_id

Input & Output

Example 1 — Multiple Sales Per Product
Input Tables:
Sales
sale_id product_id year quantity price
1 100 2008 10 5000
2 100 2009 12 5000
7 200 2011 15 9000
Product
product_id product_name
100 Nokia
200 Apple
300 Samsung
Output:
product_id total_quantity
100 22
200 15
💡 Note:

Product 100 (Nokia) has two sales records: 10 units in 2008 and 12 units in 2009, totaling 22 units. Product 200 (Apple) has one sale record with 15 units. Product 300 (Samsung) has no sales, so it doesn't appear in the result.
Example 2 — Single Sale Per Product
Input Tables:
Sales
sale_id product_id year quantity price
1 100 2008 10 5000
2 200 2009 25 3000
3 300 2010 35 8000
Product
product_id product_name
100 Nokia
200 Apple
300 Samsung
Output:
product_id total_quantity
100 10
200 25
300 35
💡 Note:

Each product has exactly one sale record, so the total quantity equals the individual sale quantity. Product 100 sold 10 units, product 200 sold 25 units, and product 300 sold 35 units.
Example 3 — Empty Sales Table
Input Tables:
Sales
sale_id product_id year quantity price
Product
product_id product_name
100 Nokia
200 Apple
Output:
product_id total_quantity
💡 Note:

When there are no sales records, the result is empty since GROUP BY only creates groups for existing data. Products with no sales don't appear in the output.

Constraints

  • 1 ≤ sale_id ≤ 1000
  • 1 ≤ product_id ≤ 1000
  • 2000 ≤ year ≤ 2020
  • 1 ≤ quantity ≤ 100
  • 1 ≤ price ≤ 10000

Visualization

Tap to expand
Product Sales Analysis II INPUT Sales Table sale_id product_id quantity 1 100 5 2 100 3 3 101 2 4 102 4 Product Table product_id product_name 100 Nokia 101 Apple 102 Samsung Goal: Total quantity per product_id ALGORITHM STEPS 1 SELECT Columns Choose product_id and SUM(quantity) as total 2 FROM Sales Table Use Sales table as the data source 3 GROUP BY product_id Aggregate quantities for each product 4 Execute Query Return result with total_quantity column SQL Query: SELECT product_id, SUM(quantity) AS total_quantity FROM Sales GROUP BY 1 FINAL RESULT Output Table product_id total_qty 100 8 101 2 102 4 Calculation: 100: 5 + 3 = 8 101: 2 = 2 102: 4 = 4 OK - Complete Key Insight: The SUM() aggregate function with GROUP BY is the optimal approach for calculating totals per group. No JOIN needed since we only need product_id from Sales table. Time complexity: O(n) where n = rows in Sales. TutorialsPoint - Product Sales Analysis II | Optimal Solution

Related Problems

Asked in
Amazon 12 Facebook 8 Microsoft 6
34.5K 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