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.
DATE_TRUNC('week', order_date).retained_shoppers / active_shoppers, rounded to 4 decimals| 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 |
| 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 |