Product Sales Analysis III

Product Sales Analysis III

What is this problem about?

The Product Sales Analysis III SQL problem asks you to find the first year each product was sold and its price in that year. This medium SQL problem tests finding the minimum year per product with its associated data — requiring a JOIN or window function approach. The database interview pattern is demonstrated.

Why is this asked in interviews?

Microsoft, Meta, Amazon, Google, and Bloomberg ask this because "find the first record per group with its associated data" is a very common analytics pattern. It validates knowledge of subquery aggregation, JOIN, or window functions.

Algorithmic pattern used

Subquery + JOIN or window function. Subquery: SELECT product_id, year AS first_year, quantity, price FROM Sales WHERE (product_id, year) IN (SELECT product_id, MIN(year) FROM Sales GROUP BY product_id). Window: SELECT product_id, year AS first_year, quantity, price FROM (SELECT *, RANK() OVER (PARTITION BY product_id ORDER BY year) AS rnk FROM Sales) t WHERE rnk=1.

Example explanation

Sales: prod1 sold in 2008(qty=10,price=10) and 2009(qty=20,price=20). Prod2 sold in 2010. Min year per product: prod1→2008, prod2→2010. Result: [(prod1,2008,10,10.00),(prod2,2010,qty,price)].

Common mistakes candidates make

• Using MIN(year) without the associated columns (must join back to get quantity/price).
• Using GROUP BY year without identifying the minimum.
• Window function with ROW_NUMBER vs RANK (if ties, rank gives all tied records).
• Selecting only product_id and year without the other required columns.

Interview preparation tip

"First record per group with its row data" is the classic GROUP BY + JOIN back pattern. Subquery finds the key (product_id, min_year); outer query retrieves all data for matching rows. Window function RANK() OVER (PARTITION BY product_id ORDER BY year) WHERE rank=1 achieves the same thing more elegantly. Practice both approaches — they appear frequently in analytics SQL interviews.