Top Networks by Rolling ROAS

MediumSQL · PostgreSQL

Problem

Tatari needs a daily view of which advertising networks are producing the strongest return on ad spend. Using campaign-level spend and attributed revenue, write a PostgreSQL query that ranks the active networks for the 30-day period ending on 2025-03-31.

Requirements

Include activity from 2025-03-02 through 2025-03-31, inclusive.
Aggregate spend and attributed revenue across all campaigns belonging to each active network.
Calculate ROAS as attributed revenue divided by spend, safely handling zero or missing values.
Return the top three networks, ordered by ROAS descending, with network name, total spend, attributed revenue, ROAS, and rank.
Exclude networks with no positive spend and revenue or spend recorded outside the rolling window.

Representative Data

Schema

networks

Column	Type	Description
network_idPK	INTEGER	Unique network identifier
network_name	VARCHAR(100)	Tatari advertising network name
is_active	BOOLEAN	Whether the network is active

campaigns

Column	Type	Description
campaign_idPK	INTEGER	Unique campaign identifier
network_id	INTEGER	Owning network identifier
campaign_name	VARCHAR(150)	Campaign name

spend_daily

Column	Type	Description
spend_idPK	INTEGER	Unique spend record identifier
campaign_id	INTEGER	Campaign associated with the spend
spend_date	DATE	Date on which spend occurred
spend_amount	NUMERIC(12,2)	Amount spent

revenue_daily

Column	Type	Description
revenue_idPK	INTEGER	Unique revenue record identifier
campaign_id	INTEGER	Campaign receiving attribution
revenue_date	DATE	Date of attributed revenue
attributed_revenue	NUMERIC(12,2)	Revenue attributed to the campaign

Tablesnetworkscampaignsspend_dailyrevenue_daily