Late Coursework Uploads and Average Hours Late per Course

Medium
138.

Late Coursework Uploads and Average Hours Late per Course

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

ColumnType
task_idint
course_codevarchar
titlevarchar
due_atdatetime

Primary key: task_id.

One row per lab record, assignment or project milestone.

Table: Upload

ColumnType
upload_idint
task_idint
roll_noint
uploaded_atdatetime

Primary key: upload_id.

task_id is always in CourseworkTask.

Example

The rows must come back in the order shown.

CourseworkTask

task_idcourse_codetitledue_at
1CS301Shell Scripting Lab2024-08-20 23:59:00
2CS301Scheduler Simulation2024-09-05 17:00:00
3MA201Hypothesis Testing Sheet2024-08-28 10:00:00
4EC210Counter Design2024-09-10 23:59:00

Upload

upload_idtask_idroll_nouploaded_at
1121012024-08-20 22:10:00
2121022024-08-21 03:30:00
3221012024-09-05 17:00:00
4221032024-09-07 09:15:00
5321012024-08-27 18:00:00
6321042024-08-28 09:59:59
7121042024-08-20 23:59:30

Output

course_codeuploadslate_uploadsavg_hours_late
CS3015314.33
MA20120NULL
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
task_idint
course_codevarchar
titlevarchar
due_atdatetime

Primary key: task_id.

One row per lab record, assignment or project milestone.

Table: Upload

ColumnType
upload_idint
task_idint
roll_noint
uploaded_atdatetime

Primary key: upload_id.

task_id is always in CourseworkTask.

Examples

Example 1

CourseworkTask

task_idcourse_codetitledue_at
1CS301Shell Scripting Lab2024-08-20 23:59:00
2CS301Scheduler Simulation2024-09-05 17:00:00
3MA201Hypothesis Testing Sheet2024-08-28 10:00:00
4EC210Counter Design2024-09-10 23:59:00

Upload

upload_idtask_idroll_nouploaded_at
1121012024-08-20 22:10:00
2121022024-08-21 03:30:00
3221012024-09-05 17:00:00
4221032024-09-07 09:15:00
5321012024-08-27 18:00:00
6321042024-08-28 09:59:59
7121042024-08-20 23:59:30

Output

course_codeuploadslate_uploadsavg_hours_late
CS3015314.33
MA20120NULL

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_code

Another 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 →