Your question is Build Monthly Retention Cohort Table. Start with the requirements and the one table on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| customer_id | INT | Customer placing the order |
| order_date | DATE | Date the order was created |
| order_status | VARCHAR(20) | Lifecycle status of the order |
| order_total | DECIMAL(10,2) | Total value of the order |
| channel | VARCHAR(20) | Purchase channel in ACME House |