Table: Customers
+---------------+---------+ | Column Name | Type | +---------------+---------+ | customer_id | int | | name | varchar | | country | varchar | +---------------+---------+ customer_id is the column with unique values for this table. This table contains information about the customers in the company.
Table: Product
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | description | varchar | | price | int | +---------------+---------+ product_id is the column with unique values for this table. This table contains information on the products in the company. price is the product cost.
Table: Orders
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| order_id | int |
| customer_id | int |
| product_id | int |
| order_date | date |
| quantity | int |
+---------------+---------+
order_id is the column with unique values for this table.
This table contains information on customer orders.
customer_id is the id of the customer who bought "quantity" products with id "product_id".
Order_date is the date in format ('YYYY-MM-DD') when the order was shipped.
Write a solution to report the customer_id and customer_name of customers who have spent at least $100 in each month of June and July 2020.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Customers table: +--------------+-----------+-------------+ | customer_id | name | country | +--------------+-----------+-------------+ | 1 | Winston | USA | | 2 | Jonathan | Peru | | 3 | Moustafa | Egypt | +--------------+-----------+-------------+ Product table: +--------------+-------------+-------------+ | product_id | description | price | +--------------+-------------+-------------+ | 10 | LC Phone | 300 | | 20 | LC T-Shirt | 10 | | 30 | LC Book | 45 | | 40 | LC Keychain | 2 | +--------------+-------------+-------------+ Orders table: +--------------+-------------+-------------+-------------+-----------+ | order_id | customer_id | product_id | order_date | quantity | +--------------+-------------+-------------+-------------+-----------+ | 1 | 1 | 10 | 2020-06-10 | 1 | | 2 | 1 | 20 | 2020-07-01 | 1 | | 3 | 1 | 30 | 2020-07-08 | 2 | | 4 | 2 | 10 | 2020-06-15 | 2 | | 5 | 2 | 40 | 2020-07-01 | 10 | | 6 | 3 | 20 | 2020-06-24 | 2 | | 7 | 3 | 30 | 2020-06-25 | 2 | | 9 | 3 | 30 | 2020-05-08 | 3 | +--------------+-------------+-------------+-------------+-----------+ Output: +--------------+------------+ | customer_id | name | +--------------+------------+ | 1 | Winston | +--------------+------------+ Explanation: Winston spent $300 (300 * 1) in June and $100 ( 10 * 1 + 45 * 2) in July 2020. Jonathan spent $600 (300 * 2) in June and $20 ( 2 * 10) in July 2020. Moustafa spent $110 (10 * 2 + 45 * 2) in June and $0 in July 2020.
Problem Overview: You need to identify customers who placed enough orders in two different months to meet a spending threshold. The database contains Customers, Orders, and Product tables. The task is to compute each customer's total spending for June and July and return customers whose spending in both months is at least $100.
Approach 1: Join + Group By + Having (O(n) time, O(k) space)
This approach joins the Orders table with Product to calculate the total order value using quantity * price. After the join, group rows by customer_id. Use conditional aggregation with SUM(CASE WHEN ...) to compute spending separately for June and July within the same grouped result. The HAVING clause filters customers whose monthly totals are both greater than or equal to 100. Time complexity is O(n) where n is the number of order records scanned, and space complexity is O(k) for grouped customer aggregates.
The key insight is computing both monthly totals in a single grouped query instead of running separate queries. Conditional aggregation allows you to scan the dataset once while building multiple metrics. This pattern appears frequently in database interview questions and analytics workloads.
Approach 2: Monthly Subqueries + Join (O(n) time, O(k) space)
Another way is to compute two separate subqueries: one that aggregates June spending per customer and another that aggregates July spending. Each subquery joins Orders and Product, groups by customer_id, and filters with HAVING SUM(quantity * price) >= 100. The two result sets are then joined on customer_id to keep customers that satisfy both conditions. Time complexity remains O(n) because all orders still need to be scanned, while space complexity is O(k) for intermediate grouped results.
This method is straightforward but requires multiple aggregations and an additional join. The query becomes longer and may perform slightly worse than a single-pass aggregation.
Recommended for interviews: The single-query solution using JOIN, GROUP BY, and conditional aggregation in HAVING is what most interviewers expect. It demonstrates strong SQL fundamentals and efficient data scanning. Understanding how to compute multiple metrics in one grouped query is a common pattern in SQL analytics problems and reporting pipelines. The subquery approach still works but mainly shows correctness rather than query optimization skill.
We can use the JOIN statement to join the Orders table and the Product table, and then join the result with the Customers table. We can filter out the records where the order_date is not in the year 2020, and then use the GROUP BY statement to group the data by customer_id. Finally, we can use the HAVING statement to filter out the customers whose spending in June and July is greater than or equal to 100.
MySQL
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Join + Group By + Having (Conditional Aggregation) | O(n) | O(k) | Best general solution. Computes multiple monthly totals in a single grouped scan. |
| Monthly Subqueries + Join | O(n) | O(k) | Useful when conditions are easier to express in separate aggregates. |
LeetCode 1511 Amazon & Facebook Interview SQL Question with Detailed Explanation | Practice SQL • Everyday Data Science • 3,414 views views
Watch 4 more video solutions →Practice Customer Order Frequency with our built-in code editor and test cases.
Practice on FleetCode