List the Products Ordered in a Period

List the Products Ordered in a Period

What is this problem about?

The "List the Products Ordered in a Period interview question" is a SQL challenge. You are given two tables: Products and Orders. You need to find the names of products that had at least 100 units ordered in February 2020 and return their total units. This "List the Products Ordered in a Period coding problem" is a fundamental test of data aggregation and filtering.

Why is this asked in interviews?

This problem is a staple in Data Analyst and Backend Engineer interviews at companies like Amazon and Microsoft. It tests your proficiency with "Database interview pattern" concepts like JOIN, GROUP BY, SUM(), and date filtering. It evaluates whether you can combine data from multiple tables and filter it based on aggregate results.

Algorithmic pattern used

The solution uses SQL relational operations.

1. Join: Combine Products and Orders on the product_id.
2. Filter: Use a WHERE clause to restrict the results to orders placed in '2020-02'.
3. Group: Use GROUP BY on the product name or ID.
4. Aggregate: Use SUM(unit) to calculate the total units for each group.
5. Filter Aggregates: Use the HAVING clause to keep only those groups where the sum is at least 100.

Example explanation

Products: {1: "Laptop", 2: "Mouse"}, Orders: {[1, '2020-02-01', 60], [1, '2020-02-15', 50], [2, '2020-02-10', 80]}

1. Join & Filter: Only February orders are kept.
2. Group Laptop: Units = 60 + 50 = 110.
3. Group Mouse: Units = 80.
4. Having: 110 >= 100 is true. 80 >= 100 is false. Result: "Laptop", 110.

Common mistakes candidates make

• Using WHERE for aggregates: Trying to use WHERE SUM(unit) >= 100, which is invalid SQL (you must use HAVING).
• Incorrect date range: Not properly filtering for the month (e.g., using LIKE '2020-02%' or BETWEEN incorrectly).
• Column ambiguity: Not specifying which table a column comes from in the join, although name is usually unique to the Products table.

Interview preparation tip

Always remember the order of execution in SQL: FROM -> JOIN -> WHERE -> GROUP BY -> HAVING -> SELECT. Understanding that HAVING filters after the aggregation is the most important takeaway for this problem.