← 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_description is not a type of restaurant.
  • Treat Conditional Pass or Closed as a follow-up status. Exclude Temporarily Not Operating from the denominator because it is a different state.
  • Multiple source records can share establishment_id and inspection_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;