Your question is Top Customer Cohorts by Revenue. 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 a table of customer activity from PNC digital marketing campaigns. Write a SQL query to identify the top-performing customer cohorts, where a cohort is defined by cohort_month. Return each cohort's total revenue and customer count, sorted from highest total revenue to lowest, and show only the top 3 cohorts.
Because this is an easy SQL exercise, solve it using only a single table with aggregation, sorting, and limiting.
| Column | Type | Description |
|---|---|---|
| activity_idPK | INT | Unique activity row identifier |
| customer_id | INT | Customer identifier |
| cohort_month | VARCHAR(7) | Customer acquisition cohort in YYYY-MM format |
| channel | VARCHAR(30) | Marketing acquisition channel |
| revenue | DECIMAL(10,2) | Revenue attributed to the activity |
| activity_date | DATE | Date of customer activity |