Calculate the Monthly Share of High-Frequency Customers

Question

This question asks the candidate to calculate the monthly share of high-frequency customers using SQL or Python. The candidate should demonstrate their ability to manipulate data effectively to derive meaningful insights.

Codemia · Accepted Answer

To calculate the monthly share of high-frequency customers, we first need to define who qualifies as a high-frequency customer. Typically, this could be customers who place a certain number of orders within a month. For this example, let's define high-frequency customers as those who place more than 5 orders in a month.

We will use SQL to extract the necessary data from the database. The steps include:
1. Identify the total number of customers for the month.
2. Identify the number of high-frequency customers.
3. Calculate the share by dividing the number of high-frequency customers by the total number of customers.

Here is a sample SQL query that accomplishes this:
```sql
SELECT 
    COUNT(DISTINCT customer_id) AS total_customers,
    COUNT(DISTINCT CASE WHEN order_count > 5 THEN customer_id END) AS high_frequency_customers,
    (COUNT(DISTINCT CASE WHEN order_count > 5 THEN customer_id END) * 1.0 / COUNT(DISTINCT customer_id)) AS high_frequency_share
FROM 
    (SELECT customer_id, COUNT(order_id) AS order_count
     FROM orders
     WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31'
     GROUP BY customer_id) AS monthly_orders;
```

In this query, we first calculate the total number of customers and the number of high-frequency customers by counting distinct customer IDs. The subquery aggregates the orders by customer for the specified month. The final calculation gives us the share of high-frequency customers as a proportion of total customers.

Interviewers will look for clarity in your thought process, the ability to write effective SQL queries, and a solid understanding of customer metrics. They may also assess your ability to explain your logic and any assumptions made during the calculation.

Calculate the Monthly Share of High-Frequency Customers

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