Late Coursework Uploads and Average Hours Late per Course
The dean's office suspects some courses set deadlines students cannot meet.
- Difficulty: Medium
- Topics: Dates, Joins, Aggregation
- Dialect: MySQL
- Problem: #138
Problem statement
The dean's office suspects some courses set deadlines students cannot meet. For each course it wants the share of coursework uploads that came in after the task's deadline and how late they were on average.
For every course with at least one upload, return course_code, uploads, late_uploads (uploads strictly after due_at) and avg_hours_late — the average of TIMESTAMPDIFF(HOUR, due_at, uploaded_at) over the late uploads only (whole hours, truncated), rounded to 2 decimal places, or NULL when the course has no late uploads. An upload at exactly due_at is on time. Order the rows by course_code.
Tables
Table: CourseworkTask
| Column | Type |
|---|---|
| task_id | int |
| course_code | varchar |
| title | varchar |
| due_at | datetime |
Primary key: task_id.
One row per lab record, assignment or project milestone.
Table: Upload
| Column | Type |
|---|---|
| upload_id | int |
| task_id | int |
| roll_no | int |
| uploaded_at | datetime |
Primary key: upload_id.
task_id is always in CourseworkTask.
Examples
Example 1
CourseworkTask
| task_id | course_code | title | due_at |
|---|---|---|---|
| 1 | CS301 | Shell Scripting Lab | 2024-08-20 23:59:00 |
| 2 | CS301 | Scheduler Simulation | 2024-09-05 17:00:00 |
| 3 | MA201 | Hypothesis Testing Sheet | 2024-08-28 10:00:00 |
| 4 | EC210 | Counter Design | 2024-09-10 23:59:00 |
Upload
| upload_id | task_id | roll_no | uploaded_at |
|---|---|---|---|
| 1 | 1 | 2101 | 2024-08-20 22:10:00 |
| 2 | 1 | 2102 | 2024-08-21 03:30:00 |
| 3 | 2 | 2101 | 2024-09-05 17:00:00 |
| 4 | 2 | 2103 | 2024-09-07 09:15:00 |
| 5 | 3 | 2101 | 2024-08-27 18:00:00 |
| 6 | 3 | 2104 | 2024-08-28 09:59:59 |
| 7 | 1 | 2104 | 2024-08-20 23:59:30 |
Output
| course_code | uploads | late_uploads | avg_hours_late |
|---|---|---|---|
| CS301 | 5 | 3 | 14.33 |
| MA201 | 2 | 0 | NULL |
How to solve Late Coursework Uploads and Average Hours Late per Course
Join each upload to its task so the deadline sits beside the upload time, then group by course. COUNT(*) is all uploads; SUM(CASE WHEN uploaded_at > due_at THEN 1 ELSE 0 END) counts the late ones. The comparison is strict, so an upload stamped exactly at the deadline is on time.
The average lateness must be taken over late uploads only. The trick is that AVG skips NULLs: AVG(CASE WHEN late THEN TIMESTAMPDIFF(HOUR, due_at, uploaded_at) END) produces a number for late rows and NULL for on-time rows, so on-time uploads affect neither the sum nor the count. A course with no late uploads averages nothing and gets NULL, exactly as the statement asks. Averaging GREATEST(0, …) instead would wrongly count on-time uploads as zero hours.
TIMESTAMPDIFF(HOUR, …) counts whole hours and truncates, so 3 h 31 min is 3. The alternative computes the late average in a separate grouped query and LEFT JOINs it back, which also gives NULL for courses with no late uploads.
Reference solution (MySQL)
SELECT t.course_code,
COUNT(*) AS uploads,
SUM(CASE WHEN u.uploaded_at > t.due_at THEN 1 ELSE 0 END) AS late_uploads,
ROUND(AVG(CASE WHEN u.uploaded_at > t.due_at THEN TIMESTAMPDIFF(HOUR, t.due_at, u.uploaded_at) END), 2) AS avg_hours_late
FROM Upload u
JOIN CourseworkTask t ON t.task_id = u.task_id
GROUP BY t.course_code
ORDER BY t.course_codeAnother way
SELECT a.course_code, a.uploads, a.late_uploads, l.avg_hours_late FROM (SELECT t.course_code, COUNT(*) AS uploads, SUM(IF(u.uploaded_at > t.due_at, 1, 0)) AS late_uploads FROM Upload u JOIN CourseworkTask t ON t.task_id = u.task_id GROUP BY t.course_code) a LEFT JOIN (SELECT t.course_code, ROUND(AVG(TIMESTAMPDIFF(HOUR, t.due_at, u.uploaded_at)), 2) AS avg_hours_late FROM Upload u JOIN CourseworkTask t ON t.task_id = u.task_id WHERE u.uploaded_at > t.due_at GROUP BY t.course_code) l ON l.course_code = a.course_code ORDER BY a.course_code← Placement Rate and Top Package by Branch · Course Catalogue Summary by Department and Level →