Problem
ShopKart wants to measure how many shoppers return in the following week after making a purchase. Write a SQL query to calculate week-over-week shopper retention.
A shopper is considered retained in week W if they made at least one completed order in week W and also made at least one completed order in week W + 1.
Requirements
- Use only completed orders.
- Aggregate activity by shopper and calendar week using
DATE_TRUNC('week', order_date). - For each week, return:
- the week start date
- the number of active shoppers in that week
- the number of those shoppers who returned the next week
- the retention rate as
retained_shoppers / active_shoppers, rounded to 4 decimals
- Exclude the final week in the dataset if there is no following week to evaluate retention against.
- Order results by week start date ascending.
Schema
shoppers
| Column | Type | Description |
|---|---|---|
| shopper_idPK | INT | Unique shopper identifier |
| shopper_name | VARCHAR(100) | Full name of the shopper |
| signup_date | DATE | Date the shopper signed up |
| region | VARCHAR(50) | Shopper region |
orders
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| shopper_id | INT | Shopper who placed the order |
| order_date | DATE | Date the order was placed |
| order_status | VARCHAR(20) | Order lifecycle status |
| order_total | NUMERIC(10,2) | Total order amount |
Practicing as: Product Growth Analyst interview at SpliceHi, I'll play your Splice interviewer for the Product Growth Analyst role. Candidates describe these interviews as mostly positive and moderately difficult, so expect me to be friendly and conversational. Take your time with the question above and answer like we're in the room.
You are practicing as a guest. Sign up free to run your code against the sample data. Your draft stays right here.
