Product Sales Analysis I | Leetcode 1068

This chapter explores fundamental relational database querying concepts through LeetCode 1068 (Product Sales Analysis I). We examine how to combine data residing across multiple normalized tables using SQL JOIN clauses, table aliasing, and alternative matching syntax like the USING keyword.

1. Why Do We Need This? (The Problem Statement)

In real-world relational databases, data is normalized across multiple tables to eliminate redundancy. For an e-commerce or retail application, keeping sales transactions and product metadata in a single massive table causes data duplication, storage inefficiency, and anomaly risks.

Consider a scenario where a company records thousands of sales daily. Storing the verbose product name alongside every single transaction row is inefficient. Instead, databases store a compact foreign key (product_id) in the Sales table, pointing to a dedicated Product table containing the descriptive metadata.

When generating reports or displaying order details to users, we need to merge these separate tables together to present a unified view containing descriptive product names alongside transaction years and prices.

2. Core Mental Model & Database Schema

To understand how tables connect, we examine the primary key (PK) and foreign key (FK) relationships specified in the problem statement.

• Sales Table: Contains columns sale_id, product_id, year, quantity, and price.
  • The combination of sale_id and year serves as a composite Primary Key (PRIMARY KEY(sale_id, year)).
  • product_id acts as a Foreign Key referencing the Product table.
• Product Table: Contains columns product_id and product_name.
  • product_id serves as the Primary Key (PRIMARY KEY(product_id)).

Real-Life Analogy: The Receipt and Catalog

Analogy: Imagine buying items at a supermarket checkout counter. The printed receipt lists the transaction details—the Year you bought it, the Price paid, and a numerical code (Product ID like 100). The receipt itself does not spell out the full product description ("Nokia Smartphone"). Instead, the cashier's computer system uses that numerical code (100) to look up the master catalog and fetch the human-readable Product Name ("Nokia"). A SQL JOIN is the database equivalent of looking up those product codes in the catalog.

3. Naive Approach vs. Relational Design

The Naive Approach (Single Table Denormalization)

If we stored everything in a single unnormalized table, updating a product's name would require updating thousands or millions of historical transaction rows, risking data inconsistencies and high storage costs.

The Relational Approach (Normalized Tables with SQL Joins)

By keeping tables separated, we isolate master product data from transactional history. When we need a combined report, we use relational algebra operations—specifically a LEFT JOIN or INNER JOIN—to dynamically stitch the tables together using their matching keys (product_id).

4. Step-by-Step Evolution of the SQL Query

Let's build the solution incrementally, starting with selecting the required columns and progressing to table joining.

Step 1: Identifying Target Columns

The problem requires three specific output columns in any order:

1. product_name (found in the Product table)
2. year (found in the Sales table)
3. price (found in the Sales table)

Step 2: Choosing the Right Join Strategy

Since we need to report on each sale present in the Sales table, the Sales table acts as our driving or "left" table. We use a LEFT JOIN to ensure that every row from the Sales table is evaluated and matched with its corresponding product metadata.

SELECT 
    p.product_name, 
    s.year, 
    s.price
FROM 
    Sales s
LEFT JOIN 
    Product p 
ON 
    s.product_id = p.product_id;

5. Complete Flow Walkthrough

Let's trace how the database executes the query using sample data.

Sample Data State

• Sales Table:

  • sale_id = 1, product_id = 100, year = 2008, quantity = 1, price = 5000
  • sale_id = 2, product_id = 100, year = 2009, quantity = 1, price = 5000
  • sale_id = 7, product_id = 200, year = 2011, quantity = 1, price = 9000

• Product Table:

  • product_id = 100, product_name = 'Nokia'
  • product_id = 200, product_name = 'Apple'

Execution Trace

1. From Clause: The database scans the Sales table (aliased as s).
2. Left Join: For each row in Sales, it looks across to the Product table (aliased as p) where s.product_id = p.product_id.
   • For sale_id = 1 (product_id = 100), it matches Product row 100 ('Nokia').
   • For sale_id = 2 (product_id = 100), it matches Product row 100 ('Nokia').
   • For sale_id = 7 (product_id = 200), it matches Product row 200 ('Apple').
3. Select Projection: It extracts only p.product_name, s.year, and s.price.

6. Alternative Syntax: Using the USING Clause

In SQL, when the join condition relies on two columns having the exact same name across both tables (in this case, product_id), we can simplify our join condition using the USING keyword instead of the ON clause.

Standard ON Clause Syntax

SELECT 
    p.product_name, 
    s.year, 
    s.price
FROM 
    Sales s
LEFT JOIN 
    Product p 
    ON s.product_id = p.product_id;

Refined USING Clause Syntax

SELECT 
    p.product_name, 
    s.year, 
    s.price
FROM 
    Sales s
LEFT JOIN 
    Product p 
    USING (product_id);

Note: Interviewers frequently appreciate candidates who know syntactic sugar alternatives like USING, as it demonstrates fluency and deep understanding of SQL standards.

7. Summary and Key Takeaways

• Relational Normalization: Separating data into Sales and Product tables prevents data redundancy and anomalies.
• Table Aliasing (AS): Using short aliases (like s for Sales and p for Product) keeps complex queries clean, readable, and concise.
• LEFT JOIN Mechanics: Ensures that all records from the primary (left) table are preserved during the merge operation.
• Column Qualification: Always prefix column names with table aliases (s.year, p.product_name) when querying across multiple tables to avoid ambiguous column reference errors.
• The USING Shortcut: When joining tables on columns with identical names, USING (column_name) serves as a cleaner alternative to ON t1.col = t2.col.
• Complexity & Performance: Joining tables on indexed primary/foreign key columns operates efficiently with a time complexity of $\mathcal{O}(N \log N)$ or $\mathcal{O}(N)$ depending on the join algorithm (hash join vs. merge join) executed by the database query optimizer.