List the Products Ordered in a Period - Practice Coding Problems

List the Products Ordered in a Period - Problem

Database Easy

List the Products Ordered in a Period

You are working as a data analyst for an e-commerce company and need to identify popular products during a specific time period. Your task is to find all products that had significant sales volume (at least 100 units) during February 2020.

You have access to two database tables:

Products table contains product information:
• product_id: Unique identifier for each product
• product_name: Name of the product
• product_category: Category the product belongs to

Orders table contains order history:
• product_id: References the product that was ordered
• order_date: Date when the order was placed
• unit: Number of units ordered

Goal: Return the names of products that had at least 100 units ordered in February 2020, along with their total units ordered during that period.

Input & Output

example_1.sql — Basic Example

$ Input: Products table: +------------+--------------+------------------+ | product_id | product_name | product_category | +------------+--------------+------------------+ | 1 | Leetcode Solutions| Book | | 2 | Jewels of Stringology| Book | | 3 | HP | Laptop | +------------+--------------+------------------+ 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 | +------------+------------+---------+

› Output: +--------------+---------+ | product_name | unit | +--------------+---------+ | Leetcode Solutions | 130 | +--------------+---------+

💡 Note: Only 'Leetcode Solutions' had at least 100 units ordered in February 2020 (60 + 70 = 130 units). 'Jewels of Stringology' had only 80 units in February, and 'HP' had only 5 units total.

example_2.sql — Multiple Products

$ Input: Products table: +------------+--------------+------------------+ | product_id | product_name | product_category | +------------+--------------+------------------+ | 1 | Gaming Mouse | Electronics | | 2 | Webcam | Electronics | | 3 | Notebook | Stationery | +------------+--------------+------------------+ Orders table: +------------+------------+---------+ | product_id | order_date | unit | +------------+------------+---------+ | 1 | 2020-02-01 | 50 | | 1 | 2020-02-15 | 60 | | 2 | 2020-02-10 | 120 | | 3 | 2020-02-20 | 40 | +------------+------------+---------+

› Output: +--------------+---------+ | product_name | unit | +--------------+---------+ | Gaming Mouse | 110 | | Webcam | 120 | +--------------+---------+

💡 Note: Both 'Gaming Mouse' (50 + 60 = 110) and 'Webcam' (120) had at least 100 units ordered in February 2020. 'Notebook' only had 40 units.

example_3.sql — Edge Case - No Qualifying Products

$ Input: Products table: +------------+--------------+------------------+ | product_id | product_name | product_category | +------------+--------------+------------------+ | 1 | Phone Case | Electronics | | 2 | Charger | Electronics | +------------+--------------+------------------+ Orders table: +------------+------------+---------+ | product_id | order_date | unit | +------------+------------+---------+ | 1 | 2020-02-05 | 30 | | 1 | 2020-02-15 | 40 | | 2 | 2020-02-10 | 50 | +------------+------------+---------+

› Output: +--------------+---------+ | product_name | unit | +--------------+---------+ (empty result set)

💡 Note: No products had at least 100 units ordered in February 2020. 'Phone Case' had 70 total units and 'Charger' had 50 units, both below the 100-unit threshold.

Constraints

1 ≤ Products table rows ≤ 1000
1 ≤ Orders table rows ≤ 10000
product_id is unique in Products table
order_date is in format 'YYYY-MM-DD'
unit ≥ 1 for all orders
February 2020 has 29 days (leap year)

Visualization

Tap to expand

Understanding the Visualization

JOIN Tables

Combine Products and Orders tables using product_id as the key

Filter Dates

Keep only orders from February 2020 using WHERE clause

Group Products

GROUP BY product_id to aggregate all orders for each product

Sum Units

Use SUM(unit) to calculate total units ordered per product

Filter Results

HAVING clause filters products with at least 100 total units

Key Takeaway

🎯 Key Insight: SQL's JOIN, GROUP BY, and HAVING clauses work together efficiently to filter, aggregate, and select data in a single optimized query execution.

Asked in

a Amazon 45 f Meta 38 G Google 32 ⊞ Microsoft 28

The optimal solution uses a single SQL query with INNER JOIN to combine Products and Orders tables, WHERE clause to filter February 2020 dates, GROUP BY to aggregate orders per product, and HAVING to filter products with at least 100 total units. This approach leverages SQL's built-in optimizations for maximum efficiency.

Common Approaches

Approach	Time	Space	Notes
✓ Nested Queries Approach	O(P * O)	O(P)	Check each product individually with separate queries for February 2020 orders
JOIN with GROUP BY (Optimal)	O(O + P)	O(P)	Single SQL query using JOIN, filtering by date, and grouping by product

Nested Queries Approach — Algorithm Steps

Get all products from Products table
For each product, query Orders table for February 2020 orders
Sum the units for each product
Filter products with at least 100 units
Return product names and their totals

Visualization

Tap to expand

Step-by-Step Walkthrough

Get Products

Retrieve all products from Products table

Check Each Product

For each product, run separate query on Orders table

Filter Results

Keep only products with >= 100 units

Code -

solution.c — C

// Brute force approach concept
#include <stdio.h>
#include <string.h>

char* getProductsBruteForce() {
    // This represents the conceptual brute force approach
    static char query[] = 
        "SELECT p.product_name, "
        "       (SELECT COALESCE(SUM(o.unit), 0) "
        "        FROM Orders o "
        "        WHERE o.product_id = p.product_id "
        "        AND o.order_date >= '2020-02-01' "
        "        AND o.order_date < '2020-03-01') as unit "
        "FROM Products p "
        "HAVING unit >= 100";
    
    return query;
}

Time & Space Complexity

Time Complexity

⏱️

O(P * O)

P products × O orders checks, very inefficient for large datasets

✓ Linear Growth

Space Complexity

O(P)

Space for storing all product results

✓ Linear Space

42.0K Views

Medium-High Frequency

~15 min Avg. Time

1.9K 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

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Nested Queries Approach — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler