Product Sales Analysis III - Solution & Explanation

Problem Overview: You are given a Sales table that records product sales by year. The goal is to return the first year each product was sold along with the corresponding quantity and price. Only the row representing the earliest year per product_id should appear in the result.

Approach 1: SQL Aggregation and Filtering (O(n) time, O(k) space)

Group records by product_id and compute the earliest year using MIN(year). This aggregation identifies the first sales year for each product. Then filter the original table by matching product_id and the computed minimum year to retrieve the correct quantity and price. This approach relies on standard SQL aggregation and works efficiently when the dataset is indexed by product or year. It is a straightforward application of SQL aggregation with a filtering step.

Approach 2: Nested Query and Join (O(n) time, O(k) space)

Create a derived table that calculates MIN(year) for each product_id. Then join this result back with the Sales table using both product_id and year. The join ensures that only rows matching the first sales year are returned. This pattern is common in database queries where aggregated values must be mapped back to full records.

Approach 3: SQL Subquery (O(n) time, O(1) extra space)

Use a correlated subquery inside the WHERE clause. For each row in Sales, compute the minimum year for that product using SELECT MIN(year) FROM Sales WHERE product_id = .... Keep only rows where the row's year matches that minimum value. This approach is compact and expressive but may run slightly slower on very large datasets because the subquery is evaluated per row. Databases with strong query optimization typically convert it internally into an efficient execution plan.

Approach 4: SQL Common Table Expression (CTE) (O(n) time, O(k) space)

Define a WITH clause that calculates the earliest year per product. The CTE stores product_id and first_year using GROUP BY and MIN(year). Then join the CTE with the original table to fetch the associated quantity and price. CTEs improve readability for complex queries and are widely used in analytical SQL workflows involving aggregation.

Recommended for interviews: The aggregation + join approach or the CTE solution is usually preferred. Both clearly separate computing the earliest year from retrieving the full row. Writing a brute-force correlated subquery shows understanding of SQL filtering logic, but the grouped aggregation pattern demonstrates stronger query design and scalability.