Second Highest Premium in SQL | Dataford Interview Questions

Your question is Second Highest Premium in SQL. Start with the requirements and the two tables on the right.

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

Problem

AXA XL Insurance needs to identify policies associated with the second-highest premium paid in its UK Property portfolio. Write a PostgreSQL query that ranks distinct premium values and returns every policy tied at the second-highest value.

Requirements

Consider only policies where product_line = 'Property', policy_status = 'Active', and underwriting_region = 'UK'.
Treat premium_paid as the amount to rank and exclude NULL values from the ranking.
Use DENSE_RANK() so all policies tied at the second-highest distinct premium are returned.
Include the policy number, premium paid, and policyholder name. Retain a qualifying policy even when its policyholder record is missing, and order results by policy number.

Schema

axa_xl_policies

Column	Type	Description
policy_idPK	INT	Unique policy identifier
policy_number	VARCHAR(30)	AXA XL Insurance policy reference
policyholder_id	INT	Associated policyholder identifier
product_line	VARCHAR(40)	Insurance product line
policy_status	VARCHAR(20)	Current policy status
underwriting_region	VARCHAR(20)	Underwriting region
premium_paid	NUMERIC(12,2)	Premium paid for the policy

policyholders

Column	Type	Description
policyholder_idPK	INT	Unique policyholder identifier
holder_name	VARCHAR(100)	Policyholder or insured organization name