Window and Join Strategies | Dataford Interview Questions

How do you approach window and join operations in large-scale SQL queries?

Using the provided PostgreSQL tables, write a query that calculates each restaurant's completed deliveries and most recent completed delivery during January 2025, then ranks restaurants within their city. Include restaurants with no qualifying deliveries.

Output

One row per restaurant with restaurant_name, city, completed_deliveries, last_completed_at, and city_rank.
Include all restaurants, order by city, city_rank, and restaurant_id.

Schema

restaurants

Column	Type	Description
restaurant_idPK	INT	Unique restaurant identifier
restaurant_name	VARCHAR(100)	Restaurant display name
city	VARCHAR(80)	Restaurant city

deliveries

Column	Type	Description
delivery_idPK	INT	Unique delivery identifier
restaurant_id	INT	Restaurant associated with the delivery
status	VARCHAR(30)	Delivery status
completed_at	TIMESTAMP	Timestamp when the delivery was completed

Your question is Window and Join Strategies. 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