Top Busiest Fire Stations Query | Dataford Interview Questions

Write a query to identify the top three busiest fire stations in a given month for Deccan International’s deployment planning.

For the supplied data, evaluate January 2024. Count deployments by station, exclude deployments with a NULL dispatch timestamp, and use station ID ascending as the tie-breaker.

Output

One row per selected fire station with station_id, station_name, and deployment_count.
Include only the three highest-count stations. Rows with a NULL dispatch timestamp are ignored.
Order by deployment count descending, then station ID ascending.

Schema

fire_stations

Column	Type	Description
station_idPK	INTEGER	Unique fire station identifier
station_name	VARCHAR(100)	Fire station display name
district	VARCHAR(100)	Operational district for the station

fire_deployments

Column	Type	Description
deployment_idPK	INTEGER	Unique deployment identifier
station_id	INTEGER	Station assigned to the deployment
dispatched_at	TIMESTAMP	Timestamp when the deployment was dispatched
incident_type	VARCHAR(80)	Type of incident requiring deployment

Your question is Top Busiest Fire Stations Query. 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