Watch 10 video solutions for List the Products Ordered in a Period, a easy level problem involving Database. This walkthrough by Learn With Chirag has 4,594 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Products
+------------------+---------+ | Column Name | Type | +------------------+---------+ | product_id | int | | product_name | varchar | | product_category | varchar | +------------------+---------+ product_id is the primary key (column with unique values) for this table. This table contains data about the company's products.
Table: Orders
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | order_date | date | | unit | int | +---------------+---------+ This table may have duplicate rows. product_id is a foreign key (reference column) to the Products table. unit is the number of products ordered in order_date.
Write a solution to get the names of products that have at least 100 units ordered in February 2020 and their amount.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Products table: +-------------+-----------------------+------------------+ | product_id | product_name | product_category | +-------------+-----------------------+------------------+ | 1 | Leetcode Solutions | Book | | 2 | Jewels of Stringology | Book | | 3 | HP | Laptop | | 4 | Lenovo | Laptop | | 5 | Leetcode Kit | T-shirt | +-------------+-----------------------+------------------+ Orders table: +--------------+--------------+----------+ | product_id | order_date | unit | +--------------+--------------+----------+ | 1 | 2020-02-05 | 60 | | 1 | 2020-02-10 | 70 | | 2 | 2020-01-18 | 30 | | 2 | 2020-02-11 | 80 | | 3 | 2020-02-17 | 2 | | 3 | 2020-02-24 | 3 | | 4 | 2020-03-01 | 20 | | 4 | 2020-03-04 | 30 | | 4 | 2020-03-04 | 60 | | 5 | 2020-02-25 | 50 | | 5 | 2020-02-27 | 50 | | 5 | 2020-03-01 | 50 | +--------------+--------------+----------+ Output: +--------------------+---------+ | product_name | unit | +--------------------+---------+ | Leetcode Solutions | 130 | | Leetcode Kit | 100 | +--------------------+---------+ Explanation: Products with product_id = 1 is ordered in February a total of (60 + 70) = 130. Products with product_id = 2 is ordered in February a total of 80. Products with product_id = 3 is ordered in February a total of (2 + 3) = 5. Products with product_id = 4 was not ordered in February 2020. Products with product_id = 5 is ordered in February a total of (50 + 50) = 100.
Problem Overview: You receive two tables: Products and Orders. The task is to find products ordered during February 2020 whose total ordered units are at least 100. The output should include the product name and the total number of units ordered in that period.
Approach 1: SQL Join and Aggregation (O(n) time, O(1) extra space)
This approach joins the Products and Orders tables on product_id, filters rows where order_date falls within February 2020, and then groups results by product. The SUM(unit) aggregation computes total units per product, and a HAVING clause keeps only those with totals ≥ 100. This is the most direct relational query pattern: JOIN → filter → GROUP BY → HAVING. Databases optimize joins and aggregations well, making this the most common production query pattern. See more about SQL and joins.
Approach 2: SQL Subquery with Aggregation (O(n) time, O(1) extra space)
Instead of joining first, compute aggregated order totals inside a subquery. The subquery filters February orders and groups by product_id, returning only products with SUM(unit) ≥ 100. The outer query then joins this result with the Products table to retrieve the product name. This pattern separates aggregation logic from the final join and can improve readability when aggregation conditions become complex. It relies heavily on aggregation operations.
Approach 3: SQL Join and Aggregation in Python (O(n) time, O(n) space)
When simulating SQL behavior in Python, iterate through the orders list, filter entries in the February 2020 range, and accumulate totals in a dictionary keyed by product_id. After computing totals, filter dictionary entries where units ≥ 100. Finally map each product_id to its corresponding name using the products table. This mirrors SQL's grouping using a hash map.
Approach 4: SQL-like Execution in C# (O(n) time, O(n) space)
The C# implementation follows a similar idea using collections and LINQ-style grouping. Iterate through orders, filter by date range, group by product_id, and compute sums. After filtering groups with totals ≥ 100, join with the products collection to produce the final result set. The logic mimics relational database execution in application code.
Recommended for interviews: The SQL Join and Aggregation approach is the expected solution. Interviewers want to see correct use of JOIN, GROUP BY, and HAVING to filter aggregated results. Knowing the subquery alternative shows deeper SQL flexibility, but the join-based query is the most concise and commonly used.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Join and Aggregation | O(n) | O(1) | Standard SQL solution when combining tables and filtering aggregated results |
| SQL Subquery with Aggregation | O(n) | O(1) | Useful when you want to separate aggregation logic from the final join |
| Python Join and Aggregation | O(n) | O(n) | When simulating SQL logic using hash maps in application code |
| C# SQL-like Execution | O(n) | O(n) | When implementing grouping and joins with collections or LINQ |
