SQL Lowest Price Per Model | Dataford Interview Questions

Your question is SQL Lowest Price Per Model. 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

Bolt Drive maintains a catalog of car models and vehicle price records across its operating cities. Write a PostgreSQL query that reports the lowest active price for every model, including models that currently have no active price.

Requirements

Use a CTE to calculate the minimum price_eur for each model from active price records only.
Return every model from car_models, including models without matching active prices.
Output the brand, model name, and lowest active price in model ID order.
Ignore inactive records and records whose model_id is NULL through the join and filtering logic.

Schema

car_models

Column	Type	Description
model_idPK	INT	Unique car model identifier
brand	VARCHAR(50)	Vehicle manufacturer
model_name	VARCHAR(100)	Vehicle model name

vehicle_prices

Column	Type	Description
price_idPK	INT	Unique price record identifier
model_id	INT	Referenced car model identifier
city	VARCHAR(80)	Bolt Drive operating city
price_eur	DECIMAL(10,2)	Recorded vehicle price in euros
listing_status	VARCHAR(20)	Price record status