Interview Guides

Median CTR by Meta Campaign | Dataford Interview Questions - Dataford - Ace your Interview

Median CTR by Meta Campaign

Hard

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

Task

Meta Ads analysts want to compare campaign performance using the median click-through rate (CTR) across daily delivery records. Write a PostgreSQL query to return the median CTR for each ad campaign using window functions.

CTR for a row is defined as clicks::numeric / impressions. Rows with impressions = 0 or impressions IS NULL should be excluded from the median calculation.

Requirements

Compute CTR for each valid row in ad_delivery.
For each campaign_id, calculate the median CTR using window functions rather than PERCENTILE_CONT.
If a campaign has an odd number of valid CTR rows, return the middle CTR.
If a campaign has an even number of valid CTR rows, return the average of the two middle CTR values.
Output campaign_id, campaign_name, and median_ctr, ordered by campaign_id.

Table Definitions

`meta_campaigns`

column	type	description
campaign_id	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name in Meta Ads Manager
objective	VARCHAR(50)	Campaign objective

`ad_delivery`

column	type	description
delivery_id	INT	Unique delivery record
campaign_id	INT	Campaign identifier
delivery_date	DATE	Daily delivery date
impressions	INT	Number of impressions served
clicks	INT	Number of clicks received

Sample Data

`meta_campaigns`

campaign_id	campaign_name	objective
103	Reels Retargeting	CONVERSIONS
101	Feed Prospecting	TRAFFIC
104	Shops Awareness	AWARENESS
102	Stories Install Push	APP_INSTALLS
105	Messenger Leads	LEADS
106	Advantage+ Catalog	SALES
107	IG Explore Test	TRAFFIC
108	Video View Booster	ENGAGEMENT

`ad_delivery`

delivery_id	campaign_id	delivery_date	impressions	clicks
11	102	2024-06-03	100	4
3	101	2024-06-02	200	10
8	102	2024-06-01	100	1
1	101	2024-06-01	100	2
15	103	2024-06-04	100	7
18	104	2024-06-03	100	2
5	101	2024-06-04	100	8
20	105	2024-06-01	100	0
24	106	2024-06-02	100	6
27	108	2024-06-01	100	9

Expected Output

campaign_id	campaign_name	median_ctr
101	Feed Prospecting	0.0500
102	Stories Install Push	0.0400
103	Reels Retargeting	0.0600
104	Shops Awareness	0.0200
105	Messenger Leads	0.0000
106	Advantage+ Catalog	0.0600

Schema

meta_campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name in Meta Ads Manager
objective	VARCHAR(50)	Campaign objective

ad_delivery

Column	Type	Description
delivery_idPK	INT	Unique delivery record
campaign_id	INT	Campaign identifier
delivery_date	DATE	Daily delivery date
impressions	INT	Number of impressions served
clicks	INT	Number of clicks received

Sample Data

Example 1

Input

