LeetCode 1327 - List the Products Ordered in a Period

The problem asks us to list all products that have been ordered in February 2020 with a total quantity of at least 100 u

leetcodeeasydatabase

LeetCode Problem 1327

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

The problem asks us to list all products that have been ordered in February 2020 with a total quantity of at least 100 units. We are given two tables: Products and Orders. The Products table contains metadata about each product including its unique product_id and product_name. The Orders table records every order of products with the product_id, the date of the order (order_date), and the quantity (unit).

We are expected to aggregate the total units ordered per product for February 2020 only, filter products whose total meets or exceeds 100 units, and return their product_name along with the computed sum. The order of rows in the result does not matter.

Constraints and implications include that multiple orders for the same product on different dates or even the same date must be summed correctly, and that some products might not have any orders in February 2020. Edge cases include products with exactly 100 units, products with orders outside of February, or products with zero orders. The solution must handle these correctly.

Approaches

A brute-force approach would involve scanning the entire Orders table, filtering manually by month and year, grouping the orders by product_id, and summing the units. Then, for each product, we would join with the Products table to get the product_name and filter by the threshold of 100 units. While this is correct, it is unnecessarily verbose, and for large tables it can be inefficient if done outside of SQL (e.g., in application code).

The optimal solution leverages SQL aggregation functions directly. By filtering orders for February 2020 in the WHERE clause, grouping by product_id, summing the units, applying the HAVING clause for filtering sums ≥ 100, and finally joining with Products to get product_name, we achieve the correct result efficiently with a single SQL query. This approach uses the database engine’s optimization and indexing, making it suitable for large datasets.

Approach Time Complexity Space Complexity Notes
Brute Force O(n) scan + manual aggregation O(p) for grouping Filters and sums done in application code
Optimal O(n) scan + O(p) aggregation in SQL O(p) for grouping Uses SQL aggregation, filtering, and join efficiently

Here, n is the number of rows in Orders and p is the number of products.

Algorithm Walkthrough

  1. Filter the Orders table to include only rows where the order_date is in February 2020. This ensures we only consider relevant orders for the period.
  2. Group the filtered orders by product_id. This allows us to compute the total units per product.
  3. Compute the sum of unit for each product group. This gives the total quantity ordered in the target period.
  4. Apply a HAVING clause to keep only groups where the sum is greater than or equal to 100. This filters out products with insufficient sales.
  5. Join the resulting grouped table with the Products table using product_id to get the product_name.
  6. Select product_name and the aggregated unit as the final output.

Why it works: The algorithm ensures that only orders within February 2020 are considered and that the aggregation computes the correct total per product. The HAVING clause guarantees that only products meeting the threshold are included, and the join ensures that product names are correctly matched to their totals.

Python Solution

# There is no Python "execution" for pure SQL problems on LeetCode,
# but we provide a template that would work in a database execution context.
# This is written as a string to submit as SQL in Python context.

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

Explanation: This SQL query first filters orders to February 2020 using date comparisons. Then it groups by product_id and product_name, computes the total units per product, and filters using HAVING to keep only products with at least 100 units. The join ensures we retrieve the correct product names.

Go Solution

// LeetCode Go solution expects SQL string execution
const sqlQuery = `
SELECT 
    p.product_name,
    SUM(o.unit) AS unit
FROM 
    Orders o
JOIN 
    Products p
ON 
    o.product_id = p.product_id
WHERE 
    o.order_date >= '2020-02-01' AND o.order_date < '2020-03-01'
GROUP BY 
    o.product_id, p.product_name
HAVING 
    SUM(o.unit) >= 100
`

Explanation: The Go version is essentially the same SQL query. Go handles the query as a constant string that can be executed via a database driver. There is no difference in logic compared to Python.

Worked Examples

Using the example from the problem:

  1. Filter Orders to February 2020:
product_id order_date unit
1 2020-02-05 60
1 2020-02-10 70
2 2020-02-11 80
3 2020-02-17 2
3 2020-02-24 3
5 2020-02-25 50
5 2020-02-27 50
  1. Group by product_id and sum unit:
product_id total_unit
1 130
2 80
3 5
5 100
  1. Apply HAVING SUM(unit) >= 100:
product_id total_unit
1 130
5 100
  1. Join with Products:
product_name unit
Leetcode Solutions 130
Leetcode Kit 100

Complexity Analysis

Measure Complexity Explanation
Time O(n) Single pass over Orders table to filter and aggregate
Space O(p) Store sums per product, proportional to number of products

The time complexity is linear with respect to the number of orders, as each row is scanned once. Space complexity depends on the number of distinct products.

Test Cases

# Example from problem statement
assert sql_query  # Would return "Leetcode Solutions" with 130 and "Leetcode Kit" with 100

# Edge case: no orders in February
# Would return empty table

# Edge case: order exactly 100 units
# Product should appear

# Edge case: orders spanning multiple months
# Only February orders counted
Test Why
Orders in February 2020 with sum ≥ 100 Validates correct aggregation
No orders in February Validates empty result handling
Orders exactly 100 Validates boundary condition
Orders spanning multiple months Ensures proper month filtering

Edge Cases

No orders in February: Some products may have orders in other months but none in February. The query filters by date range, so these products are correctly excluded.

Exactly 100 units: The HAVING SUM(unit) >= 100 ensures that products with exactly 100 units are included. This boundary check prevents off-by-one errors.

Multiple orders for the same product on the same day: The algorithm sums all orders regardless of duplication in the Orders table. By grouping by product_id, even repeated orders in a single day are correctly aggregated.

Orders with null values: Since the schema ensures unit is an integer and product_id is a valid foreign key, nulls are not expected. If they occur, SQL would skip or error depending on settings, but LeetCode's constraints guarantee valid input.