500,269 requests: what Toronto asked its city for in 2025

-- Number of 311 service requests created in each calendar month.
SELECT strftime('%Y-%m', created_at) AS month,
       COUNT(*)                      AS requests
FROM   service_requests
GROUP  BY month
ORDER  BY month;

-- The 10 most common service request types, with their share of all requests.
SELECT request_type,
       COUNT(*) AS requests,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM service_requests), 2) AS pct_of_total
FROM   service_requests
GROUP  BY request_type
ORDER  BY requests DESC
LIMIT  10;

-- Status breakdown per responsible division (counts). The Python script turns
-- this into percentages for the stacked bar chart.
SELECT division,
       status,
       COUNT(*) AS requests
FROM   service_requests
GROUP  BY division, status
ORDER  BY division, requests DESC;

-- Requests by day of week (0 = Sunday ... 6 = Saturday) and hour of day.
-- Feeds the heatmap.
SELECT CAST(strftime('%w', created_at) AS INTEGER) AS weekday,
       CAST(strftime('%H', created_at) AS INTEGER) AS hour,
       COUNT(*)                                     AS requests
FROM   service_requests
GROUP  BY weekday, hour
ORDER  BY weekday, hour;

-- Wards with the most requests, and each ward's share of requests that ended
-- up Cancelled. Wards with a missing name are excluded.
SELECT ward,
       COUNT(*) AS requests,
       ROUND(100.0 * SUM(status = 'Cancelled') / COUNT(*), 1) AS pct_cancelled
FROM   service_requests
WHERE  ward IS NOT NULL
GROUP  BY ward
ORDER  BY requests DESC
LIMIT  10;

-- Basic data-quality profile: row count, date range and missing values.
SELECT COUNT(*)                                        AS total_rows,
       MIN(created_at)                                 AS first_request,
       MAX(created_at)                                 AS last_request,
       SUM(ward IS NULL OR ward = 'Unknown')       AS missing_ward,
       SUM(request_type IS NULL)                       AS missing_type,
       SUM(location_kind = 'Intersection')             AS rows_located_by_intersection,
       COUNT(DISTINCT request_type)                    AS distinct_request_types
FROM   service_requests;

-- The five busiest individual days.
SELECT date(created_at) AS day,
       COUNT(*)         AS requests
FROM   service_requests
GROUP  BY day
ORDER  BY requests DESC
LIMIT  5;

-- Overall status distribution.
SELECT status,
       COUNT(*) AS requests,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM service_requests), 1) AS pct
FROM   service_requests
GROUP  BY status
ORDER  BY requests DESC;

-- Requests per division.
SELECT division,
       COUNT(*) AS requests,
       ROUND(100.0 * COUNT(*) / (SELECT COUNT(*) FROM service_requests), 1) AS pct
FROM   service_requests
GROUP  BY division
ORDER  BY requests DESC;

-- What people were asking for on the single busiest day of the year.
SELECT request_type,
       COUNT(*) AS requests
FROM   service_requests
WHERE  date(created_at) = (SELECT date(created_at)
                           FROM   service_requests
                           GROUP  BY date(created_at)
                           ORDER  BY COUNT(*) DESC
                           LIMIT  1)
GROUP  BY request_type
ORDER  BY requests DESC
LIMIT  5;