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
Products Ordered in Period AnalysisStep 1: JOINProducts + OrdersLeetcode Solutions2020-02-05: 602020-02-10: 70Step 2: FilterFebruary 2020 OnlyLeetcode Solutions60 + 70 = 130Step 3: ResultUnits ≥ 100Leetcode Solutions130 unitsSQL Query FlowSELECT p.product_name, SUM(o.unit) as unitFROM Products p INNER JOIN Orders o ON p.product_id = o.product_idWHERE o.order_date BETWEEN '2020-02-01' AND '2020-02-29'GROUP BY p.product_id, p.product_nameHAVING SUM(o.unit) >= 100
Understanding the Visualization
1
JOIN Tables
Combine Products and Orders data
2
Filter Period
Keep only February 2020 orders
3
Group & Filter
Aggregate by product and apply threshold
Key Takeaway
🎯 Key Insight: Use JOIN to combine tables, GROUP BY to aggregate data, and HAVING to filter aggregated results

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