List the Products Ordered in a Period - Problem

You have two tables: Products and Orders.

The Products table contains information about the company's products with columns:

  • product_id (primary key): unique identifier for each product
  • product_name: name of the product
  • product_category: category of the product

The Orders table contains order information with columns:

  • product_id (foreign key): references Products table
  • order_date: date when the order was placed
  • unit: number of units ordered

Write a SQL query to find the names of products that have at least 100 units ordered in February 2020 and return their total amount ordered.

Return the result in any order.

Table Schema

Products
Column Name Type Description
product_id PK int Primary key, unique identifier for each product
product_name varchar Name of the product
product_category varchar Category of the product
Primary Key: product_id
Orders
Column Name Type Description
product_id int Foreign key referencing Products table
order_date date Date when the order was placed
unit int Number of units ordered

Input & Output

Example 1 — Products with Sufficient Orders
Input Tables:
Products
product_id product_name product_category
1 Leetcode Solutions Book
2 Jewels of Stringology Book
3 HP Laptop
Orders
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
Output:
product_name unit
Leetcode Solutions 130
💡 Note:

Leetcode Solutions (product_id=1) has orders of 60 units on 2020-02-05 and 70 units on 2020-02-10, totaling 130 units in February 2020, which exceeds 100. Other products either don't reach 100 units in February 2020 or don't exist in the Products table.
Example 2 — No Products Meet Criteria
Input Tables:
Products
product_id product_name product_category
1 Mouse Electronics
2 Keyboard Electronics
Orders
product_id order_date unit
1 2020-02-05 30
1 2020-02-10 40
2 2020-02-15 50
Output:
product_name unit
💡 Note:

No products have at least 100 units ordered in February 2020. Mouse has 70 total units (30+40) and Keyboard has 50 units, both below the 100-unit threshold.

Constraints

  • 1 ≤ product_id ≤ 1000
  • product_name and product_category are non-empty strings
  • order_date is a valid date
  • unit ≥ 1

Visualization

Tap to expand
List the Products Ordered in a Period INPUT Products Table product_id name category 1 Keyboard Electronics 2 Mouse Electronics 3 Screen Electronics Orders Table product_id order_date unit 1 2020-02-05 60 1 2020-02-10 70 2 2020-02-15 50 3 2020-02-20 150 Target Period: Feb 2020 (units >= 100) ALGORITHM STEPS 1 JOIN Tables Connect Products with Orders ON product_id 2 Filter by Date WHERE order_date BETWEEN '2020-02-01' AND '2020-02-29' 3 GROUP BY product SUM(unit) for each product GROUP BY product_name 4 HAVING Filter Keep only products with SUM(unit) >= 100 SELECT p.name, SUM(o.unit) FROM Products p JOIN Orders o WHERE o.order_date BETWEEN... GROUP BY... HAVING SUM >= 100 FINAL RESULT Product Name Units OK Keyboard 130 OK Screen 150 X Mouse (50 units) < 100 Summary 2 products meet criteria Total units: 280 Output: [{Keyboard, 130}, {Screen, 150}] Key Insight: Use JOIN to combine Products and Orders tables, then filter by date range using WHERE clause. GROUP BY aggregates units per product, and HAVING filters groups meeting the threshold (>= 100 units). This is more efficient than subqueries as it processes data in a single pass through the joined tables. TutorialsPoint - List the Products Ordered in a Period | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18
28.5K Views
Medium Frequency
~12 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen