Customer Order Frequency - Practice Coding Problems

Customer Order Frequency - Problem

Database Easy

You're managing an e-commerce platform and need to identify your most valuable customers - those who consistently spend big every month! 💰

Given three tables:

Customers: Contains customer information (ID, name, country)
Product: Contains product details (ID, description, price)
Orders: Contains order transactions (order ID, customer ID, product ID, date, quantity)

Your task is to find customers who spent at least $100 in BOTH June AND July 2020. These are your premium customers who deserve special attention!

Think of it like finding customers who hit a spending milestone in consecutive months - they're likely to be your most loyal buyers.

Return the customer_id and customer_name of these high-value customers in any order.

Input & Output

example_1.sql — Basic Case

$ Input: Customers: [(1,'Winston','USA'),(2,'Jonathan','Peru'),(3,'Moustafa','Egypt')] Product: [(10,'LC Phone',300),(20,'LC T-Shirt',10),(30,'LC Book',45),(40,'LC Keychain',2)] Orders: [(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: [(1,'Winston')]

💡 Note: Winston spent $300 in June (1 LC Phone) and $110 in July (1 LC T-Shirt + 2 LC Books = $10 + $90). Both months >= $100, so he qualifies.

example_2.sql — Multiple Qualified Customers

$ Input: Customers: [(1,'Alice','USA'),(2,'Bob','Canada'),(3,'Carol','UK')] Product: [(1,'Laptop',1000),(2,'Mouse',25),(3,'Keyboard',50)] Orders: [(1,1,1,'2020-06-05',1),(2,1,2,'2020-07-10',4),(3,2,1,'2020-06-20',1),(4,2,3,'2020-07-15',2),(5,3,2,'2020-06-30',5)]

› Output: [(1,'Alice'),(2,'Bob')]

💡 Note: Alice: $1000 (June) + $100 (July) both >= $100. Bob: $1000 (June) + $100 (July) both >= $100. Carol only has June orders.

example_3.sql — Edge Case - No Qualified Customers

$ Input: Customers: [(1,'David','France'),(2,'Eve','Spain')] Product: [(1,'Pen',5),(2,'Notebook',15)] Orders: [(1,1,1,'2020-06-01',10),(2,1,2,'2020-07-01',3),(3,2,1,'2020-06-15',5),(4,2,2,'2020-08-01',10)]

› Output: []

💡 Note: David spent $50 (June) + $45 (July), both under $100. Eve has no July 2020 orders. No customers qualify.

Visualization

Tap to expand

Understanding the Visualization

Data Join

Combine Orders with Product prices and Customer info

Monthly Grouping

Group transactions by customer and calculate monthly totals

Threshold Filter

Keep only customers with $100+ spending in BOTH target months

Key Takeaway

🎯 Key Insight: Use conditional aggregation (CASE statements) to calculate spending for multiple months in a single query, then filter with HAVING clause for efficiency.

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through Orders table with efficient grouping

✓ Linear Growth

Space Complexity

O(k)

Space for grouping results where k is number of unique customers

✓ Linear Space

Constraints

1 ≤ customers.length ≤ 100
1 ≤ product.length ≤ 100
1 ≤ orders.length ≤ 1000
Product prices are between $1 and $1000
Order quantities are between 1 and 100
All dates are in format 'YYYY-MM-DD'

Asked in

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

The optimal approach uses conditional aggregation with CASE statements to calculate June and July spending in a single query. Group orders by customer and use HAVING clause to filter customers who spent $100+ in both months. This avoids multiple table scans and is much more efficient than nested subqueries.

Common Approaches

Approach	Time	Space	Notes
✓ Conditional Aggregation (Optimal)	O(n)	O(k)	Single query with conditional SUM for both months simultaneously
Nested Subqueries (Brute Force)	O(n²)	O(n)	Separate subqueries for June and July spending with intersection

Conditional Aggregation (Optimal) — Algorithm Steps

JOIN Orders and Product tables to get order values
Filter for orders in June and July 2020
GROUP BY customer_id and use conditional SUM for each month
Use HAVING clause to filter customers with $100+ in both months
JOIN with Customers table to get customer names

Visualization

Tap to expand

Step-by-Step Walkthrough

Join & Filter

Join Orders with Products, filter for June-July 2020

Group & Aggregate

Group by customer, use CASE to sum spending by month

Filter Results

HAVING clause filters customers with $100+ both months

Code -

solution.c — C

-- Optimal SQL Solution
SELECT c.customer_id, c.name as customer_name
FROM Customers c
INNER JOIN (
    SELECT o.customer_id
    FROM Orders o
    JOIN Product p ON o.product_id = p.product_id
    WHERE o.order_date BETWEEN '2020-06-01' AND '2020-07-31'
    GROUP BY o.customer_id
    HAVING 
        SUM(CASE WHEN MONTH(o.order_date) = 6 THEN o.quantity * p.price ELSE 0 END) >= 100
        AND 
        SUM(CASE WHEN MONTH(o.order_date) = 7 THEN o.quantity * p.price ELSE 0 END) >= 100
) qualified ON c.customer_id = qualified.customer_id;

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through Orders table with efficient grouping

✓ Linear Growth

Space Complexity

O(k)

Space for grouping results where k is number of unique customers

✓ Linear Space

Constraints

1 ≤ customers.length ≤ 100
1 ≤ product.length ≤ 100
1 ≤ orders.length ≤ 1000
Product prices are between $1 and $1000
Order quantities are between 1 and 100
All dates are in format 'YYYY-MM-DD'

42.8K Views

High Frequency

~15 min Avg. Time

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

Visualization

Time & Space Complexity

Related Problems

Constraints

Common Approaches

Conditional Aggregation (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Constraints

Select Compiler