Frequent Customers - SQL to Identify High-Frequency Repeat Orderers

Question

## Round 1 - SQL

## Problem

You have the following DoorDash schema:

```sql
CREATE TABLE customers (customer_id INT, name VARCHAR, city VARCHAR, joined_date DATE);
CREATE TABLE orders    (order_id INT, customer_id INT, restaurant_id INT,
                        order_total DECIMAL, placed_at TIMESTAMP);
```

**Task 1:** Find all customers who placed orders on at least 5 distinct days in the past 30 days.

**Task 2:** For each city, find the customer with the highest number of orders this month. Break ties by customer_id ascending.

**Task 3:** Find customers who ordered from the same restaurant at least 3 times. Return customer_id, restaurant_id, and order_count.

```sql
-- Task 1 skeleton:
SELECT customer_id
FROM orders
WHERE placed_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY customer_id
HAVING COUNT(DISTINCT DATE(placed_at)) >= 5;
```

```sql
-- Task 2 skeleton:
WITH ranked AS (
  SELECT c.city, o.customer_id,
         COUNT(*) AS order_count,
         RANK() OVER (PARTITION BY c.city ORDER BY COUNT(*) DESC, o.customer_id ASC) AS rnk
  FROM orders o JOIN customers c USING (customer_id)
  WHERE DATE_TRUNC('month', o.placed_at) = DATE_TRUNC('month', CURRENT_DATE)
  GROUP BY c.city, o.customer_id
)
SELECT city, customer_id, order_count FROM ranked WHERE rnk = 1;
```

## Follow-ups

1. In Task 1, why use `COUNT(DISTINCT DATE(placed_at))` instead of `COUNT(*)`?
2. In Task 2, what is the difference between `RANK()` and `ROW_NUMBER()` for tie-breaking?
3. For Task 3, how would you find customers who ordered the same restaurant on consecutive days?
4. How would you optimize these queries on a 500M-row orders table?

Frequent Customers - SQL to Identify High-Frequency Repeat Orderers

Question Details

Full Details

Topics

More from DoorDash