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.
This approach involves using SQL to join the 'Products' and 'Orders' tables, filtering the 'Orders' dataset for February 2020, and then aggregating the units per product. We will return products with at least 100 units ordered.
This SQL code performs a join between the 'Products' table and the 'Orders' table using the shared 'product_id'. It filters the orders to only include those from February 2020. Then it sums the units for each product name and filters to only include those with a total of at least 100 units. The result displays product names and their aggregated unit counts.
SQL
Time Complexity: O(n), where n is the number of entries in the Orders table.
Space Complexity: O(1), we output the one final result set.
This method uses a subquery to aggregate the units in February 2020 first. Then, it leverages the result to join with the 'Products' table for filtering and final output, focusing again on products with at least 100 units ordered.
This solution uses a subquery to first compute the total units ordered in February 2020 by each product_id. It retains only those products with total ordered units over 100. The outer query joins this result with the 'Products' table to fetch the respective product names. This step ensures only relevant data is processed in the join, potentially enhancing performance over larger datasets.
SQL
Time Complexity: O(n), similar to the first approach, the primary work is scanning the Orders table.
Space Complexity: O(1) for the resulted processed data.
This approach involves joining the Products and Orders tables and filtering the result to only include orders from February 2020. We then aggregate the order units by product and filter out products that have less than 100 units ordered.
This solution uses the pandas library in Python to emulate a SQL-like query. First, data is converted to DataFrames. Then, orders are filtered for the specified date range. The units are summed for each product, after which only products with 100 or more units are selected. Finally, the data is merged back with product names.
Python
Time Complexity: O(n) - where n is the number of orders in the designated time frame; we iterate over each order to sum units.
Space Complexity: O(m) - where m is the number of distinct products after filtering for the given date range.
This approach simulates SQL operations using LINQ in C#. We filter the Orders collection for entries in February 2020, group by product_id, and sum the units. Products with more than 100 units are selected and joined with the Products collection to get product names.
This solution uses C# and LINQ to perform SQL-like operations. The code filters and groups the orders based on the specified date range, then sums the unit counts for these orders, filtering down to those with at least 100 units. It uses a join to merge the resulting data with the Products list for product names.
C#
Time Complexity: O(n) - where n is the number of order entries within the specified timeframe.
Space Complexity: O(m) - where m is the number of grouped products that satisfy the condition.
MySQL
| Approach | Complexity |
|---|---|
| SQL Join and Group | Time Complexity: O(n), where n is the number of entries in the Orders table. |
| SQL Subquery with Aggregation | Time Complexity: O(n), similar to the first approach, the primary work is scanning the Orders table. |
| Approach 1: SQL Join and Aggregation | Time Complexity: O(n) - where n is the number of orders in the designated time frame; we iterate over each order to sum units. |
| Approach 2: SQL-like Execution in C# | Time Complexity: O(n) - where n is the number of order entries within the specified timeframe. |
| Default Approach | — |
| 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 |
List the Products Ordered in a Period | Leetcode 1327 | Crack SQL Interviews in 50 Qs #mysql • Learn With Chirag • 4,594 views views
Watch 9 more video solutions →Practice List the Products Ordered in a Period with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor