Interview Guides

Diagnose Attribution Event Drop | Dataford Interview Questions - Dataford - Ace your Interview

Diagnose Attribution Event Drop

Medium

SQL & Data Manipulation

JoinsAggregationsDate Functions

Problem

Task

StreamWave's analytics team noticed attribution events are down by roughly 15% in the last 24 hours. Write a SQL query to identify the likely root cause by comparing the most recent 24-hour window to the prior 24-hour window.

Your query should surface the dimensions with the largest absolute drop in successful attribution events so an analyst can quickly see whether the issue is concentrated in a specific source, platform, or pipeline status.

Requirements

Compare successful attribution events in the last 24 hours against the previous 24 hours.
Break results out by source, platform, and status.
Return only rows where the last 24 hours had fewer successful events than the prior 24 hours.
Include the absolute drop and percentage drop, ordered by the largest absolute drop first.

Table Definitions

`attribution_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User tied to the attribution event
campaign_id	INT	Campaign tied to the event
event_time	TIMESTAMP	Time the attribution event was recorded
source	VARCHAR(50)	Attribution source such as SDK or API
platform	VARCHAR(20)	Client platform
status	VARCHAR(20)	Processing status

`campaigns`

column	type	description
campaign_id	INT	Campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Marketing channel
is_active	BOOLEAN	Whether the campaign is active

`users`

column	type	description
user_id	INT	User identifier
country	VARCHAR(50)	User country
signup_platform	VARCHAR(20)	User signup platform
is_test_user	BOOLEAN	Whether the user is a test account

Sample Data

`attribution_events`

event_id	user_id	campaign_id	event_time	source	platform	status
1001	1	101	2024-06-10 10:00:00	sdk	ios	attributed
1002	2	101	2024-06-10 11:00:00	sdk	ios	attributed
1003	3	102	2024-06-10 12:00:00	api	android	attributed
1004	4	103	2024-06-10 13:00:00	sdk	web	failed
1005	5	104	2024-06-11 02:00:00	sdk	ios	attributed
1006	6	102	2024-06-11 03:00:00	api	android	failed

Expected Output

source	platform	status	previous_24h_events	last_24h_events	absolute_drop	pct_drop
sdk	ios	attributed	4	1	3	75.00
api	android	attributed	2	1	1	50.00

Schema

attribution_events

Column	Type	Description
event_idPK	INT	Unique attribution event identifier
user_id	INT	User associated with the event
campaign_id	INT	Campaign associated with the event
event_time	TIMESTAMP	Timestamp when the attribution event was recorded
source	VARCHAR(50)	Attribution source
platform	VARCHAR(20)	Client platform
status	VARCHAR(20)	Processing status of the attribution event

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Marketing channel
is_active	BOOLEAN	Whether the campaign is currently active

users

Column	Type	Description
user_idPK	INT	Unique user identifier
country	VARCHAR(50)	User country
signup_platform	VARCHAR(20)	Platform used at signup
is_test_user	BOOLEAN	Whether the user is a test account

users

user_id	country	signup_platform	is_test_user
1	US	ios	false
2	CA	ios	false
3	US	android	false
4	GB	web	false
5	DE	web	false
6	FR	web	null
7	US	ios	true
8	IN	ios	false
9	BR	ios	false
10	US	ios	false
11	JP	android	false
12	US	ios	false
13	IT	kiosk	false
14	KR	fire_tv	false
15	PT	kiosk	true
16	DE	roku	false
17	JP	partner_portal	false
18	DK	android	false
19	ZA	sms	false
20	GB	sms	null
21	PL	null	false
22	DE	windows	false
23	US	sms	true
24	IN	null	true
25	null	null	true
26	ES	console	false
27	IN	ios	false
28	null	web	true
29	BR	partner_portal	false
30	null	kiosk	false

campaigns

campaign_id	campaign_name	channel	is_active
104	Affiliate Push	affiliate	false
101	Summer Launch	paid_social	true
103	Winback Email	email	true
106	Dormant Search	search	false
102	Android Retargeting	paid_search	true
105	Legacy iOS Burst	display	false
107	Brand Awareness	video	true
108	Referral Boost	referral	true
109	Affiliate Push	nonbrand_search	false
110	CTV Expansion	display	true
111	Retention Booster	out_of_home	false
112	Creator Partnership	display	false
113	Paid Search Core	podcast	false
114	Brand Awareness	email	true
115	CTV Expansion	tv	false
116	Search Nonbrand	organic_search	true
117	Creator Partnership	podcast	true
118	Install Surge	community	true
119	CTV Expansion	direct_mail	false
120	Paid Search Core	partnership	true
121	Legacy iOS Burst	referral	false
122	Premium Upsell	organic_search	false
123	Email Reactivation	null	false
124	Geo Test Campaign	video	false
125	Premium Upsell	paid_social	false
126	Referral Boost	push	false

attribution_events

event_id	user_id	campaign_id	event_time	source	platform	status
1008	8	101	2024-06-10 11:30:00	sdk	ios	attributed
1003	3	102	2024-06-09 16:00:00	api	android	attributed
1011	11	102	2024-06-10 15:00:00	api	android	attributed
1001	1	101	2024-06-09 13:00:00	sdk	ios	attributed
1010	10	101	2024-06-10 14:00:00	sdk	ios	attributed
1006	6	104	2024-06-10 08:00:00	partner	web	attributed
1004	4	103	2024-06-09 18:00:00	sdk	web	attributed
1007	7	101	2024-06-10 10:00:00	sdk	ios	attributed
1012	12	101	2024-06-10 16:00:00	sdk	ios	failed
1002	2	101	2024-06-09 14:00:00	sdk	ios	attributed
1005	5	104	2024-06-10 02:00:00	partner	web	attributed
1009	9	105	2024-06-10 13:00:00	sdk	ios	attributed
1013	6	101	2024-06-07 12:56:02	event_bridge	null	pending
1014	4	101	2024-06-08 12:15:10	gtm	windows	duplicate
1015	2	101	2024-06-11 17:09:51	redirect	null	errored
1016	10	101	2024-06-07 10:09:54	s2s	api_client	reprocessed
1017	7	104	2024-06-13 05:16:32	null	ios_webview	resolved
1018	4	101	2024-06-08 17:43:55	mmp	console	pending
1019	3	103	2024-06-12 11:00:41	null	android_tv	blocked
1020	5	103	2024-06-12 19:00:42	ios_sdk	desktop	rejected
1021	9	102	2024-06-11 04:31:31	gtm	backend	duplicate
1022	6	101	2024-06-08 06:10:50	ad_network	null	attributed
1023	5	102	2024-06-07 14:13:30	null	mobile_web	null
1024	10	101	2024-06-08 07:13:28	null	android_tv	matched
1025	12	104	2024-06-07 21:27:30	s2s	roku	accepted
1026	7	101	2024-06-09 16:24:04	api	carplay	attributed
1027	1	103	2024-06-06 16:39:05	null	smart_tv	blocked
1028	6	102	2024-06-09 13:15:21	manual_upload	tablet	pending
1029	2	101	2024-06-12 19:39:16	pixel	email	validated
1030	4	101	2024-06-07 21:17:37	internal_tool	android_webview	blocked

Expected Output

source	platform	status	previous_24h_events	last_24h_events	absolute_drop	pct_drop
sdk	ios	attributed	3	1	2	66.67
sdk	web	attributed	1	0	1	100.00

Problem

Task

Requirements

Compare successful attribution events in the last 24 hours against the previous 24 hours.
Break results out by source, platform, and status.
Return only rows where the last 24 hours had fewer successful events than the prior 24 hours.
Include the absolute drop and percentage drop, ordered by the largest absolute drop first.

Table Definitions

`attribution_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User tied to the attribution event
campaign_id	INT	Campaign tied to the event
event_time	TIMESTAMP	Time the attribution event was recorded
source	VARCHAR(50)	Attribution source such as SDK or API
platform	VARCHAR(20)	Client platform
status	VARCHAR(20)	Processing status

