🔁 One Purchase Is Luck. Two Is Loyalty.
The growth team at Nykaa wants to understand repeat purchase behaviour. A customer is a repeat buyer if they have placed more than one order.
Calculate the total number of customers, the number of repeat buyers, and the repeat buyer rate as a percentage (rounded to 1 decimal place). Return as a single row: total_customers, repeat_buyers, repeat_rate.
You need to aggregate at the customer level first, then aggregate again at the overall level — a classic two-level aggregation pattern.
orders Table:
| Column | Type |
|---|---|
| order_id | integer |
| customer_id | integer |
| amount | integer |
| order_date | text |
Expected Outcome (Illustrative Sample):
| total_customers | repeat_buyers | repeat_rate |
|---|---|---|
| 10 | 4 | 40.0 |
This single number drives the entire retention strategy.