Routes with Weekly Visitor Demand | Chill Interview
Routes with Weekly Visitor Demand
Asked by:
MetaOnsite
A regional trail network records daily visitor totals reported by its counting stations. Planners want to know how many routes attracted meaningful demand during the latest week represented in the data.
Table: route_visits
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| station_id | integer |
| route_id | integer |
| visitor_count | integer |
| visit_date | date |
+---------------+---------+
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| station_id | integer |
| route_id | integer |
| visitor_count | integer |
| visit_date | date |
+---------------+---------+
The combination of station_id, route_id, and visit_date is the primary key. Each row records the number of visitors reported by one station for a route on one date. Assume visitor_count is nonnegative.
Find the maximum visit_date in the table. Define the reporting window as the seven calendar dates ending on that maximum date: the maximum date itself and the six preceding dates.
Within that window, sum visitor_count for each route_id across all dates and stations. A route qualifies when its total is at least 10.
Return one row containing the number of qualifying routes in a column named route_count. If the table is empty, return 0.
The maximum date is 2026-08-30, so the window covers 2026-08-24 through 2026-08-30. Route 10 totals exactly 10 visitors and qualifies. Route 20 totals 9, while route 30's visits fall before the window.
