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
SQL Problem: Customer Order FrequencyInput TablesOrders + ProductsAlice: Jun $115, Jul $120Bob: Jun $180, Jul $0Tom: Jun $50, Jul $90Need $100+ both monthsGROUP BYFILTEROutputQualified Customers✓ Alice (1)Met criteria both monthsAnalysis Steps1. Join Orders → Products → Customers2. Calculate: price × quantity per order3. Group by customer_id, month4. Filter: SUM ≥ $100 in both months
Understanding the Visualization
1
Join Tables
Combine order data with product prices and customer info
2
Filter Dates
Focus on June and July 2020 orders
3
Group & Calculate
Sum monthly spending per customer
4
Apply Filters
Find customers with $100+ in both months
Key Takeaway
🎯 Key Insight: Use temporal grouping with HAVING to find consistent behavior patterns

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