`campaigns`

column	type	description
campaign_id	INT	Campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Marketing channel
is_active	BOOLEAN	Whether the campaign is active

`users`

column	type	description
user_id	INT	User identifier
country	VARCHAR(50)	User country
signup_platform	VARCHAR(20)	User signup platform
is_test_user	BOOLEAN	Whether the user is a test account

Sample Data

`attribution_events`

event_id	user_id	campaign_id	event_time	source	platform	status
1001	1	101	2024-06-10 10:00:00	sdk	ios	attributed
1002	2	101	2024-06-10 11:00:00	sdk	ios	attributed
1003	3	102	2024-06-10 12:00:00	api	android	attributed
1004	4	103	2024-06-10 13:00:00	sdk	web	failed
1005	5	104	2024-06-11 02:00:00	sdk	ios	attributed
1006	6	102	2024-06-11 03:00:00	api	android	failed

Expected Output

source	platform	status	previous_24h_events	last_24h_events	absolute_drop	pct_drop
sdk	ios	attributed	4	1	3	75.00
api	android	attributed	2	1	1	50.00

Schema

attribution_events

Column	Type	Description
event_idPK	INT	Unique attribution event identifier
user_id	INT	User associated with the event
campaign_id	INT	Campaign associated with the event
event_time	TIMESTAMP	Timestamp when the attribution event was recorded
source	VARCHAR(50)	Attribution source
platform	VARCHAR(20)	Client platform
status	VARCHAR(20)	Processing status of the attribution event

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Marketing channel
is_active	BOOLEAN	Whether the campaign is currently active

