Interview Guides

Diagnose Inconsistent Customer Report | Dataford Interview Questions - Dataford - Ace your Interview

Diagnose Inconsistent Customer Report

Medium

SQL & Data Manipulation

Asked at 9 companies9JoinsData Wrangling

Also asked at

Problem

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

Schema

customer_profiles

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
region	VARCHAR(50)	Commercial region

customer_weekly_snapshot

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

customer_interactions

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

customer_profiles

customer_id	customer_name	region
104	Maya Patel	West
101	Elena Ruiz	East
108	Noah Bennett	South
103	Priya Shah	Central
106	Owen Clark
102	Marcus Hill	East
105	Jordan Lee	West
107	Ava Nguyen	North
1	Chloe Martin	null
2	Liam Foster	Southeast
3	Ethan Brooks	null
4	David Ross	Great Lakes
5	Benjamin Cox	International
6	Priya Shah	Territory B
7	Maya Patel	International
8	Elena Ruiz	South
9	Owen Clark	null
10	Samuel Price	Plains
11	Nora Simmons	Rural
12	Ava Nguyen	Southwest
13	Ava Nguyen	Rural
14	Benjamin Cox	North
15	Ella Ramirez	Urban
16	Lucas Adams	Atlantic
17	Maya Patel	Rural
18	Henry Ward	North
19	Isabella Reed	Lower Midwest
20	Priya Shah	null

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
3	101	2024-05-10	Call	3
8	104	2024-05-09	Portal	2
2	101	2024-05-15	Portal	4
6	103	2024-05-04	Field	4
10	105	2024-05-12	Email	2
4	102	2024-05-03	Email	1
7	104	2024-05-05	Call	1
9		2024-05-08	Email	5
12	108	2024-05-06
1	28	2024-05-07	Conference	87
2	26	2024-05-03	Direct Mail	50
3	17	2024-05-12	Partner	29
4	2	2024-05-15	Mobile App	38
5	50	2024-05-07	Phone	95
6	12	2024-05-04	Inbound	56
7	15	2024-05-15	Inbound	2
8	64	2024-05-07	Inbound	5
9	65	2024-05-01	In-Person	62
10	36	2024-04-30	Portal	90
11	52	2024-05-01	Email	70
12	99	2024-05-10	Direct Mail	8
13	19	2024-05-10	null	77
14	61	2024-05-16	null	93
15	21	2024-05-06	Pharmacy	72
16	45	2024-05-16	Mobile App	98
17	84	2024-05-14	Pharmacy	27

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
8	104	2024-05-12	Core	true
4	102	2024-05-12	Core	true
1	101	2024-05-05	Growth	true
7	104	2024-05-05	Core	true
3	102	2024-05-05	Core	true
5	103	2024-05-05	Growth	true
9	106	2024-05-12		true
1	31	2024-05-14	Prospect	true
2	18	2024-05-14	Emerging	false
3	73	2024-05-09	null	null
4	3	2024-05-05	null	false
5	24	2024-05-08	Emerging	true
6	6	2024-05-08	Dormant	true
7	33	2024-05-15	Priority	false
8	73	2024-05-09	Prospect	true
9	12	2024-05-09	Unknown	false
10	41	2024-05-10	null	null
11	68	2024-05-12	Trial	null
12	11	2024-05-08	Retention	false
13	47	2024-05-04	Priority	null
14	15	2024-05-15	Digital First	null
15	60	2024-05-06	Occasional	true
16	65	2024-05-13	null	true
17	11	2024-05-14	Prospect	true
18	18	2024-05-03	Digital First	false
19	7	2024-05-06	Loyal	false
20	14	2024-05-13	SMB	false

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
7	Maya Patel	2024-05-06	null	Loyal	null	False	null	0	0	no_prior_snapshot
11	Nora Simmons	2024-05-14	2024-05-08	Prospect	Retention	True	False	0	0	segment_changed
14	Benjamin Cox	2024-05-13	null	SMB	null	False	null	0	0	no_prior_snapshot
15	Ella Ramirez	2024-05-15	null	Digital First	null	null	null	0	0	no_prior_snapshot
18	Henry Ward	2024-05-03	null	Digital First	null	False	null	0	0	no_prior_snapshot
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
106	Owen Clark	2024-05-12	null	null	null	True	null	0	0	no_prior_snapshot

