Product Sales by Year

Problem

Schemas: Sales(product_id INT, year INT, quantity INT, price DECIMAL), Product(id INT, name VARCHAR). Return product_name, year, total_quantity, total_revenue (quantity × price) per product per year. Order by product name then year.

Constraints

• Price may vary across rows within a (product, year).

Approach — Core Patterns

This is a Core Patterns problem. The idea: pick the data structure best matched to the constraints and solve it in one clean pass. Work through the reference code below line by line, then re-derive it yourself in the editor — that's how the pattern sticks.

Complexity: see the walkthrough below.

Solution code

SQL

SELECT p.name AS product_name, s.year,
       SUM(s.quantity) AS total_quantity,
       SUM(s.quantity * s.price) AS total_revenue
FROM Sales s
JOIN Product p ON p.id = s.product_id
GROUP BY p.name, s.year
ORDER BY p.name, s.year;

Practice it

Reading a solution isn't the same as being able to write it under pressure. Open this problem in the in-browser editor, hide the solution, and solve it from scratch — your code runs against real test cases instantly.