SQL: Find the 2nd Highest

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is SQL: Find the 2nd Highest. 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.

You need to log in / sign up to run or submit.

Problem

Blue Yonder's Luminate Planning data team needs to identify distribution centers with the second-highest inventory valuation among active facilities. Write a PostgreSQL query that ranks distinct non-null inventory values and returns every facility tied at rank two.

Requirements

Join inventory valuations to facilities and include only active facilities.
Exclude rows where inventory_value is NULL.
Use a window function such as DENSE_RANK so ties at the second-highest distinct value are retained.
Return the facility name and inventory value, ordered by facility name.

Schema

facilities

Column	Type	Description
facility_idPK	INTEGER	Unique distribution center identifier
facility_name	VARCHAR(100)	Distribution center name
region	VARCHAR(50)	Operating region
is_active	BOOLEAN	Whether the facility is currently active

inventory_valuations

Column	Type	Description
valuation_idPK	INTEGER	Unique valuation record identifier
facility_id	INTEGER	Facility associated with the valuation
sku_code	VARCHAR(30)	Stock keeping unit code
inventory_value	NUMERIC(12,2)	Total inventory valuation amount

Tablesfacilitiesinventory_valuations

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results