Katkat
Back

Find Monthly Top Customers Excluding High-Frequency Users

DoorDashData Scientist · Technical Screen
EasyData ScientistTechnical ScreenData Manipulation (SQL/Python)

Last updated

This problem tests proficiency in window functions, filtering based on aggregated metrics, and handling ranking logic in SQL.

Problem Statement

Given a table orders with columns order_id, customer_id, order_date (format 'YYYY-MM-DD'), and amount, write a SQL query to identify the top customer by total spending for each month.

However, you must exclude any customer_id that is considered a 'high-frequency user.' A high-frequency user is defined as any customer who has placed more than 10 orders in that specific month.

Input Schema

ColumnType
order_idINT
customer_idINT
order_dateDATE
amountDECIMAL

Requirements

  • The output should contain: month (format 'YYYY-MM'), customer_id, and total_spend.
  • If multiple customers are tied for the highest spending in a month after filtering, return all of them.
  • Rank by total_spend in descending order.

Example

Input: Some users have 12 orders in January (high frequency), others have 3. If customer 101 spent $500 with 5 orders, and customer 102 spent $600 with 12 orders, the query should only return 101 for that month.

Output:

monthcustomer_idtotal_spend
2023-01101500.00

Edge Cases

  • A month where all customers are 'high-frequency' (result should be empty for that month).
  • Ties in total_spend.

Hints

Hint 1

Use a Common Table Expression (CTE) to calculate the monthly order count and total spend for each customer first.

Hint 2

Filter the CTE to exclude customers with order counts greater than 10 before performing the ranking.

Hint 3

Use the DENSE_RANK() window function partitioned by month to identify the top spending customer(s).

Follow-up questions

  • How would your query change if the definition of a high-frequency user was based on their total spend rather than their order count?
  • If the dataset grows to billions of rows, what indexes would you create to optimize this specific query?
  • How would you handle the requirement if you needed to return the top 3 customers instead of just the top 1?