Problem

Task

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

Schema

customer_profiles

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
region	VARCHAR(50)	Commercial region

customer_weekly_snapshot

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

customer_interactions

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

customer_profiles

customer_id	customer_name	region
104	Maya Patel	West
101	Elena Ruiz	East
108	Noah Bennett	South
103	Priya Shah	Central
106	Owen Clark
102	Marcus Hill	East
105	Jordan Lee	West
107	Ava Nguyen	North
1	Chloe Martin	null
2	Liam Foster	Southeast
3	Ethan Brooks	null
4	David Ross	Great Lakes
5	Benjamin Cox	International
6	Priya Shah	Territory B
7	Maya Patel	International
8	Elena Ruiz	South
9	Owen Clark	null
10	Samuel Price	Plains
11	Nora Simmons	Rural
12	Ava Nguyen	Southwest
13	Ava Nguyen	Rural
14	Benjamin Cox	North
15	Ella Ramirez	Urban
16	Lucas Adams	Atlantic
17	Maya Patel	Rural
18	Henry Ward	North
19	Isabella Reed	Lower Midwest
20	Priya Shah	null

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
3	101	2024-05-10	Call	3
8	104	2024-05-09	Portal	2
2	101	2024-05-15	Portal	4
6	103	2024-05-04	Field	4
10	105	2024-05-12	Email	2
4	102	2024-05-03	Email	1
7	104	2024-05-05	Call	1
9		2024-05-08	Email	5
12	108	2024-05-06
1	28	2024-05-07	Conference	87
2	26	2024-05-03	Direct Mail	50
3	17	2024-05-12	Partner	29
4	2	2024-05-15	Mobile App	38
5	50	2024-05-07	Phone	95
6	12	2024-05-04	Inbound	56
7	15	2024-05-15	Inbound	2
8	64	2024-05-07	Inbound	5
9	65	2024-05-01	In-Person	62
10	36	2024-04-30	Portal	90
11	52	2024-05-01	Email	70
12	99	2024-05-10	Direct Mail	8
13	19	2024-05-10	null	77
14	61	2024-05-16	null	93
15	21	2024-05-06	Pharmacy	72
16	45	2024-05-16	Mobile App	98
17	84	2024-05-14	Pharmacy	27

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
8	104	2024-05-12	Core	true
4	102	2024-05-12	Core	true
1	101	2024-05-05	Growth	true
7	104	2024-05-05	Core	true
3	102	2024-05-05	Core	true
5	103	2024-05-05	Growth	true
9	106	2024-05-12		true
1	31	2024-05-14	Prospect	true
2	18	2024-05-14	Emerging	false
3	73	2024-05-09	null	null
4	3	2024-05-05	null	false
5	24	2024-05-08	Emerging	true
6	6	2024-05-08	Dormant	true
7	33	2024-05-15	Priority	false
8	73	2024-05-09	Prospect	true
9	12	2024-05-09	Unknown	false
10	41	2024-05-10	null	null
11	68	2024-05-12	Trial	null
12	11	2024-05-08	Retention	false
13	47	2024-05-04	Priority	null
14	15	2024-05-15	Digital First	null
15	60	2024-05-06	Occasional	true
16	65	2024-05-13	null	true
17	11	2024-05-14	Prospect	true
18	18	2024-05-03	Digital First	false
19	7	2024-05-06	Loyal	false
20	14	2024-05-13	SMB	false

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
7	Maya Patel	2024-05-06	null	Loyal	null	False	null	0	0	no_prior_snapshot
11	Nora Simmons	2024-05-14	2024-05-08	Prospect	Retention	True	False	0	0	segment_changed
14	Benjamin Cox	2024-05-13	null	SMB	null	False	null	0	0	no_prior_snapshot
15	Ella Ramirez	2024-05-15	null	Digital First	null	null	null	0	0	no_prior_snapshot
18	Henry Ward	2024-05-03	null	Digital First	null	False	null	0	0	no_prior_snapshot
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
106	Owen Clark	2024-05-12	null	null	null	True	null	0	0	no_prior_snapshot

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Diagnose Cross-System Data DiscrepanciesHard

Track Customer Pain Points MonthlyMedium

Repeat Support Issues by CustomerMedium

Next question

Diagnose Inconsistent Customer Report

Medium

