Second Highest Tier Revenue | Dataford Interview Questions

Your question is Second Highest Tier Revenue. Start with the requirements and the three 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

Paramount+ analysts need to compare subscription-tier revenue across geographic markets. Write a PostgreSQL query that finds the second-highest revenue-generating tier within each region.

Requirements

Include only rows with status = 'paid' and a non-null revenue value.
Aggregate revenue by region and subscription tier.
Use a window function to rank distinct revenue levels within each region, retaining ties correctly.
Return the region, tier name, and total revenue for rank 2, ordered alphabetically by region.

Regions with fewer than two qualifying tiers should not appear in the result.

Schema

regions

Column	Type	Description
region_idPK	INT	Unique geographic region identifier
region_name	VARCHAR(100)	Display name of the geographic region

plans

Column	Type	Description
plan_idPK	INT	Unique Paramount+ plan identifier
tier_name	VARCHAR(50)	Name of the subscription tier

subscriptions

Column	Type	Description
subscription_idPK	INT	Unique billing record identifier
region_id	INT	Region associated with the subscription
plan_id	INT	Plan associated with the subscription
revenue	NUMERIC(10,2)	Revenue recorded for the billing record
status	VARCHAR(20)	Billing status, such as paid or pending