Your question is SQL Window Functions for Deadlines. Start with the requirements and the four tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
Newton School of Technology wants to identify students who may need support because they are repeatedly missing project deadlines.
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.
sequence_number.| Column | Type | Description |
|---|---|---|
| student_idPK | INT | Unique student identifier |
| student_name | VARCHAR(100) | Student's full name |
| 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 |
| Column | Type | Description |
|---|---|---|
| assignment_idPK | INT | Unique assignment identifier |
| student_id | INT | Assigned student |
| project_id | INT | Assigned project |
| 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 |