Rank Policies by State | Dataford Interview Questions

Write a SQL query using window functions to rank policies by premium within each state for Plymouth Rock Assurance.

Use the provided policies and states tables. Policies with a NULL premium are ignored, and only policies associated with a known state are included.

Output

One row per included policy with policy_id, policy_number, state_code, premium, and premium_rank.
Rank premiums from highest to lowest within each state. Equal premiums share the same rank.
Order by state_code, premium_rank, and policy_id ascending.

Schema

states

Column	Type	Description
state_codePK	VARCHAR(20)	State or jurisdiction code
state_name	VARCHAR(100)	Full state name

policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
policy_number	VARCHAR(30)	Business policy number
state_code	VARCHAR(20)	State code associated with the policy
premium	NUMERIC(12,2)	Policy premium amount

Your question is Rank Policies 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.

Problem