Find top advertisers and cost per KPI

You are given three advertising campaign tables.

Table: campaign_spend

Column	Type	Description
date	DATE	Calendar date in UTC.
advertiser_id	BIGINT	Advertiser identifier.
campaign_id	BIGINT	Campaign identifier.
creative_group	VARCHAR	Creative group within a campaign.
ad_spend	DECIMAL	Amount spent on ads for the row.
impressions	BIGINT	Number of ad impressions.

Table: campaign_conversion

Column	Type	Description
date	DATE	Calendar date in UTC.
advertiser_id	BIGINT	Advertiser identifier.
campaign_id	BIGINT	Campaign identifier.
creative_group	VARCHAR	Creative group within a campaign.
installs	BIGINT	Number of installs attributed to the campaign.
kpi_cnt	BIGINT	Final KPI conversion count. Different campaigns may optimize for different KPIs, but this column stores the final conversion count for that campaign's KPI.

Table: campaign_goal

Column	Type	Description
campaign_id	BIGINT	Campaign identifier.
campaign_goal	VARCHAR	Campaign optimization goal, such as install, purchase, signup, or other KPI type.
campaign_launch_date	DATE	Campaign launch date in UTC.

Assume campaign_id maps to exactly one campaign_goal. Spend and conversion rows can be joined at the grain of date, advertiser_id, campaign_id, and creative_group when both spend and conversion information are needed.

Answer the following SQL questions:

1. Find the top 3 spending advertisers for each day.

   • Aggregate spend at the date and advertiser_id level.
   • Rank advertisers by total daily spend in descending order.
   • Return all advertisers tied within the top 3 ranks if ties occur.
   • Required output columns: date, advertiser_id, daily_ad_spend, spend_rank.

2. Calculate cost per KPI for each campaign goal.

   • For each campaign_goal, aggregate total ad spend and total KPI conversions across all campaigns with that goal.
   • Use only spend and conversions on or after each campaign's campaign_launch_date.
   • Define cost_per_kpi = total_ad_spend / total_kpi_cnt.
   • If total_kpi_cnt is zero, return NULL for cost_per_kpi instead of dividing by zero.
   • Required output columns: campaign_goal, total_ad_spend, total_kpi_cnt, cost_per_kpi.

Find top advertisers and cost per KPI

Table campaign_spend

column	type	constraints
date	DATE
advertiser_id	BIGINT
campaign_id	BIGINT
creative_group	VARCHAR
ad_spend	DECIMAL
impressions	BIGINT

Sample data:

ad_spend	advertiser_id	campaign_id	creative_group	date	impressions
120	10	1001	A	2024-01-05	10000
80	20	1002	B	2024-01-05	7000
30	10	1003	C	2024-01-05	4000

Table campaign_conversion

column	type	constraints
date	DATE
advertiser_id	BIGINT
campaign_id	BIGINT
creative_group	VARCHAR
installs	BIGINT
kpi_cnt	BIGINT

Sample data:

advertiser_id	campaign_id	creative_group	date	installs	kpi_cnt
10	1001	A	2024-01-05	10	10
20	1002	B	2024-01-05	4	2
10	1003	C	2024-01-05	0	1

Table campaign_goal

column	type	constraints
campaign_id	BIGINT
campaign_goal	VARCHAR
campaign_launch_date	DATE

Sample data:

campaign_goal	campaign_id	campaign_launch_date
install	1001	2024-01-01
purchase	1002	2024-01-01
purchase	1003	2024-01-01

Expected output

Single result set with two summary rows to satisfy validation expectations. Each row labels a sub-result and reports how many rows that sub-result would return on the provided sample data.

result_name	rows
q1_top_advertisers_rows	2
q2_cost_per_kpi_rows	2

Hints

1. Use SUM(ad_spend) grouped by date and advertiser_id to get daily totals, then RANK() OVER (PARTITION BY date ORDER BY daily_ad_spend DESC) to include ties.
2. For cost per KPI, join spend and conversions to campaign_goal separately by campaign_id and filter by date >= campaign_launch_date before aggregating.
3. Left join aggregated spend/conversion to the distinct set of goals to ensure every campaign_goal appears, even if it has no data.
4. Avoid division by zero using CASE or NULLIF when calculating cost_per_kpi.