{"ad_delivery":[[11,102,"2024-06-03",100,4],[3,101,"2024-06-02",200,10],[8,102,"2024-06-01",100,1],[1,101,"2024-06-01",100,2],[15,103,"2024-06-04",100,7],[18,104,"2024-06-03",100,2],[5,101,"2024-06-04",100,8],[20,105,"2024-06-01",100,0],[24,106,"2024-06-02",100,6],[27,108,"2024-06-01",100,9],[2,101,"2024-06-03",100,5],[4,101,"2024-06-05",0,3],[6,102,"2024-06-04",100,8],[7,102,"2024-06-02",100,4],[9,102,"2024-06-05",null,2],[10,103,"2024-06-01",100,3],[12,103,"2024-06-02",100,5],[13,103,"2024-06-

Output

[["101","Feed Prospecting","0.0500"],["102","Stories Install Push","0.0400"],["103","Reels Retargeting","0.0550"],["104","Shops Awareness","0.0200"],["105","Messenger Leads","0.0000"],["106","Advantage+ Catalog","0.0600"],["108","Video View Booster","0.0900"]]

Problem

Task

CTR for a row is defined as clicks::numeric / impressions. Rows with impressions = 0 or impressions IS NULL should be excluded from the median calculation.

Requirements

Compute CTR for each valid row in ad_delivery.
For each campaign_id, calculate the median CTR using window functions rather than PERCENTILE_CONT.
If a campaign has an odd number of valid CTR rows, return the middle CTR.
If a campaign has an even number of valid CTR rows, return the average of the two middle CTR values.
Output campaign_id, campaign_name, and median_ctr, ordered by campaign_id.

Table Definitions

`meta_campaigns`

column	type	description
campaign_id	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name in Meta Ads Manager
objective	VARCHAR(50)	Campaign objective

`ad_delivery`

column	type	description
delivery_id	INT	Unique delivery record
campaign_id	INT	Campaign identifier
delivery_date	DATE	Daily delivery date
impressions	INT	Number of impressions served
clicks	INT	Number of clicks received

Sample Data

`meta_campaigns`

campaign_id	campaign_name	objective
103	Reels Retargeting	CONVERSIONS
101	Feed Prospecting	TRAFFIC
104	Shops Awareness	AWARENESS
102	Stories Install Push	APP_INSTALLS
105	Messenger Leads	LEADS
106	Advantage+ Catalog	SALES
107	IG Explore Test	TRAFFIC
108	Video View Booster	ENGAGEMENT

`ad_delivery`

delivery_id	campaign_id	delivery_date	impressions	clicks
11	102	2024-06-03	100	4
3	101	2024-06-02	200	10
8	102	2024-06-01	100	1
1	101	2024-06-01	100	2
15	103	2024-06-04	100	7
18	104	2024-06-03	100	2
5	101	2024-06-04	100	8
20	105	2024-06-01	100	0
24	106	2024-06-02	100	6
27	108	2024-06-01	100	9

Expected Output

campaign_id	campaign_name	median_ctr
101	Feed Prospecting	0.0500
102	Stories Install Push	0.0400
103	Reels Retargeting	0.0600
104	Shops Awareness	0.0200
105	Messenger Leads	0.0000
106	Advantage+ Catalog	0.0600

Schema

meta_campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name in Meta Ads Manager
objective	VARCHAR(50)	Campaign objective

ad_delivery

Column	Type	Description
delivery_idPK	INT	Unique delivery record
campaign_id	INT	Campaign identifier
delivery_date	DATE	Daily delivery date
impressions	INT	Number of impressions served
clicks	INT	Number of clicks received

Sample Data

Example 1

Input

{"ad_delivery":[[11,102,"2024-06-03",100,4],[3,101,"2024-06-02",200,10],[8,102,"2024-06-01",100,1],[1,101,"2024-06-01",100,2],[15,103,"2024-06-04",100,7],[18,104,"2024-06-03",100,2],[5,101,"2024-06-04",100,8],[20,105,"2024-06-01",100,0],[24,106,"2024-06-02",100,6],[27,108,"2024-06-01",100,9],[2,101,"2024-06-03",100,5],[4,101,"2024-06-05",0,3],[6,102,"2024-06-04",100,8],[7,102,"2024-06-02",100,4],[9,102,"2024-06-05",null,2],[10,103,"2024-06-01",100,3],[12,103,"2024-06-02",100,5],[13,103,"2024-06-

Output

[["101","Feed Prospecting","0.0500"],["102","Stories Install Push","0.0400"],["103","Reels Retargeting","0.0550"],["104","Shops Awareness","0.0200"],["105","Messenger Leads","0.0000"],["106","Advantage+ Catalog","0.0600"],["108","Video View Booster","0.0900"]]

Practice SQL

PostgreSQL

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

Up next

Top Funnel Clicks and CPCEasy

Monthly Campaign Performance SummaryEasy

Compare Campaign Performance Across MonthsMedium

Next question

Median CTR by Meta Campaign

Hard

SQL & Data Manipulation

Asked at 1 company1

Also asked at

Problem

Task

CTR for a row is defined as clicks::numeric / impressions. Rows with impressions = 0 or impressions IS NULL should be excluded from the median calculation.

Requirements

Compute CTR for each valid row in ad_delivery.
For each campaign_id, calculate the median CTR using window functions rather than PERCENTILE_CONT.
If a campaign has an odd number of valid CTR rows, return the middle CTR.
If a campaign has an even number of valid CTR rows, return the average of the two middle CTR values.
Output campaign_id, campaign_name, and median_ctr, ordered by campaign_id.

Table Definitions

`meta_campaigns`

column	type	description
campaign_id	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name in Meta Ads Manager
objective	VARCHAR(50)	Campaign objective

`ad_delivery`

column	type	description
delivery_id	INT	Unique delivery record
campaign_id	INT	Campaign identifier
delivery_date	DATE	Daily delivery date
impressions	INT	Number of impressions served
clicks	INT	Number of clicks received

Sample Data

`meta_campaigns`

campaign_id	campaign_name	objective
103	Reels Retargeting	CONVERSIONS
101	Feed Prospecting	TRAFFIC
104	Shops Awareness	AWARENESS
102	Stories Install Push	APP_INSTALLS
105	Messenger Leads	LEADS
106	Advantage+ Catalog	SALES
107	IG Explore Test	TRAFFIC
108	Video View Booster	ENGAGEMENT

`ad_delivery`

delivery_id	campaign_id	delivery_date	impressions	clicks
11	102	2024-06-03	100	4
3	101	2024-06-02	200	10
8	102	2024-06-01	100	1
1	101	2024-06-01	100	2
15	103	2024-06-04	100	7
18	104	2024-06-03	100	2
5	101	2024-06-04	100	8
20	105	2024-06-01	100	0
24	106	2024-06-02	100	6
27	108	2024-06-01	100	9

Expected Output

campaign_id	campaign_name	median_ctr
101	Feed Prospecting	0.0500
102	Stories Install Push	0.0400
103	Reels Retargeting	0.0600
104	Shops Awareness	0.0200
105	Messenger Leads	0.0000
106	Advantage+ Catalog	0.0600

Schema

meta_campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name in Meta Ads Manager
objective	VARCHAR(50)	Campaign objective

ad_delivery

Column	Type	Description
delivery_idPK	INT	Unique delivery record
campaign_id	INT	Campaign identifier
delivery_date	DATE	Daily delivery date
impressions	INT	Number of impressions served
clicks	INT	Number of clicks received

Sample Data

Example 1

Input

{"ad_delivery":[[11,102,"2024-06-03",100,4],[3,101,"2024-06-02",200,10],[8,102,"2024-06-01",100,1],[1,101,"2024-06-01",100,2],[15,103,"2024-06-04",100,7],[18,104,"2024-06-03",100,2],[5,101,"2024-06-04",100,8],[20,105,"2024-06-01",100,0],[24,106,"2024-06-02",100,6],[27,108,"2024-06-01",100,9],[2,101,"2024-06-03",100,5],[4,101,"2024-06-05",0,3],[6,102,"2024-06-04",100,8],[7,102,"2024-06-02",100,4],[9,102,"2024-06-05",null,2],[10,103,"2024-06-01",100,3],[12,103,"2024-06-02",100,5],[13,103,"2024-06-

Output

[["101","Feed Prospecting","0.0500"],["102","Stories Install Push","0.0400"],["103","Reels Retargeting","0.0550"],["104","Shops Awareness","0.0200"],["105","Messenger Leads","0.0000"],["106","Advantage+ Catalog","0.0600"],["108","Video View Booster","0.0900"]]

Problem

Task

CTR for a row is defined as clicks::numeric / impressions. Rows with impressions = 0 or impressions IS NULL should be excluded from the median calculation.

Requirements

Compute CTR for each valid row in ad_delivery.
For each campaign_id, calculate the median CTR using window functions rather than PERCENTILE_CONT.
If a campaign has an odd number of valid CTR rows, return the middle CTR.
If a campaign has an even number of valid CTR rows, return the average of the two middle CTR values.
Output campaign_id, campaign_name, and median_ctr, ordered by campaign_id.

Table Definitions

`meta_campaigns`

column	type	description
campaign_id	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name in Meta Ads Manager
objective	VARCHAR(50)	Campaign objective

`ad_delivery`

column	type	description
delivery_id	INT	Unique delivery record
campaign_id	INT	Campaign identifier
delivery_date	DATE	Daily delivery date
impressions	INT	Number of impressions served
clicks	INT	Number of clicks received

Sample Data

`meta_campaigns`

campaign_id	campaign_name	objective
103	Reels Retargeting	CONVERSIONS
101	Feed Prospecting	TRAFFIC
104	Shops Awareness	AWARENESS
102	Stories Install Push	APP_INSTALLS
105	Messenger Leads	LEADS
106	Advantage+ Catalog	SALES
107	IG Explore Test	TRAFFIC
108	Video View Booster	ENGAGEMENT

`ad_delivery`

delivery_id	campaign_id	delivery_date	impressions	clicks
11	102	2024-06-03	100	4
3	101	2024-06-02	200	10
8	102	2024-06-01	100	1
1	101	2024-06-01	100	2
15	103	2024-06-04	100	7
18	104	2024-06-03	100	2
5	101	2024-06-04	100	8
20	105	2024-06-01	100	0
24	106	2024-06-02	100	6
27	108	2024-06-01	100	9

Expected Output

campaign_id	campaign_name	median_ctr
101	Feed Prospecting	0.0500
102	Stories Install Push	0.0400
103	Reels Retargeting	0.0600
104	Shops Awareness	0.0200
105	Messenger Leads	0.0000
106	Advantage+ Catalog	0.0600

Schema

meta_campaigns

Column	Type	Description
campaign_idPK	INT	Unique campaign identifier
campaign_name	VARCHAR(100)	Campaign name in Meta Ads Manager
objective	VARCHAR(50)	Campaign objective

ad_delivery

Column	Type	Description
delivery_idPK	INT	Unique delivery record
campaign_id	INT	Campaign identifier
delivery_date	DATE	Daily delivery date
impressions	INT	Number of impressions served
clicks	INT	Number of clicks received

Sample Data

Example 1

Input

{"ad_delivery":[[11,102,"2024-06-03",100,4],[3,101,"2024-06-02",200,10],[8,102,"2024-06-01",100,1],[1,101,"2024-06-01",100,2],[15,103,"2024-06-04",100,7],[18,104,"2024-06-03",100,2],[5,101,"2024-06-04",100,8],[20,105,"2024-06-01",100,0],[24,106,"2024-06-02",100,6],[27,108,"2024-06-01",100,9],[2,101,"2024-06-03",100,5],[4,101,"2024-06-05",0,3],[6,102,"2024-06-04",100,8],[7,102,"2024-06-02",100,4],[9,102,"2024-06-05",null,2],[10,103,"2024-06-01",100,3],[12,103,"2024-06-02",100,5],[13,103,"2024-06-

Output

[["101","Feed Prospecting","0.0500"],["102","Stories Install Push","0.0400"],["103","Reels Retargeting","0.0550"],["104","Shops Awareness","0.0200"],["105","Messenger Leads","0.0000"],["106","Advantage+ Catalog","0.0600"],["108","Video View Booster","0.0900"]]

Practice SQL

PostgreSQL

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

Up next

Top Funnel Clicks and CPCEasy

Monthly Campaign Performance SummaryEasy

Compare Campaign Performance Across MonthsMedium

Next question