SQL & Data Manipulation

Asked at 9 companies9JoinsData Wrangling

Also asked at

Problem

Task

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

Schema

customer_profiles

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
region	VARCHAR(50)	Commercial region

customer_weekly_snapshot

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

customer_interactions

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

customer_profiles

customer_id	customer_name	region
104	Maya Patel	West
101	Elena Ruiz	East
108	Noah Bennett	South
103	Priya Shah	Central
106	Owen Clark
102	Marcus Hill	East
105	Jordan Lee	West
107	Ava Nguyen	North
1	Chloe Martin	null
2	Liam Foster	Southeast
3	Ethan Brooks	null
4	David Ross	Great Lakes
5	Benjamin Cox	International
6	Priya Shah	Territory B
7	Maya Patel	International
8	Elena Ruiz	South
9	Owen Clark	null
10	Samuel Price	Plains
11	Nora Simmons	Rural
12	Ava Nguyen	Southwest
13	Ava Nguyen	Rural
14	Benjamin Cox	North
15	Ella Ramirez	Urban
16	Lucas Adams	Atlantic
17	Maya Patel	Rural
18	Henry Ward	North
19	Isabella Reed	Lower Midwest
20	Priya Shah	null

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
3	101	2024-05-10	Call	3
8	104	2024-05-09	Portal	2
2	101	2024-05-15	Portal	4
6	103	2024-05-04	Field	4
10	105	2024-05-12	Email	2
4	102	2024-05-03	Email	1
7	104	2024-05-05	Call	1
9		2024-05-08	Email	5
12	108	2024-05-06
1	28	2024-05-07	Conference	87
2	26	2024-05-03	Direct Mail	50
3	17	2024-05-12	Partner	29
4	2	2024-05-15	Mobile App	38
5	50	2024-05-07	Phone	95
6	12	2024-05-04	Inbound	56
7	15	2024-05-15	Inbound	2
8	64	2024-05-07	Inbound	5
9	65	2024-05-01	In-Person	62
10	36	2024-04-30	Portal	90
11	52	2024-05-01	Email	70
12	99	2024-05-10	Direct Mail	8
13	19	2024-05-10	null	77
14	61	2024-05-16	null	93
15	21	2024-05-06	Pharmacy	72
16	45	2024-05-16	Mobile App	98
17	84	2024-05-14	Pharmacy	27

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
8	104	2024-05-12	Core	true
4	102	2024-05-12	Core	true
1	101	2024-05-05	Growth	true
7	104	2024-05-05	Core	true
3	102	2024-05-05	Core	true
5	103	2024-05-05	Growth	true
9	106	2024-05-12		true
1	31	2024-05-14	Prospect	true
2	18	2024-05-14	Emerging	false
3	73	2024-05-09	null	null
4	3	2024-05-05	null	false
5	24	2024-05-08	Emerging	true
6	6	2024-05-08	Dormant	true
7	33	2024-05-15	Priority	false
8	73	2024-05-09	Prospect	true
9	12	2024-05-09	Unknown	false
10	41	2024-05-10	null	null
11	68	2024-05-12	Trial	null
12	11	2024-05-08	Retention	false
13	47	2024-05-04	Priority	null
14	15	2024-05-15	Digital First	null
15	60	2024-05-06	Occasional	true
16	65	2024-05-13	null	true
17	11	2024-05-14	Prospect	true
18	18	2024-05-03	Digital First	false
19	7	2024-05-06	Loyal	false
20	14	2024-05-13	SMB	false

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
7	Maya Patel	2024-05-06	null	Loyal	null	False	null	0	0	no_prior_snapshot
11	Nora Simmons	2024-05-14	2024-05-08	Prospect	Retention	True	False	0	0	segment_changed
14	Benjamin Cox	2024-05-13	null	SMB	null	False	null	0	0	no_prior_snapshot
15	Ella Ramirez	2024-05-15	null	Digital First	null	null	null	0	0	no_prior_snapshot
18	Henry Ward	2024-05-03	null	Digital First	null	False	null	0	0	no_prior_snapshot
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
106	Owen Clark	2024-05-12	null	null	null	True	null	0	0	no_prior_snapshot

Problem

Task

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

Schema

customer_profiles

Column	Type	Description
customer_idPK	INT	Unique customer identifier
customer_name	VARCHAR(100)	Customer full name
region	VARCHAR(50)	Commercial region

