Task
You are given ACME House customer order activity and asked to build a monthly retention cohort table in SQL. Define each customer's cohort as the month of their first completed order in ACME House. Then calculate how many customers from each cohort returned in month 0, month 1, month 2, and month 3 based on completed orders only, along with the retention rate for each month as a percentage of the cohort size. Exclude canceled orders from both cohort assignment and retention activity. Return one row per cohort month, ordered chronologically.
Schema
| column | type | description |
|---|
| order_id | INT | Unique order identifier |
| customer_id | INT | Customer placing the order |
| order_date | DATE | Date the order was created |
| order_status | VARCHAR(20) | Order status such as completed, canceled, or pending |
| order_total | DECIMAL(10,2) | Order amount |
| channel | VARCHAR(20) | Purchase channel in ACME House |
Sample data
| order_id | customer_id | order_date | order_status | order_total | channel |
|---|
| 1003 | 2 | 2024-01-20 | completed | 80.00 | mobile_app |
| 1001 | 1 | 2024-01-05 | completed | 120.00 | web |
| 1007 | 4 | 2024-02-10 | completed | 90.00 | mobile_app |
| 1010 | 5 | 2024-03-01 | completed | 60.00 | web |
| 1013 | 7 | 2024-03-15 | completed | 200.00 | showroom |
Expected output
| cohort_month | cohort_size | retained_m0 | retained_m1 | retained_m2 | retained_m3 | retention_rate_m0 | retention_rate_m1 | retention_rate_m2 | retention_rate_m3 |
|---|
| 2024-01 | 3 | 3 | 2 | 1 | 1 | 100.00 | 66.67 | 33.33 | 33.33 |
| 2024-02 | 2 | 2 | 2 | 0 | 0 | 100.00 | 100.00 | 0.00 | 0.00 |
| 2024-03 | 2 | 2 | 1 | 0 | 0 | 100.00 | 50.00 | 0.00 | 0.00 |
