List the Products Ordered in a Period | Leetcode 1327

1. Overview and Problem Statement

This chapter covers LeetCode Problem 1327: "List the Products Ordered in a Period", designated as Question 49 in the MySQL interview preparation series. The core objective of this problem is to extract the names of products and their total aggregated units ordered during a specific timeframe (February 2020), applying a conditional filtering threshold where the total ordered quantity is at least 100 units (>= 100).

2. Database Schema and Table Relationships

The problem relies on two core relational database tables: Products and Orders. These tables are connected via a relational key (product_id).

Table 1: Products

Stores static metadata regarding company items.

• product_id (INT): Primary Key uniquely identifying each product.
• product_name (VARCHAR): The display name of the product.
• product_category (VARCHAR): The category to which the product belongs.

Table 2: Orders

Stores transactional data tracking customer orders over time. Note that this table may contain duplicate rows for the same product if multiple orders were placed on different dates.

• product_id (INT): Foreign Key referencing Products(product_id).
• order_date (DATE): The exact calendar date when the order was placed.
• unit (INT): The number of product units ordered on that specific date.

3. Core Mental Model & Table Linking Analogy

Real-Life Analogy: Think of the Products table as a master catalog binder in a warehouse where each item has a unique ID number and a name tag. The Orders table acts as a stack of shipping receipts filled out every day. To find out how many units of "LeetCode Solutions" were shipped in February, you must take each shipping receipt, look up its product_id in the master catalog binder to find the corresponding product name, collect all receipts matching February 2020, and sum up their unit counts on an abacus.

4. Step-by-Step Example Walkthrough

Let's trace how different product IDs behave against our temporal and aggregate constraints (Month = 2, Year = 2020, Total Units >= 100):

5. Naive vs. Production-Ready SQL Query Architecture

Naive / Draft Approach

A beginner might attempt to filter dates using raw string substrings or unindexed clauses, leading to poor query optimizer performance and unaggregated multiplication bugs.

-- Naive / Unoptimized approach
SELECT p.product_name, o.unit
FROM Products p
JOIN Orders o ON p.product_id = o.product_id
WHERE o.order_date LIKE '2020-02%';

• Pitfall: This approach fails to aggregate units per product, returning multiple raw rows instead of a grouped sum, and relies on fragile string matching (LIKE) rather than date functions.

Production-Ready Solution

We combine an INNER JOIN, date extraction functions (MONTH() and YEAR()), grouping (GROUP BY), and post-aggregation filtering (HAVING).

SELECT 
    p.product_name, 
    SUM(o.unit) AS unit
FROM Products p
INNER JOIN Orders o 
    USING (product_id)
WHERE 
    MONTH(o.order_date) = 2 
    AND YEAR(o.order_date) = 2020
GROUP BY 
    p.product_name
HAVING 
    SUM(o.unit) >= 100;

6. Detailed Breakdown of SQL Clauses

A. Joining Tables (INNER JOIN & USING)

FROM Products p
INNER JOIN Orders o 
    USING (product_id)

• An INNER JOIN ensures we only match products that have corresponding records in the orders table.
• The USING (product_id) clause is a cleaner syntactic shortcut equivalent to ON p.product_id = o.product_id, valid when both tables share an identical column name. Alternatively, the standard ON p.product_id = o.product_id syntax can be used.

B. Temporal Filtering (WHERE Clause)

WHERE 
    MONTH(o.order_date) = 2 
    AND YEAR(o.order_date) = 2020

• MONTH() and YEAR(): Built-in SQL date functions that extract the numeric month and four-digit year from the order_date datatype column.
• AND Operator: Ensures that both conditions must evaluate to true simultaneously (restricting records strictly to February of the year 2020, filtering out January and March orders).

C. Grouping Data (GROUP BY)

GROUP BY 
    p.product_name

• Because a single product can have multiple order entries throughout February, we group rows by p.product_name. This collapses all matching rows for the same product into a single summary row, enabling aggregate calculations.

D. Aggregate Calculation & Thresholding (SUM & HAVING)

SELECT 
    p.product_name, 
    SUM(o.unit) AS unit
...
HAVING 
    SUM(o.unit) >= 100;

• SUM(o.unit): Aggregates all individual order quantities recorded during February for each specific product.
• HAVING vs WHERE: We use HAVING instead of WHERE because the filtering condition (>= 100) applies to an aggregated value (SUM(o.unit)), not an individual table row. Standard WHERE clauses cannot evaluate aggregate functions directly.

7. Alternative Syntax: Using ON and Standard Date Literals

For SQL dialects that prefer explicit join conditions and ISO date range comparisons (which are more index-friendly in production environments), the query can also be structured as follows:

SELECT 
    p.product_name, 
    SUM(o.unit) AS unit
FROM Products p
JOIN Orders o 
    ON p.product_id = o.product_id
WHERE 
    o.order_date >= '2020-02-01' 
    AND o.order_date <= '2020-02-29'
GROUP BY 
    p.product_name
HAVING 
    SUM(o.unit) >= 100;

Note: Using range comparisons (>= '2020-02-01' AND o.order_date <= '2020-02-29') is generally preferred in enterprise environments over MONTH() and YEAR() functions because date ranges can leverage database b-tree indexes on order_date, avoiding full table scans.

Summary and Key Takeaways

• Problem Objective: Retrieve product names and their total order quantities for a specific month and year (Feb 2020), filtering for quantities $\ge 100$.
• Table Linking: Use an INNER JOIN connected via product_id to bridge product metadata with order transactions.
• Date Filtering: Extract temporal components using MONTH(order_date) = 2 and YEAR(order_date) = 2020 (or optimized date range filters).
• Aggregation: Use GROUP BY product_name combined with SUM(unit) to calculate total monthly sales per product.
• Post-Aggregation Filtering: Always use the HAVING clause when filtering results based on aggregate functions like SUM(), COUNT(), or AVG().
• Column Aliasing: Use AS unit to match the exact output column naming conventions required by online judges like LeetCode.