customer_weekly_snapshot

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

customer_interactions

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

customer_profiles

customer_id	customer_name	region
104	Maya Patel	West
101	Elena Ruiz	East
108	Noah Bennett	South
103	Priya Shah	Central
106	Owen Clark
102	Marcus Hill	East
105	Jordan Lee	West
107	Ava Nguyen	North
1	Chloe Martin	null
2	Liam Foster	Southeast
3	Ethan Brooks	null
4	David Ross	Great Lakes
5	Benjamin Cox	International
6	Priya Shah	Territory B
7	Maya Patel	International
8	Elena Ruiz	South
9	Owen Clark	null
10	Samuel Price	Plains
11	Nora Simmons	Rural
12	Ava Nguyen	Southwest
13	Ava Nguyen	Rural
14	Benjamin Cox	North
15	Ella Ramirez	Urban
16	Lucas Adams	Atlantic
17	Maya Patel	Rural
18	Henry Ward	North
19	Isabella Reed	Lower Midwest
20	Priya Shah	null

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
3	101	2024-05-10	Call	3
8	104	2024-05-09	Portal	2
2	101	2024-05-15	Portal	4
6	103	2024-05-04	Field	4
10	105	2024-05-12	Email	2
4	102	2024-05-03	Email	1
7	104	2024-05-05	Call	1
9		2024-05-08	Email	5
12	108	2024-05-06
1	28	2024-05-07	Conference	87
2	26	2024-05-03	Direct Mail	50
3	17	2024-05-12	Partner	29
4	2	2024-05-15	Mobile App	38
5	50	2024-05-07	Phone	95
6	12	2024-05-04	Inbound	56
7	15	2024-05-15	Inbound	2
8	64	2024-05-07	Inbound	5
9	65	2024-05-01	In-Person	62
10	36	2024-04-30	Portal	90
11	52	2024-05-01	Email	70
12	99	2024-05-10	Direct Mail	8
13	19	2024-05-10	null	77
14	61	2024-05-16	null	93
15	21	2024-05-06	Pharmacy	72
16	45	2024-05-16	Mobile App	98
17	84	2024-05-14	Pharmacy	27

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
8	104	2024-05-12	Core	true
4	102	2024-05-12	Core	true
1	101	2024-05-05	Growth	true
7	104	2024-05-05	Core	true
3	102	2024-05-05	Core	true
5	103	2024-05-05	Growth	true
9	106	2024-05-12		true
1	31	2024-05-14	Prospect	true
2	18	2024-05-14	Emerging	false
3	73	2024-05-09	null	null
4	3	2024-05-05	null	false
5	24	2024-05-08	Emerging	true
6	6	2024-05-08	Dormant	true
7	33	2024-05-15	Priority	false
8	73	2024-05-09	Prospect	true
9	12	2024-05-09	Unknown	false
10	41	2024-05-10	null	null
11	68	2024-05-12	Trial	null
12	11	2024-05-08	Retention	false
13	47	2024-05-04	Priority	null
14	15	2024-05-15	Digital First	null
15	60	2024-05-06	Occasional	true
16	65	2024-05-13	null	true
17	11	2024-05-14	Prospect	true
18	18	2024-05-03	Digital First	false
19	7	2024-05-06	Loyal	false
20	14	2024-05-13	SMB	false

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
7	Maya Patel	2024-05-06	null	Loyal	null	False	null	0	0	no_prior_snapshot
11	Nora Simmons	2024-05-14	2024-05-08	Prospect	Retention	True	False	0	0	segment_changed
14	Benjamin Cox	2024-05-13	null	SMB	null	False	null	0	0	no_prior_snapshot
15	Ella Ramirez	2024-05-15	null	Digital First	null	null	null	0	0	no_prior_snapshot
18	Henry Ward	2024-05-03	null	Digital First	null	False	null	0	0	no_prior_snapshot
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
106	Owen Clark	2024-05-12	null	null	null	True	null	0	0	no_prior_snapshot

Practice SQL

PostgreSQL

Open on desktop for the full SQL editor with syntax highlighting and autocomplete.

Up next

Diagnose Cross-System Data DiscrepanciesHard

Track Customer Pain Points MonthlyMedium

Repeat Support Issues by CustomerMedium

Next question