Skip to content

1069. Product Sales Analysis II.md

Latest commit

 

History

History
72 lines (47 loc) · 2.94 KB

1069. Product Sales Analysis II.md

File metadata and controls

72 lines (47 loc) · 2.94 KB

Question 1069: Product Sales Analysis II

LeetCode URL: https://leetcode.com/problems/product-sales-analysis-ii/

Description

The query result format is in the following example: 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 | | 300 | Samsung | +------------+--------------+ Result table: +--------------+----------------+ | product_id | total_quantity | +--------------+----------------+ | 100 | 22 | | 200 | 15 | +--------------+----------------+ Difficulty: Easy Lock: Prime Company: Amazon Problem Solution 1069-Product-Sales-Analysis-II All Problems: Link to All Problems All contents and pictures on this website come from the Internet and are updated regularly every week.

Table Schema Structure

Create table If Not Exists Sales (sale_id int, product_id int, year int, quantity int, price int);
Create table If Not Exists Product (product_id int, product_name varchar(10));

Sample Input Data

insert into Sales (sale_id, product_id, year, quantity, price) values ('1', '100', '2008', '10', '5000');
insert into Sales (sale_id, product_id, year, quantity, price) values ('2', '100', '2009', '12', '5000');
insert into Sales (sale_id, product_id, year, quantity, price) values ('7', '200', '2011', '15', '9000');
insert into Product (product_id, product_name) values ('100', 'Nokia');
insert into Product (product_id, product_name) values ('200', 'Apple');
insert into Product (product_id, product_name) values ('300', 'Samsung');

Expected Output Data

+--------------+----------------+
| product_id   | total_quantity |
+--------------+----------------+
| 100          | 22             |
| 200          | 15             |
+--------------+----------------+

SQL Solution

SELECT product_id,SUM(quantity) AS total_quantity
FROM sales_1068
GROUP BY product_id;

Solution Breakdown

Goal

The query builds the final result columns product_id, total_quantity from sales.

Result Grain

One row per unique key in GROUP BY product_id.

Step-by-Step Logic

  1. Aggregate rows with SUM grouped by product_id.
  2. Project final output columns: product_id, total_quantity.

Why This Works

Grouping keys define the reporting grain; aggregate functions then summarize values at exactly that grain. The final projection exposes only the columns required by the result contract.

Performance Notes

Primary cost drivers are sorting/grouping. Indexes on join/filter/group keys typically provide the biggest speedup.

Common Pitfalls

  • Every non-aggregated selected column must belong to the grouping grain.