users

Column	Type	Description
user_idPK	INT	Unique user identifier
country	VARCHAR(50)	User country
signup_platform	VARCHAR(20)	Platform used at signup
is_test_user	BOOLEAN	Whether the user is a test account

users

user_id	country	signup_platform	is_test_user
1	US	ios	false
2	CA	ios	false
3	US	android	false
4	GB	web	false
5	DE	web	false
6	FR	web	null
7	US	ios	true
8	IN	ios	false
9	BR	ios	false
10	US	ios	false
11	JP	android	false
12	US	ios	false
13	IT	kiosk	false
14	KR	fire_tv	false
15	PT	kiosk	true
16	DE	roku	false
17	JP	partner_portal	false
18	DK	android	false
19	ZA	sms	false
20	GB	sms	null
21	PL	null	false
22	DE	windows	false
23	US	sms	true
24	IN	null	true
25	null	null	true
26	ES	console	false
27	IN	ios	false
28	null	web	true
29	BR	partner_portal	false
30	null	kiosk	false

campaigns

campaign_id	campaign_name	channel	is_active
104	Affiliate Push	affiliate	false
101	Summer Launch	paid_social	true
103	Winback Email	email	true
106	Dormant Search	search	false
102	Android Retargeting	paid_search	true
105	Legacy iOS Burst	display	false
107	Brand Awareness	video	true
108	Referral Boost	referral	true
109	Affiliate Push	nonbrand_search	false
110	CTV Expansion	display	true
111	Retention Booster	out_of_home	false
112	Creator Partnership	display	false
113	Paid Search Core	podcast	false
114	Brand Awareness	email	true
115	CTV Expansion	tv	false
116	Search Nonbrand	organic_search	true
117	Creator Partnership	podcast	true
118	Install Surge	community	true
119	CTV Expansion	direct_mail	false
120	Paid Search Core	partnership	true
121	Legacy iOS Burst	referral	false
122	Premium Upsell	organic_search	false
123	Email Reactivation	null	false
124	Geo Test Campaign	video	false
125	Premium Upsell	paid_social	false
126	Referral Boost	push	false

attribution_events

