← ALL CHALLENGES
CHALLENGE / INTERMEDIATE / READY TO QUERY

ปีไหนร้านอาหารในโทรอนโตถูกสั่งให้แก้ไขมากที่สุด?

ฝึก GROUP BY และคำนวณสัดส่วนผลการตรวจ DineSafe รายปี โดยนับตามสถานประกอบการและวันที่ตรวจ

01 / THE QUESTION

Question

ปีใดมีสัดส่วนการตรวจ (นับตามสถานประกอบการและวันที่ตรวจ) ที่ได้ผล Conditional Pass หรือ Closed สูงที่สุด?

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

  • ไฟล์ DineSafe ปัจจุบันไม่มีคอลัมน์ประเภทสถานประกอบการ ส่วน infraction_description คือรายละเอียดการกระทำผิด ไม่ใช่ประเภทร้าน
  • ถือว่า Conditional Pass และ Closed เป็นกลุ่มที่ต้องติดตาม และไม่นับ Temporarily Not Operating ในตัวหาร
  • สถานประกอบการหนึ่งแห่งอาจมีหลายรายการในวันเดียวกัน จึงควรจัดกลุ่มด้วย establishment_id และ inspection_date ก่อนคำนวณ
  • ปีแรกและปีปัจจุบันมีข้อมูลไม่ครบทั้งปี จึงควรระวังเมื่อนำมาเปรียบเทียบ

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;