← ALL CHALLENGES
CHALLENGE / INTERMEDIATE / READY TO QUERY
When did Toronto inspections most often need follow-up?
Practice grouping DineSafe outcomes by year and calculating a share from source records.
01 / THE QUESTION
Question
In which years did the highest share of Toronto DineSafe establishment-day groups have a Conditional Pass or Closed status?
02 / SOURCE TABLES
Tables to use
03 / RELATIONSHIP
ER diagram
The table relationship will be documented when a compatible dataset is available.
04 / ANALYST GUIDE
Guide
Analysis requirements
- The current DineSafe file has no establishment-type field.
infraction_descriptionis not a type of restaurant. - Treat
Conditional PassorClosedas a follow-up status. ExcludeTemporarily Not Operatingfrom the denominator because it is a different state. - Multiple source records can share
establishment_idandinspection_date. First group those records, then calculate a year-level share. This is an establishment-day proxy; multiple inspections on one day cannot be distinguished. - The file begins in late 2023 and the current year is incomplete. Compare full years carefully.
Suggested query
Expand suggested query
SQL / STARTER QUERY
WITH establishment_days AS (
SELECT establishment_id, inspection_date, year,
MAX(inspection_status) AS inspection_status
FROM toronto_dinesafe_inspection_records
WHERE establishment_id IS NOT NULL AND inspection_date IS NOT NULL
GROUP BY establishment_id, inspection_date, year
)
SELECT year,
COUNT(*) AS establishment_days,
COUNT(*) FILTER (WHERE inspection_status IN ('Conditional Pass', 'Closed')) AS follow_up_days,
COUNT(*) FILTER (WHERE inspection_status IN ('Conditional Pass', 'Closed')) * 1.0
/ NULLIF(COUNT(*), 0) AS follow_up_share
FROM establishment_days
WHERE inspection_status IN ('Pass', 'Conditional Pass', 'Closed')
GROUP BY year
ORDER BY year;