SQL Window Ranking by State | Dataford Interview Questions

Write a SQL query using window functions to rank carrier partners by total revenue generated within each state on Insurify.

Use the carrier_partners and policy_revenue tables. Include only records with a non-null state and revenue.

Output

One row per carrier partner and state, with state_code, carrier_name, total_revenue, and revenue_rank.
Tied revenue totals share the same rank.
Order by state, rank ascending, then carrier name ascending.

carrier_partners

Column	Type	Description
carrier_idPK	INT	Unique carrier partner identifier
carrier_name	VARCHAR(100)	Carrier partner name

policy_revenue

Column	Type	Description
policy_revenue_idPK	INT	Unique revenue record identifier
carrier_id	INT	Referenced carrier partner
state_code	VARCHAR(2)	US state associated with the revenue
revenue	NUMERIC(12,2)	Revenue generated by the policy record

Your question is SQL Window Ranking by State. 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.