Customer Order Frequency
ASKED IN INTERVIEWAmazon
Problem Statement
Amazon Prime Day events often span June and July. Marketing wants to find "loyal high-spenders" who spent a total of $100 or more on all their orders in June 2026 AND $100 or more on all their orders in July 2026.
Rules:
- Spending is calculated as (quantity * price).
- The customer must meet the $100 threshold in each month individually.
- Output columns: customer_id, name.
- Results must be sorted by customer_id in ascending order.
Tests your understanding of
Date Manipulation, Aggregation and Filtering
Input Tables
Customers
| customer_id(INTEGER) | name(VARCHAR) |
|---|---|
| 1 | Winston |
| 2 | Julia |
| 3 | O'Brien |
Product
| product_id(INTEGER) | price(INTEGER) |
|---|---|
| 10 | 50 |
| 20 | 10 |
| 30 | 150 |
Orders
| order_id(INTEGER) | customer_id(INTEGER) | product_id(INTEGER) | order_date(DATE) | quantity(INTEGER) |
|---|---|---|---|---|
| 1 | 1 | 10 | 2026-06-01 | 2 |
| 2 | 1 | 30 | 2026-07-05 | 1 |
| 3 | 2 | 20 | 2026-06-15 | 5 |
| 4 | 2 | 10 | 2026-07-20 | 2 |
| 5 | 3 | 30 | 2026-06-10 | 1 |
Expected Output
| customer_id(INTEGER) | name(VARCHAR) |
|---|---|
| 1 | Winston |
Tags
EasyASKED IN INTERVIEWDate ManipulationAggregationFiltering
15-20 min
64%