Drill 3 of 12 in Education questions
0

Interview Question Bank · Timestamp comparison · COUNT FILTER

3. Late submission rate by course

EasyEducationQuestion #219

Concept: Timestamp comparison · COUNT FILTERshow

A submission is late when its timestamp is after the deadline, so compare the two columns directly: submitted_at > due_at. Strictly greater means a hand-in exactly at the deadline is on time. COUNT(*) FILTER (WHERE ...) counts the late ones next to the total in the same GROUP BY, and multiplying by 100.0 keeps the percentage from being cut to a whole number.

Example (different data):

SELECT route, COUNT(*) AS trips,
  COUNT(*) FILTER (WHERE arrived_at > scheduled_at) AS delayed
FROM bus_trips
GROUP BY route
ORDER BY route;

Your task

Brightpath Academy's cohort courses have hard deadlines at 23:59 on the due day. The dean wants to know which courses struggle most with late work.

For each course with submissions, return course (title), submissions, late (submitted_at after the assignment's due_at; exactly on the deadline is on time) and late_pct (late as a % of submissions, rounded to 1 decimal). Sort by late_pct descending, then course.

Starting Postgres in your browser…

Checking your access…