SQL ROI by Date and Channel | Dataford Interview Questions

Business context

Bolt marketing analysts need to compare revenue generated with campaign costs across cities and channels. The source tables contain city-level records, but the required KPI is reported at the date and channel level.

Task

Write a PostgreSQL query that calculates Bolt ROI by date and channel, where ROI is total revenue divided by total cost.

Requirements

Aggregate each source to the date, channel, and city grain before joining to prevent revenue-cost fanout.
Preserve valid date, channel, and city combinations appearing in either table.
Treat missing revenue or cost as zero, and return NULL ROI when total cost is zero.
Include the previous available date's ROI for each channel and rank channels by ROI for each date, with zero-cost channels ranked last.
Return results ordered by date and channel.

Schema

city_cost

Column	Type	Description
date	DATE	Date on which the channel cost was incurred
channel	VARCHAR(40)	Bolt marketing or acquisition channel
city	VARCHAR(80)	Bolt operating city
cost	NUMERIC(12,2)	Cost attributed to the city and channel

order_rev

Column	Type	Description
date	DATE	Date on which the order was completed
channel	VARCHAR(40)	Bolt channel attributed to the order
city	VARCHAR(80)	City associated with the order
order_id	BIGINT	Unique order identifier
revenue	NUMERIC(12,2)	Revenue attributed to the order

Your question is SQL ROI by Date and Channel. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem