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…