SQL Window Functions for Deadlines

HardSQL · PostgreSQL

Problem

Business Context

Newton School of Technology wants to identify students who may need support because they are repeatedly missing project deadlines.

Task

Write a PostgreSQL query that finds every run of at least two consecutive missed project deadlines for each student. A deadline is missed when the student has no submission or submits after the deadline.

Requirements

Join students, project assignments, projects, and submissions.
Use window functions to detect consecutive missed projects according to sequence_number.
Treat missing submissions and late submissions as missed deadlines.
Return one row per consecutive run, including the student, number of missed deadlines, first and last deadline, and project names.
Exclude runs containing fewer than two consecutive missed deadlines, and order results by student and run start.

Schema

students

Column	Type	Description
student_idPK	INT	Unique student identifier
student_name	VARCHAR(100)	Student's full name

projects

Column	Type	Description
project_idPK	INT	Unique project identifier
project_name	VARCHAR(150)	Project title
sequence_number	INT	Chronological project sequence
deadline	DATE	Project submission deadline

assignments

Column	Type	Description
assignment_idPK	INT	Unique assignment identifier
student_id	INT	Assigned student
project_id	INT	Assigned project

submissions

Column	Type	Description
submission_idPK	INT	Unique submission identifier
assignment_id	INT	Assignment associated with the submission
submitted_at	DATE	Date the project was submitted

Tablesstudentsprojectsassignmentssubmissions