event_id	user_id	campaign_id	event_time	source	platform	status
1008	8	101	2024-06-10 11:30:00	sdk	ios	attributed
1003	3	102	2024-06-09 16:00:00	api	android	attributed
1011	11	102	2024-06-10 15:00:00	api	android	attributed
1001	1	101	2024-06-09 13:00:00	sdk	ios	attributed
1010	10	101	2024-06-10 14:00:00	sdk	ios	attributed
1006	6	104	2024-06-10 08:00:00	partner	web	attributed
1004	4	103	2024-06-09 18:00:00	sdk	web	attributed
1007	7	101	2024-06-10 10:00:00	sdk	ios	attributed
1012	12	101	2024-06-10 16:00:00	sdk	ios	failed
1002	2	101	2024-06-09 14:00:00	sdk	ios	attributed
1005	5	104	2024-06-10 02:00:00	partner	web	attributed
1009	9	105	2024-06-10 13:00:00	sdk	ios	attributed
1013	6	101	2024-06-07 12:56:02	event_bridge	null	pending
1014	4	101	2024-06-08 12:15:10	gtm	windows	duplicate
1015	2	101	2024-06-11 17:09:51	redirect	null	errored
1016	10	101	2024-06-07 10:09:54	s2s	api_client	reprocessed
1017	7	104	2024-06-13 05:16:32	null	ios_webview	resolved
1018	4	101	2024-06-08 17:43:55	mmp	console	pending
1019	3	103	2024-06-12 11:00:41	null	android_tv	blocked
1020	5	103	2024-06-12 19:00:42	ios_sdk	desktop	rejected
1021	9	102	2024-06-11 04:31:31	gtm	backend	duplicate
1022	6	101	2024-06-08 06:10:50	ad_network	null	attributed
1023	5	102	2024-06-07 14:13:30	null	mobile_web	null
1024	10	101	2024-06-08 07:13:28	null	android_tv	matched
1025	12	104	2024-06-07 21:27:30	s2s	roku	accepted
1026	7	101	2024-06-09 16:24:04	api	carplay	attributed
1027	1	103	2024-06-06 16:39:05	null	smart_tv	blocked
1028	6	102	2024-06-09 13:15:21	manual_upload	tablet	pending
1029	2	101	2024-06-12 19:39:16	pixel	email	validated
1030	4	101	2024-06-07 21:17:37	internal_tool	android_webview	blocked

Expected Output

source	platform	status	previous_24h_events	last_24h_events	absolute_drop	pct_drop
sdk	ios	attributed	3	1	2	66.67
sdk	web	attributed	1	0	1	100.00

Practice SQL

PostgreSQL

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

Up next

Ownership and bias for action in diagnosing a key metric dropMedium

Diagnose Databricks Engagement DropMedium

Detect Post-Event Product DropoffHard

Next question

Diagnose Attribution Event Drop

Medium

SQL & Data Manipulation

JoinsAggregationsDate Functions

Problem

Task

Requirements

Compare successful attribution events in the last 24 hours against the previous 24 hours.
Break results out by source, platform, and status.
Return only rows where the last 24 hours had fewer successful events than the prior 24 hours.
Include the absolute drop and percentage drop, ordered by the largest absolute drop first.

Table Definitions

