Find Monthly Top Customers Excluding High-Frequency Users

Question

This question asks the candidate to identify the top customers for a given month while excluding those who are classified as high-frequency users.

Codemia · Accepted Answer

To solve the problem of finding the monthly top customers while excluding high-frequency users, we first need to define our criteria for what constitutes a high-frequency user. Typically, this could be based on a threshold of orders per month. Once we have this definition, we can proceed to analyze the customer data.

1. **Define High-Frequency Users**: Identify customers who exceed a certain number of orders in a month. For example, we might define high-frequency users as those who place more than 10 orders in a month.
2. **Aggregate Customer Data**: Use SQL or Python to aggregate the total orders per customer for the specified month.
3. **Filter Out High-Frequency Users**: Exclude those customers from our results.
4. **Rank Remaining Customers**: Sort the remaining customers by their total orders to identify the top customers.

Here is a SQL query that accomplishes this task:
```sql
WITH CustomerOrders AS (
    SELECT customer_id, COUNT(order_id) AS total_orders
    FROM orders
    WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
    GROUP BY customer_id
), HighFrequencyUsers AS (
    SELECT customer_id
    FROM CustomerOrders
    WHERE total_orders > 10
)
SELECT co.customer_id, co.total_orders
FROM CustomerOrders co
LEFT JOIN HighFrequencyUsers hfu ON co.customer_id = hfu.customer_id
WHERE hfu.customer_id IS NULL
ORDER BY co.total_orders DESC
LIMIT 10;
```

Using Python with pandas, the approach would look like this:
```python
import pandas as pd

# Sample data
orders = pd.DataFrame({
    'customer_id': [...],
    'order_id': [...],
    'order_date': [...]  # add your data here
})

# Define the month
start_date = '2023-01-01'
end_date = '2023-01-31'

# Filter orders for the month
monthly_orders = orders[(orders['order_date'] >= start_date) & (orders['order_date'] <= end_date)]

# Count total orders per customer
customer_counts = monthly_orders['customer_id'].value_counts()

# Define high-frequency users
high_frequency_users = customer_counts[customer_counts > 10].index

# Filter out high-frequency users
top_customers = customer_counts[~customer_counts.index.isin(high_frequency_users)].nlargest(10)
print(top_customers)
```

Interviewers are looking for a structured approach to problem-solving, clarity in defining terms, and the ability to write efficient code or queries. They appreciate candidates who can explain their thought process and justify their choices in filtering and aggregating data. Additionally, demonstrating an understanding of customer behavior and segmentation will strengthen the candidate's response.

Find Monthly Top Customers Excluding High-Frequency Users

DoorDash

What the Interviewer Expects

Key Topics to Cover

How to Approach This

Possible Follow-up Questions

Sharpen Your Skills on Codemia

Sample Answer

Framing

Approach

Submit Your Answer

DoorDash Data Scientist Interview Guide

Related Questions

Optimize a slow query on products

Calculate conversion rate per user from orders

Window function: rank over sessions

Optimize a slow query on orders

Write a query to find time to first action from sessions