Product Quantity Rollup by SKU

Easy

You are given two related tables: Sales: transactional sales rows with columns sale_id, product_id, year, quantity, and price Product: product metadata with columns product_id and product_name Task: Compute total sold quantity per product_id across all rows in Sales. Include only product_ids that actually appear in Sales. Return one row per qualifying product_id. Required output columns: product_id total_quantity Notes: Product metadata is provided for context and future extensions. This task can be solved in both SQL and Pandas. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

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        | Phone-A      |
| 200        | Phone-B      |
| 300        | Phone-C      |

Output

[
  {"product_id":100,"total_quantity":22},
  {"product_id":200,"total_quantity":15}
]

Explanation

Product 100 appears twice and sums to 22. Product 200 sums to 15. Product 300 has no sales and is excluded.

Example 2

Input

Sales:
| sale_id | product_id | year | quantity | price |
|---------|------------|------|----------|-------|
| 11      | 10         | 2020 | 5        | 30    |
| 12      | 10         | 2020 | 7        | 28    |
| 13      | 11         | 2020 | 2        | 100   |
| 14      | 10         | 2021 | 3        | 32    |

Product:
| product_id | product_name |
|------------|--------------|
| 10         | Widget-X     |
| 11         | Widget-Y     |

Output

[
  {"product_id":10,"total_quantity":15},
  {"product_id":11,"total_quantity":2}
]

Explanation

Aggregation is by product_id across all years and rows. Price changes do not affect quantity totals.

Example 3

Input

Sales:
| sale_id | product_id | year | quantity | price |
|---------|------------|------|----------|-------|
| 50      | 5          | 2019 | 1        | 100   |
| 51      | 6          | 2019 | 4        | 90    |
| 52      | 6          | 2020 | 6        | 95    |
| 53      | 7          | 2020 | 8        | 120   |

Product:
| product_id | product_name |
|------------|--------------|
| 5          | Alpha        |
| 6          | Beta         |
| 7          | Gamma        |
| 8          | Delta        |

Output

[
  {"product_id":5,"total_quantity":1},
  {"product_id":6,"total_quantity":10},
  {"product_id":7,"total_quantity":8}
]

Explanation

Only products present in Sales are returned. Product 8 exists in Product but is not included because it has zero sales rows.

Constraints

1 <= number of rows in Sales <= 10000
1 <= number of rows in Product <= 10000
1 <= quantity <= 1000000
1 <= price <= 10000000
(sale_id, year) is unique
Sales.product_id references Product.product_id
Return one row per product_id appearing in Sales
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns product_id and total_quantity

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 | Phone-A | | 200 | Phone-B | | 300 | Phone-C |

Sales: | sale_id | product_id | year | quantity | price | |---------|------------|------|----------|-------| | 11 | 10 | 2020 | 5 | 30 | | 12 | 10 | 2020 | 7 | 28 | | 13 | 11 | 2020 | 2 | 100 | | 14 | 10 | 2021 | 3 | 32 | Product: | product_id | product_name | |------------|--------------| | 10 | Widget-X | | 11 | Widget-Y |

Sales: | sale_id | product_id | year | quantity | price | |---------|------------|------|----------|-------| | 50 | 5 | 2019 | 1 | 100 | | 51 | 6 | 2019 | 4 | 90 | | 52 | 6 | 2020 | 6 | 95 | | 53 | 7 | 2020 | 8 | 120 | Product: | product_id | product_name | |------------|--------------| | 5 | Alpha | | 6 | Beta | | 7 | Gamma | | 8 | Delta |