SQL Interview Questions – Product sales analysis

Problem Description –

Write an SQL query that selects the product id, year, quantity, and price for the first year of every product sold.

Return the resulting table in any order.

The query result format is in the following example.

Difficulty Level – Medium

Problem Link – https://leetcode.com/problems/product-sales-analysis-iii/

Solution –

SELECT
    product_id,
    year as first_year,
    quantity,
    price
FROM (
    SELECT
        product_id,
        year,
        DENSE_RANK() OVER(PARTITION BY product_id ORDER BY year ASC) as rnk,
        quantity,
        price
    FROM sales
    ) x
WHERE rnk = 1

To solve this problem, we use the DENSE_RANK window function to get the first year. If you try to solve this problem by MIN(year) you will get wrong result.

First we partition the data by product_id and order the year in ascending order to get the first year of every product sold.

SELECT
      product_id,
      year,
      DENSE_RANK() OVER(PARTITION BY product_id ORDER BY year ASC) as rnk,
      quantity,
      price
FROM sales

Then in the outer query we simple select only the rows where rnk is 1 to get the answer.