Product Sales Analysis II

Problem

Table: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the product name of each product.

Problem Definition

Write a solution that reports the total quantity sold for every product id.

Example

Output

Try It Yourself

Solution

We can simply select the product_id and calculate the sum of the quantity column as total_quantity from the Sales table. Then, we group the results by product_id.

SELECT product_id,
       Sum(quantity) AS total_quantity
FROM   Sales
GROUP  BY product_id 

Let's break down the query step by step:

Step 1: Inspect raw fields

SELECT product_id,
       quantity
FROM   Sales

The SELECT clause specifies the raw columns we want to inspect before aggregation.

Output After Step 1:

+------------+----------+
| product_id | quantity |
+------------+----------+
| 100        | 10       |
| 100        | 12       |
| 200        | 15       |
+------------+----------+

Step 2: GROUP BY product_id and SUM quantity

SELECT product_id,
       Sum(quantity) AS total_quantity
FROM   Sales
GROUP  BY product_id

We group the rows by product_id and aggregate the quantity values using the SUM function to compute the total sales per product.

Final Output:

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

Pattern: sum-per-key (group-reduce)

Name: aggregate-per-key — GROUP BY the entity key, fold measures with SUM/COUNT/… One output row per distinct key that appears in the fact table.

Trace: product 100 → quantities 10+12 = 22; product 200 → 15. Keys come only from rows that exist in Sales.

Missing-group (when-not for driving from Sales alone): a product in Product with zero sales never appears — INNER nature of grouping a fact table. If the report must show zeros:

SELECT p.product_id, COALESCE(SUM(s.quantity), 0) AS total_quantity
FROM Product p
LEFT JOIN Sales s ON s.product_id = p.product_id
GROUP BY p.product_id;

WHERE vs HAVING: filter rows before grouping with WHERE (WHERE sale_date >= …); filter groups after with HAVING (HAVING SUM(quantity) > 10). You cannot put SUM in WHERE.

Index: (product_id) on Sales supports the group key; covering (product_id) INCLUDE (quantity) / composite helps index-only aggregates on large facts.

Wrong approach: selecting non-grouped columns without aggregates (illegal under ONLY_FULL_GROUP_BY).

Drill: Add HAVING so only products with total_quantity ≥ 20 remain. Sample answer: only product 100.

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — Product Sales Analysis II

Why this exists / the decision it encodes

This is the aggregate-per-key pattern: GROUP BY entity key, SUM the measure. The standout decision is what set of keys you want — only products that sold (drive from Sales) vs every product including zeros (drive from Product with LEFT JOIN).

Worked example with numbers or traced SQL/FD

Sales: (100,10), (100,12), (200,15)
GROUP BY product_id SUM(quantity) → 100:22, 200:15
Product 300 with zero sales: ABSENT from Sales-only group
Zero-preserving form:
SELECT p.product_id, COALESCE(SUM(s.quantity),0)
FROM Product p LEFT JOIN Sales s ON s.product_id=p.product_id
GROUP BY p.product_id;
WHERE filters rows pre-group; HAVING SUM(quantity)>10 filters groups post-group
HAVING total ≥ 20 on sample → only product 100

When NOT / named alternative

When NOT Sales-only: inventory/catalog reports that must show zero-sales products. When NOT GROUP BY: if you need one row per sale line, do not aggregate. Wrong: SELECT product_name without GROUP/aggregate under ONLY_FULL_GROUP_BY.

Failure mode / ops fingerprint / interview trap

Trap: SUM without GROUP BY returns one row for whole table. Ops: missing-group products look "deleted" in dashboards that INNER-join aggregates. Index (product_id) INCLUDE (quantity) for large facts.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K13: driving table choice (fact vs dimension) is report semantics. Index the group key for large sales facts.

Hostile-panel drills (with model answers)

Q1. Name the pattern.
Model answer: Aggregate-per-key / group-reduce: one output row per distinct group key that appears in the fact stream, with SUM/COUNT folds.

Q2. How do you include products with zero sales?
Model answer: LEFT JOIN from Product to Sales, GROUP BY product_id, COALESCE(SUM(quantity),0).

Q3. WHERE vs HAVING for quantity filters?
Model answer: WHERE quantity > 0 filters input rows before grouping. HAVING SUM(quantity) > 10 filters after aggregation; aggregates are illegal in WHERE.