Product Sales Analysis III | Leetcode 1070

Welcome to Question 25 of the Crack SQL Interview in 50 Questions series. This chapter covers Product Sales Analysis III (LeetCode 1070), a medium-level SQL problem focused on extracting the earliest transactional record for every product using subqueries and aggregate functions.

1. Problem Statement & Schema Overview

We are given two relational database tables: Sales and Product.

Table Schemas

Objective

Write an SQL query to select the product_id, first_year (the year the product was first sold), quantity, and price for the first year of every product sold. You can return the resulting table in any order.

2. Core Mental Model & Walkthrough with Example

To understand what data needs to be filtered, let's analyze a sample dataset containing multiple sales records across different years for the same products.

Sample Input Data

Sales Table:

Step-by-Step Analysis

1. Product 200: Has only one transaction record in the year 2011. Therefore, its first year of sale is 2011.
2. Product 100: Has two transaction records across different years (2008 and 2009). We need to extract the earliest (minimum) year, which is 2008, along with its respective quantity (10') and price (5000`).
3. Role of the Product Table: Notice that the Product table is unnecessary for solving this core problem because all required metrics (product_id, year, quantity, price) reside entirely inside the Sales table.

Expected Output

3. Naive vs. Optimized Approach

When approaching problems that require finding minimum/maximum values per group alongside other attributes, developers often encounter common pitfalls.

4. Step-by-Step Query Construction

Let's build the production-ready query piece by piece.

Step A: Selecting Columns and Source Table

We start by selecting the necessary columns from the Sales table. We also rename the year column to first_year using an alias.

SELECT 
    product_id, 
    year AS first_year, 
    quantity, 
    price
FROM 
    sales

Step B: Writing the Subquery for Minimum Years

To find the exact combination of product_id and its earliest year, we use a subquery that groups by product_id and applies the MIN() aggregate function on the year.

SELECT 
    product_id, 
    MIN(year)
FROM 
    sales
GROUP BY 
    product_id

Step C: Combining Main Query with Tuple Matching (IN Operator)

We filter the main query rows by checking if the tuple (product_id, year) exists within the set returned by our 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
    );

5. Execution Flow Walkthrough

6. Summary and Key Takeaways

• Unused Tables: Not every table provided in an interview schema is mandatory; analyze whether columns can be satisfied from a single source table (like Sales).
• Tuple Filtering: Using multi-column conditional checks like (col1, col2) IN (Subquery) is an effective pattern in SQL for retrieving full row contexts matching specific aggregate group criteria.
• Aggregate Functions: The MIN() function is vital when isolating temporal milestones, such as finding a product's first year of sale or a user's first login date.
• Aliasing for Clarity: Always use column aliasing (AS first_year) when required by output specifications to match exact test-case expectations.
• Grouping Constraints: Remember that non-aggregated columns cannot be queried alongside aggregate functions without proper subquery insulation or window functions (such as RANK() or ROW_NUMBER()).