← 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;