Customer Order Frequency - Problem

You are given three tables: Customers, Product, and Orders.

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.

Table Schema

Customers
Column Name Type Description
customer_id PK int Primary key, unique customer identifier
name varchar Customer name
country varchar Customer's country
Primary Key: customer_id
Product
Column Name Type Description
product_id PK int Primary key, unique product identifier
description varchar Product description
price int Product cost
Primary Key: product_id
Orders
Column Name Type Description
order_id PK int Primary key, unique order identifier
customer_id int Foreign key to Customers table
product_id int Foreign key to Product table
order_date date Order date in YYYY-MM-DD format
quantity int Quantity of products ordered
Primary Key: order_id

Input & Output

Example 1 — Customer with Qualifying Spending
Input Tables:
Customers
customer_id name country
1 Alice USA
2 Bob UK
3 Tom USA
Product
product_id description price
1 laptop 90
2 mouse 25
3 keyboard 40
Orders
order_id customer_id product_id order_date quantity
1 1 1 2020-06-10 1
2 1 2 2020-06-25 1
3 1 3 2020-07-01 3
4 2 1 2020-06-02 2
5 3 2 2020-06-20 2
6 3 1 2020-07-12 1
Output:
customer_id name
1 Alice
💡 Note:

Alice spent $90 + $25 = $115 in June 2020 and $40 × 3 = $120 in July 2020. She spent at least $100 in both months. Bob spent $180 in June but $0 in July. Tom spent $50 in June and $90 in July, not reaching $100 in June.
Example 2 — No Qualifying Customers
Input Tables:
Customers
customer_id name country
1 John USA
Product
product_id description price
1 phone 80
Orders
order_id customer_id product_id order_date quantity
1 1 1 2020-06-15 1
2 1 1 2020-07-20 1
Output:
customer_id name
💡 Note:

John spent only $80 in June and $80 in July, not reaching the $100 threshold in either month.

Constraints

  • 1 ≤ customer_id, product_id, order_id ≤ 1000
  • order_date is in format 'YYYY-MM-DD'
  • 1 ≤ price, quantity ≤ 100

Visualization

Tap to expand
Customer Order Frequency INPUT TABLES Customers customer_id name 1 Winston 2 Jonathan Orders order_id cust_id price date 1 1 $60 Jun-20 2 1 $50 Jun-20 3 1 $120 Jul-20 ... ... ... ... Product product_id price ... Filter Criteria: June 2020 AND July 2020 ALGORITHM STEPS 1 JOIN Tables Customers + Orders + Product 2 Filter by Date WHERE year=2020, month IN (6,7) 3 GROUP BY customer Aggregate spending per month 4 HAVING Clause SUM for June >= $100 AND SUM for July >= $100 SELECT c.customer_id, c.name FROM Customers c JOIN Orders o ... GROUP BY c.customer_id HAVING june>=100, july>=100 FINAL RESULT Qualified Customers customer_id name 1 Winston Verification: Winston June: $60+$50 = $110 Winston July: $120 = $120 Both months >= $100 ... OK Output: [(1, "Winston")] Key Insight: Use conditional aggregation with SUM(CASE WHEN month=6 THEN price*quantity ELSE 0 END) to calculate spending separately for June and July. The HAVING clause then filters customers meeting BOTH conditions. This avoids multiple subqueries and efficiently checks both months in a single GROUP BY operation. TutorialsPoint - Customer Order Frequency | Optimal Solution

Related Problems

Asked in
Amazon 12 Facebook 8
28.0K Views
Medium Frequency
~12 min Avg. Time
890 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