SQL for Monthly Facility Usage | Dataford Interview Questions

Stanford University wants to understand which campus facilities are used most often by different student cohorts. Write a PostgreSQL query that ranks facilities by completed utilization events for each month and student cohort.

Requirements

Join usage events to Stanford students and campus facilities.
Include only completed events from January and February 2024, excluding rows with no student cohort.
Aggregate utilization counts by calendar month, cohort year, and facility.
Return the top five facilities per month and cohort, using utilization count descending and facility name ascending as the deterministic tie-breaker.
Return results ordered by month, cohort year, rank, and facility name.

Schema

students

Column	Type	Description
student_idPK	INT	Unique Stanford student identifier
student_name	VARCHAR(100)	Student full name
cohort_year	INT	Year the student entered Stanford University

campus_facilities

Column	Type	Description
facility_idPK	INT	Unique facility identifier
facility_name	VARCHAR(150)	Official Stanford campus facility name

facility_usage

Column	Type	Description
usage_idPK	INT	Unique facility usage event identifier
student_id	INT	Student associated with the usage event
facility_id	INT	Facility associated with the usage event
used_at	TIMESTAMP	Timestamp when the facility was used
status	VARCHAR(20)	Usage event status

Your question is SQL for Monthly Facility Usage. Start with the requirements and the three 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