Busiest Hour per Place
A maps app shows a "Popular times" chart for every café, gym and museum, and highlights the hour when the place is usually at its busiest. The chart is built from anonymised visits. You are writing the query that finds that peak hour.
Tables
places
place_id: the place's id.name: the place's name, unique.
visits
visit_id: the visit's id.place_id: the place that was visited.visited_at: when the visit started, as aTIMESTAMPin the place's local time.
Task
For every place that has at least one visit, find the hour of the day (0 to 23) with the most visits, counting visits from all days together: a visit at 08:40 on Monday and one at 08:15 on Tuesday both count for hour 8.
If several hours tie for the most visits, return every tied hour, one row each. Places with no visits do not appear.
Example
In the sample, Riverside Gym has two visits in hour 7, four in hour 18 (from four different days) and one in hour 19, so its row is Riverside Gym | 18 | 4. Old Town Museum has no visits and is left out.
| place | hour | visits |
|---|---|---|
| Blue Bottle Cafe | 8 | 3 |
| Central Library | 10 | 3 |
| Riverside Gym | 18 | 4 |
Submitting also runs your answer against 3 hidden datasets, each built around an edge case: NULLs, ties, empty tables. A failure names the case without showing its data.
Follow-up: Product now wants the peak hour per day of the week (Mondays peak at 8, Saturdays at 11). What changes in your grouping and your window, and what index on `visits` would make the query cheap for one place?
- Return the columns `place` (the place's `name`), `hour` (an integer from 0 to 23) and `visits` (the number of visits in that hour), in that order. - A visit belongs to the hour its `visited_at` falls in: 10:59:59 is hour 10 and 11:00:00 is hour 11. - Return one row per tied peak hour. - Sort by `place`, then by `hour`.
- Views
- 2