Task
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.
Schema
customer_profiles
| column | type | description |
|---|
| customer_id | INT | Unique customer identifier |
| customer_name | VARCHAR(100) | Customer name |
| region | VARCHAR(50) | Commercial region |
customer_weekly_snapshot
| column | type | description |
|---|
| snapshot_id | INT | Snapshot row identifier |
| customer_id | INT | Customer identifier |
| snapshot_date | DATE | Weekly snapshot date |
| segment | VARCHAR(50) | Customer segment in AbbVie Customer 360 |
| is_active | BOOLEAN | Whether the customer is marked active |
customer_interactions
| column | type | description |
|---|
| interaction_id | INT | Interaction row identifier |
| customer_id | INT | Customer identifier |
| interaction_date | DATE | Date of interaction |
| channel | VARCHAR(50) | Interaction channel |
| interaction_count | INT | Number of interactions recorded |
Sample data
customer_profiles
| customer_id | customer_name | region |
|---|
| 104 | Maya Patel | West |
| 101 | Elena Ruiz | East |
| 108 | Noah Bennett | South |
customer_weekly_snapshot
| snapshot_id | customer_id | snapshot_date | segment | is_active |
|---|
| 6 | 103 | 2024-05-12 | Growth | false |
| 2 | 101 | 2024-05-12 | Core | true |
| 10 | 105 | 2024-05-12 | At Risk | true |
customer_interactions
| interaction_id | customer_id | interaction_date | channel | interaction_count |
|---|
| 5 | 103 | 2024-05-11 | Email | 1 |
| 1 | 101 | 2024-05-05 | Email | 2 |
| 11 | 106 | 2024-05-12 | Phone | 0 |
Expected output
| customer_id | customer_name | latest_snapshot_date | previous_snapshot_date | current_segment | previous_segment | current_is_active | previous_is_active | current_total_interactions | previous_total_interactions | change_type |
|---|
| 101 | Elena Ruiz | 2024-05-12 | 2024-05-05 | Core | Growth | true | true | 5 | 2 | segment_changed |
| 103 | Priya Shah | 2024-05-12 | 2024-05-05 | Growth | Growth | false | true | 5 | 4 | status_changed |
| 104 | Maya Patel | 2024-05-12 | 2024-05-05 | Core | Core | true | true | 3 | 1 | interaction_count_changed |
| 105 | Jordan Lee | 2024-05-12 | null | At Risk | null | true | null | 2 | 0 | no_prior_snapshot |
