Product Sales Analysis III - Practice Coding Problems

Product Sales Analysis III - Problem

Database Medium

Imagine you're a business analyst working for an e-commerce company. You have access to a comprehensive Sales table that records every product sale across multiple years. Your task is to identify the breakthrough moment for each product - finding all sales that occurred during the very first year each product entered the market.

The Sales Table Structure:

Column Name	Type	Description
sale_id	int	Unique identifier for each sale
product_id	int	Product identifier
year	int	Year when the sale occurred
quantity	int	Number of units sold
price	int	Price per unit

Your Mission: For each product, determine its launch year (earliest year it appears in sales) and return all sales records from that inaugural year. This helps identify which products had strong initial performance and understand launch patterns.

Output Format: Return a table with columns product_id, first_year, quantity, and price for all first-year sales.

Input & Output

example_1.sql — Basic Sales Data

$ Input: Sales table:\n| sale_id | product_id | year | quantity | price |\n|---------|------------|------|----------|-------|\n| 1 | 100 | 2019 | 5 | 200 |\n| 2 | 100 | 2020 | 3 | 250 |\n| 3 | 200 | 2018 | 7 | 300 |\n| 4 | 200 | 2019 | 2 | 320 |

› Output: | product_id | first_year | quantity | price |\n|------------|------------|----------|-------|\n| 100 | 2019 | 5 | 200 |\n| 200 | 2018 | 7 | 300 |

💡 Note: Product 100 first appeared in 2019 with one sale (5 units at $200). Product 200 first appeared in 2018 with one sale (7 units at $300). We return all sales from each product's inaugural year.

example_2.sql — Multiple First-Year Sales

$ Input: Sales table:\n| sale_id | product_id | year | quantity | price |\n|---------|------------|------|----------|-------|\n| 1 | 100 | 2019 | 5 | 200 |\n| 2 | 100 | 2019 | 3 | 180 |\n| 3 | 100 | 2020 | 8 | 250 |\n| 4 | 200 | 2020 | 4 | 150 |

› Output: | product_id | first_year | quantity | price |\n|------------|------------|----------|-------|\n| 100 | 2019 | 5 | 200 |\n| 100 | 2019 | 3 | 180 |\n| 200 | 2020 | 4 | 150 |

💡 Note: Product 100 had TWO sales in its first year (2019), so both records are returned. Product 200 only has sales in 2020, which becomes its first year, so that single sale is returned.

example_3.sql — Single Product Edge Case

$ Input: Sales table:\n| sale_id | product_id | year | quantity | price |\n|---------|------------|------|----------|-------|\n| 1 | 500 | 2021 | 10 | 100 |

› Output: | product_id | first_year | quantity | price |\n|------------|------------|----------|-------|\n| 500 | 2021 | 10 | 100 |

💡 Note: When a product has only one sale record, that automatically becomes its first year sale. This is the simplest case where first_year equals the only year available.

Constraints

1 ≤ sale_id ≤ 10⁶
1 ≤ product_id ≤ 10⁴
2000 ≤ year ≤ 2030
1 ≤ quantity ≤ 1000
1 ≤ price ≤ 10⁴
(sale_id, year) combination is unique
Each product appears in at least one sales record

Visualization

Tap to expand

Understanding the Visualization

Identify Launch Years

For each product, find the earliest year it appears in sales records - this is the launch year

Create Launch Registry

Build a lookup table mapping each product to its launch year using MIN aggregation

Collect Launch Performance

Join back with original data to get ALL sales from each product's launch year

Analyze Results

The result shows complete first-year performance for every product

Key Takeaway

🎯 Key Insight: By using MIN aggregation with self-join, we efficiently identify each product's market entry year and capture its complete first-year performance in a single optimized database operation.

Asked in

a Amazon 45 G Google 38 f Meta 32 ⊞ Microsoft 28 Apple 22

The optimal solution uses a self-join with MIN aggregation. First, we create a subquery that finds the minimum year for each product using GROUP BY product_id. Then we join this result back with the original Sales table, matching on both product_id and year to retrieve all sales records from each product's inaugural year. This approach is efficient with O(n) time complexity and leverages database indexing effectively.

Common Approaches

Approach	Time	Space	Notes
✓ Window Function Approach	O(n log n)	O(n)	Use window functions to efficiently identify first years and filter results
Correlated Subquery (Brute Force)	O(n²)	O(1)	For each sale, check if its year equals the minimum year for that product
Self-Join with MIN (Optimal)	O(n)	O(k)	Join the table with itself using MIN aggregation to get first-year sales

Window Function Approach — Algorithm Steps

Use ROW_NUMBER() window function to rank years for each product
Partition by product_id and order by year to get rankings
Wrap the ranked query in a CTE or subquery
Filter the outer query to only include rows where rank = 1
Select required columns with year renamed as first_year

Visualization

Tap to expand

Step-by-Step Walkthrough

Partition Creation

Data is logically partitioned by product_id

Ordering Within Partitions

Within each partition, rows are ordered by year

Ranking Assignment

ROW_NUMBER() assigns sequential numbers starting from 1

Filtering

Only rows with rank = 1 (earliest year) are selected

Code -

solution.c — C

// SQL solution using window functions
#include <stdio.h>
#include <string.h>

char* solution() {
    static char sqlQuery[] = 
        "WITH RankedSales AS (\n"
        "    SELECT \n"
        "        product_id,\n"
        "        year,\n"
        "        quantity,\n"
        "        price,\n"
        "        ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY year) as rn\n"
        "    FROM Sales\n"
        ")\n"
        "SELECT \n"
        "    product_id,\n"
        "    year as first_year,\n"
        "    quantity,\n"
        "    price\n"
        "FROM RankedSales\n"
        "WHERE rn = 1\n"
        "ORDER BY product_id, quantity;";
    return sqlQuery;
}

int main() {
    printf("%s\n", solution());
    return 0;
}

Time & Space Complexity

Time Complexity

⏱️

O(n log n)

Sorting operation for window function partitioning and ordering

⚡ Linearithmic

Space Complexity

O(n)

Space needed for ranking calculations and intermediate results

⚡ Linearithmic Space

42.0K Views

High Frequency

~15 min Avg. Time

1.9K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Window Function Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler