Customer Order Frequency

Customer Order Frequency

What is this problem about?

The Customer Order Frequency interview question focuses on identifying loyal customers based on their spending habits over a specific period. Usually, you are given three tables: Customers, Product, and Orders. The goal is to find the IDs and names of customers who have spent at least $100 in each of two specific months (for example, June and July of a particular year). This Customer Order Frequency coding problem requires joining multiple tables and performing conditional aggregations.

Why is this asked in interviews?

Amazon frequently uses this question because it mirrors real-world business requirements for marketing and loyalty programs. It tests a candidate's ability to handle multi-table joins, filter data by specific date ranges, and use aggregate functions like SUM() combined with GROUP BY and HAVING clauses. It also evaluates how you handle "AND" logic across different time slices, which is a common hurdle in SQL.

Algorithmic pattern used

This follows the Database interview pattern of "Join-Filter-Aggregate-Filter."

1. Join: Connect Orders with Product to get prices and Customers to get names.
2. Filter/Aggregate: Calculate the total spending per customer per month.
3. Cross-Month Validation: Ensure the spending threshold is met in both required months. This is often handled using a HAVING clause with conditional sums or a self-join approach.

Example explanation

Imagine Customer A buys a $60 gadget in June and a$50 book in June (Total $110). In July, they buy a$120 chair. Customer B buys $200 worth of items in June but nothing in July.

• Customer A meets the criteria ($110 in June >=$100 AND $120 in July >=$100).
• Customer B fails (nothing in July). The query would return Customer A's ID and Name.

Common mistakes candidates make

• Incorrect Date Filtering: Using simple string matches for dates instead of robust date functions like MONTH() or LEFT(order_date, 7).
• Ignoring the "Each" Condition: Successfully finding customers who spent $200 total across both months, but failing to ensure they spent at least$100 in each individual month.
• Join Logic: Forgetting to join the Product table, which is necessary to calculate the total price (quantity * price).

Interview preparation tip

When a SQL problem asks for a condition to be met in "Month A AND Month B," think about using a SUM(CASE WHEN ...) structure inside a HAVING clause. This allows you to check multiple time-based conditions in a single group-by operation.