`attribution_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User tied to the attribution event
campaign_id	INT	Campaign tied to the event
event_time	TIMESTAMP	Time the attribution event was recorded
source	VARCHAR(50)	Attribution source such as SDK or API
platform	VARCHAR(20)	Client platform
status	VARCHAR(20)	Processing status

`campaigns`

column	type	description
campaign_id	INT	Campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Marketing channel
is_active	BOOLEAN	Whether the campaign is active

`users`

column	type	description
user_id	INT	User identifier
country	VARCHAR(50)	User country
signup_platform	VARCHAR(20)	User signup platform
is_test_user	BOOLEAN	Whether the user is a test account

Sample Data

`attribution_events`

event_id	user_id	campaign_id	event_time	source	platform	status
1001	1	101	2024-06-10 10:00:00	sdk	ios	attributed
1002	2	101	2024-06-10 11:00:00	sdk	ios	attributed
1003	3	102	2024-06-10 12:00:00	api	android	attributed
1004	4	103	2024-06-10 13:00:00	sdk	web	failed
1005	5	104	2024-06-11 02:00:00	sdk	ios	attributed
1006	6	102	2024-06-11 03:00:00	api	android	failed

Expected Output

source	platform	status	previous_24h_events	last_24h_events	absolute_drop	pct_drop
sdk	ios	attributed	4	1	3	75.00
api	android	attributed	2	1	1	50.00

Schema

attribution_events

Column	Type	Description
event_idPK	INT	Unique attribution event identifier
user_id	INT	User associated with the event
campaign_id	INT	Campaign associated with the event
event_time	TIMESTAMP	Timestamp when the attribution event was recorded
source	VARCHAR(50)	Attribution source
platform	VARCHAR(20)	Client platform
status	VARCHAR(20)	Processing status of the attribution event

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Marketing channel
is_active	BOOLEAN	Whether the campaign is currently active

users

Column	Type	Description
user_idPK	INT	Unique user identifier
country	VARCHAR(50)	User country
signup_platform	VARCHAR(20)	Platform used at signup
is_test_user	BOOLEAN	Whether the user is a test account

users

user_id	country	signup_platform	is_test_user
1	US	ios	false
2	CA	ios	false
3	US	android	false
4	GB	web	false
5	DE	web	false
6	FR	web	null
7	US	ios	true
8	IN	ios	false
9	BR	ios	false
10	US	ios	false
11	JP	android	false
12	US	ios	false
13	IT	kiosk	false
14	KR	fire_tv	false
15	PT	kiosk	true
16	DE	roku	false
17	JP	partner_portal	false
18	DK	android	false
19	ZA	sms	false
20	GB	sms	null
21	PL	null	false
22	DE	windows	false
23	US	sms	true
24	IN	null	true
25	null	null	true
26	ES	console	false
27	IN	ios	false
28	null	web	true
29	BR	partner_portal	false
30	null	kiosk	false

campaigns

campaign_id	campaign_name	channel	is_active
104	Affiliate Push	affiliate	false
101	Summer Launch	paid_social	true
103	Winback Email	email	true
106	Dormant Search	search	false
102	Android Retargeting	paid_search	true
105	Legacy iOS Burst	display	false
107	Brand Awareness	video	true
108	Referral Boost	referral	true
109	Affiliate Push	nonbrand_search	false
110	CTV Expansion	display	true
111	Retention Booster	out_of_home	false
112	Creator Partnership	display	false
113	Paid Search Core	podcast	false
114	Brand Awareness	email	true
115	CTV Expansion	tv	false
116	Search Nonbrand	organic_search	true
117	Creator Partnership	podcast	true
118	Install Surge	community	true
119	CTV Expansion	direct_mail	false
120	Paid Search Core	partnership	true
121	Legacy iOS Burst	referral	false
122	Premium Upsell	organic_search	false
123	Email Reactivation	null	false
124	Geo Test Campaign	video	false
125	Premium Upsell	paid_social	false
126	Referral Boost	push	false

attribution_events

event_id	user_id	campaign_id	event_time	source	platform	status
1008	8	101	2024-06-10 11:30:00	sdk	ios	attributed
1003	3	102	2024-06-09 16:00:00	api	android	attributed
1011	11	102	2024-06-10 15:00:00	api	android	attributed
1001	1	101	2024-06-09 13:00:00	sdk	ios	attributed
1010	10	101	2024-06-10 14:00:00	sdk	ios	attributed
1006	6	104	2024-06-10 08:00:00	partner	web	attributed
1004	4	103	2024-06-09 18:00:00	sdk	web	attributed
1007	7	101	2024-06-10 10:00:00	sdk	ios	attributed
1012	12	101	2024-06-10 16:00:00	sdk	ios	failed
1002	2	101	2024-06-09 14:00:00	sdk	ios	attributed
1005	5	104	2024-06-10 02:00:00	partner	web	attributed
1009	9	105	2024-06-10 13:00:00	sdk	ios	attributed
1013	6	101	2024-06-07 12:56:02	event_bridge	null	pending
1014	4	101	2024-06-08 12:15:10	gtm	windows	duplicate
1015	2	101	2024-06-11 17:09:51	redirect	null	errored
1016	10	101	2024-06-07 10:09:54	s2s	api_client	reprocessed
1017	7	104	2024-06-13 05:16:32	null	ios_webview	resolved
1018	4	101	2024-06-08 17:43:55	mmp	console	pending
1019	3	103	2024-06-12 11:00:41	null	android_tv	blocked
1020	5	103	2024-06-12 19:00:42	ios_sdk	desktop	rejected
1021	9	102	2024-06-11 04:31:31	gtm	backend	duplicate
1022	6	101	2024-06-08 06:10:50	ad_network	null	attributed
1023	5	102	2024-06-07 14:13:30	null	mobile_web	null
1024	10	101	2024-06-08 07:13:28	null	android_tv	matched
1025	12	104	2024-06-07 21:27:30	s2s	roku	accepted
1026	7	101	2024-06-09 16:24:04	api	carplay	attributed
1027	1	103	2024-06-06 16:39:05	null	smart_tv	blocked
1028	6	102	2024-06-09 13:15:21	manual_upload	tablet	pending
1029	2	101	2024-06-12 19:39:16	pixel	email	validated
1030	4	101	2024-06-07 21:17:37	internal_tool	android_webview	blocked

Expected Output

source	platform	status	previous_24h_events	last_24h_events	absolute_drop	pct_drop
sdk	ios	attributed	3	1	2	66.67
sdk	web	attributed	1	0	1	100.00

Problem

Task

Requirements

Compare successful attribution events in the last 24 hours against the previous 24 hours.
Break results out by source, platform, and status.
Return only rows where the last 24 hours had fewer successful events than the prior 24 hours.
Include the absolute drop and percentage drop, ordered by the largest absolute drop first.

Table Definitions

`attribution_events`

column	type	description
event_id	INT	Unique event identifier
user_id	INT	User tied to the attribution event
campaign_id	INT	Campaign tied to the event
event_time	TIMESTAMP	Time the attribution event was recorded
source	VARCHAR(50)	Attribution source such as SDK or API
platform	VARCHAR(20)	Client platform
status	VARCHAR(20)	Processing status

`campaigns`

column	type	description
campaign_id	INT	Campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Marketing channel
is_active	BOOLEAN	Whether the campaign is active

`users`

column	type	description
user_id	INT	User identifier
country	VARCHAR(50)	User country
signup_platform	VARCHAR(20)	User signup platform
is_test_user	BOOLEAN	Whether the user is a test account

Sample Data

`attribution_events`

event_id	user_id	campaign_id	event_time	source	platform	status
1001	1	101	2024-06-10 10:00:00	sdk	ios	attributed
1002	2	101	2024-06-10 11:00:00	sdk	ios	attributed
1003	3	102	2024-06-10 12:00:00	api	android	attributed
1004	4	103	2024-06-10 13:00:00	sdk	web	failed
1005	5	104	2024-06-11 02:00:00	sdk	ios	attributed
1006	6	102	2024-06-11 03:00:00	api	android	failed

Expected Output

source	platform	status	previous_24h_events	last_24h_events	absolute_drop	pct_drop
sdk	ios	attributed	4	1	3	75.00
api	android	attributed	2	1	1	50.00

Schema

attribution_events

Column	Type	Description
event_idPK	INT	Unique attribution event identifier
user_id	INT	User associated with the event
campaign_id	INT	Campaign associated with the event
event_time	TIMESTAMP	Timestamp when the attribution event was recorded
source	VARCHAR(50)	Attribution source
platform	VARCHAR(20)	Client platform
status	VARCHAR(20)	Processing status of the attribution event

campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name
channel	VARCHAR(50)	Marketing channel
is_active	BOOLEAN	Whether the campaign is currently active

users

Column	Type	Description
user_idPK	INT	Unique user identifier
country	VARCHAR(50)	User country
signup_platform	VARCHAR(20)	Platform used at signup
is_test_user	BOOLEAN	Whether the user is a test account

users

user_id	country	signup_platform	is_test_user
1	US	ios	false
2	CA	ios	false
3	US	android	false
4	GB	web	false
5	DE	web	false
6	FR	web	null
7	US	ios	true
8	IN	ios	false
9	BR	ios	false
10	US	ios	false
11	JP	android	false
12	US	ios	false
13	IT	kiosk	false
14	KR	fire_tv	false
15	PT	kiosk	true
16	DE	roku	false
17	JP	partner_portal	false
18	DK	android	false
19	ZA	sms	false
20	GB	sms	null
21	PL	null	false
22	DE	windows	false
23	US	sms	true
24	IN	null	true
25	null	null	true
26	ES	console	false
27	IN	ios	false
28	null	web	true
29	BR	partner_portal	false
30	null	kiosk	false

campaigns

campaign_id	campaign_name	channel	is_active
104	Affiliate Push	affiliate	false
101	Summer Launch	paid_social	true
103	Winback Email	email	true
106	Dormant Search	search	false
102	Android Retargeting	paid_search	true
105	Legacy iOS Burst	display	false
107	Brand Awareness	video	true
108	Referral Boost	referral	true
109	Affiliate Push	nonbrand_search	false
110	CTV Expansion	display	true
111	Retention Booster	out_of_home	false
112	Creator Partnership	display	false
113	Paid Search Core	podcast	false
114	Brand Awareness	email	true
115	CTV Expansion	tv	false
116	Search Nonbrand	organic_search	true
117	Creator Partnership	podcast	true
118	Install Surge	community	true
119	CTV Expansion	direct_mail	false
120	Paid Search Core	partnership	true
121	Legacy iOS Burst	referral	false
122	Premium Upsell	organic_search	false
123	Email Reactivation	null	false
124	Geo Test Campaign	video	false
125	Premium Upsell	paid_social	false
126	Referral Boost	push	false

attribution_events

event_id	user_id	campaign_id	event_time	source	platform	status
1008	8	101	2024-06-10 11:30:00	sdk	ios	attributed
1003	3	102	2024-06-09 16:00:00	api	android	attributed
1011	11	102	2024-06-10 15:00:00	api	android	attributed
1001	1	101	2024-06-09 13:00:00	sdk	ios	attributed
1010	10	101	2024-06-10 14:00:00	sdk	ios	attributed
1006	6	104	2024-06-10 08:00:00	partner	web	attributed
1004	4	103	2024-06-09 18:00:00	sdk	web	attributed
1007	7	101	2024-06-10 10:00:00	sdk	ios	attributed
1012	12	101	2024-06-10 16:00:00	sdk	ios	failed
1002	2	101	2024-06-09 14:00:00	sdk	ios	attributed
1005	5	104	2024-06-10 02:00:00	partner	web	attributed
1009	9	105	2024-06-10 13:00:00	sdk	ios	attributed
1013	6	101	2024-06-07 12:56:02	event_bridge	null	pending
1014	4	101	2024-06-08 12:15:10	gtm	windows	duplicate
1015	2	101	2024-06-11 17:09:51	redirect	null	errored
1016	10	101	2024-06-07 10:09:54	s2s	api_client	reprocessed
1017	7	104	2024-06-13 05:16:32	null	ios_webview	resolved
1018	4	101	2024-06-08 17:43:55	mmp	console	pending
1019	3	103	2024-06-12 11:00:41	null	android_tv	blocked
1020	5	103	2024-06-12 19:00:42	ios_sdk	desktop	rejected
1021	9	102	2024-06-11 04:31:31	gtm	backend	duplicate
1022	6	101	2024-06-08 06:10:50	ad_network	null	attributed
1023	5	102	2024-06-07 14:13:30	null	mobile_web	null
1024	10	101	2024-06-08 07:13:28	null	android_tv	matched
1025	12	104	2024-06-07 21:27:30	s2s	roku	accepted
1026	7	101	2024-06-09 16:24:04	api	carplay	attributed
1027	1	103	2024-06-06 16:39:05	null	smart_tv	blocked
1028	6	102	2024-06-09 13:15:21	manual_upload	tablet	pending
1029	2	101	2024-06-12 19:39:16	pixel	email	validated
1030	4	101	2024-06-07 21:17:37	internal_tool	android_webview	blocked

Expected Output

source	platform	status	previous_24h_events	last_24h_events	absolute_drop	pct_drop
sdk	ios	attributed	3	1	2	66.67
sdk	web	attributed	1	0	1	100.00

Practice SQL

PostgreSQL

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

Up next

Ownership and bias for action in diagnosing a key metric dropMedium

Diagnose Databricks Engagement DropMedium

Detect Post-Event Product DropoffHard

Next question