Your question is Diagnose Inconsistent Customer Report. Start with the requirements and the three tables 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.
A customer report in AbbVie Customer 360 suddenly looks inconsistent from one week to the next. You are asked to write a SQL query that surfaces the first places you would investigate: customers whose latest weekly snapshot has a different segment, a different active flag, or a different total interaction count than the prior snapshot. Return one row per customer for the most recent snapshot date, along with the prior snapshot date, the current and previous values, and a change_type label showing whether the inconsistency is driven by a segment change, status change, interaction count change, or no prior snapshot.
Use the latest two snapshots available for each customer. Include only customers whose latest snapshot exists in the data, and count interactions that occurred on or before each snapshot date.
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer full name |
| region | VARCHAR(50) | Commercial region |
| Column | Type | Description |
|---|---|---|
| snapshot_idPK | INT | Snapshot row identifier |
| customer_id | INT | References customer_profiles.customer_id |
| snapshot_date | DATE | Weekly snapshot date |
| segment | VARCHAR(50) | Customer segment in AbbVie Customer 360 |
| is_active | BOOLEAN | Whether the customer is marked active |
| Column | Type | Description |
|---|---|---|
| interaction_idPK | INT | Interaction row identifier |
| customer_id | INT | References customer_profiles.customer_id |
| interaction_date | DATE | Date of interaction |
| channel | VARCHAR(50) | Interaction channel |
| interaction_count | INT | Number of interactions recorded |