Interview Question Bank · to_char weekday · RANK per group · ties
6. Busiest day of the week per clinic
MediumHealthcareQuestion #138
Concept: to_char weekday · RANK per group · tiesshow
to_char(date, 'FMDay') gives the weekday name; without FM, Postgres pads it to 9 characters ('Monday '). To find the top group within each partition, aggregate, then RANK() OVER (PARTITION BY ... ORDER BY COUNT(*) DESC) and keep rank 1. RANK gives tied groups the same rank, so a tie returns both, where ROW_NUMBER or LIMIT would silently pick one.
Example (different data):
WITH h AS (
SELECT store, EXTRACT(HOUR FROM sold_at) AS hr, COUNT(*) AS sales,
RANK() OVER (PARTITION BY store ORDER BY COUNT(*) DESC) AS rk
FROM sales GROUP BY store, 2
)
SELECT store, hr, sales FROM h WHERE rk = 1;Your task
Larkspur Health is deciding which weekday to add extra front-desk staff at each outpatient clinic, based on visits from February to May 2025.
For each clinic, return clinic, weekday (the full day name, like 'Monday') and visits for its busiest weekday. If days tie, return all of them. Sort by clinic, then weekday from Monday to Sunday.
Starting Postgres in your browser…
Checking your access…