First Student to Submit Each Assignment — SQL Problem
A course portal gives a bonus mark to the first student who submits each assignment. Students may submit several times; any submission counts.
- Difficulty: Medium
- Topics: Window Functions, Joins
- Dialect: MySQL
- Problem: #131
Problem statement
A course portal gives a bonus mark to the first student who submits each assignment. Students may submit several times; any submission counts.
For every assignment that has at least one submission, return assignment_id, title, roll_no (the first submitter) and submitted_at (their earliest submission). When two students submitted at the same second, the lower roll_no wins. Assignments without submissions are not listed. Order the rows by assignment_id.
Tables
Table: Assignment
| Column | Type |
|---|---|
| assignment_id | int |
| course_code | varchar |
| title | varchar |
| due_at | datetime |
Primary key: assignment_id.
One row per assignment released on the portal.
Table: Submission
| Column | Type |
|---|---|
| submission_id | int |
| assignment_id | int |
| roll_no | int |
| submitted_at | datetime |
Primary key: submission_id.
One row per upload; assignment_id is always in Assignment.
Examples
Example 1
Assignment
| assignment_id | course_code | title | due_at |
|---|---|---|---|
| 1 | CS301 | Process Scheduling | 2024-09-10 23:59:00 |
| 2 | CS301 | Deadlock Detection | 2024-09-24 23:59:00 |
| 3 | MA201 | Bayes Problem Set | 2024-09-15 23:59:00 |
Submission
| submission_id | assignment_id | roll_no | submitted_at |
|---|---|---|---|
| 1 | 1 | 2103 | 2024-09-08 21:14:05 |
| 2 | 1 | 2101 | 2024-09-08 19:02:40 |
| 3 | 1 | 2102 | 2024-09-09 10:00:00 |
| 4 | 1 | 2101 | 2024-09-07 22:30:00 |
| 5 | 3 | 2104 | 2024-09-12 18:45:10 |
| 6 | 3 | 2102 | 2024-09-12 18:45:10 |
| 7 | 3 | 2105 | 2024-09-13 08:00:00 |
Output
| assignment_id | title | roll_no | submitted_at |
|---|---|---|---|
| 1 | Process Scheduling | 2101 | 2024-09-07 22:30:00 |
| 3 | Bayes Problem Set | 2102 | 2024-09-12 18:45:10 |
How to solve First Student to Submit Each Assignment
This is the classic first row per group. ROW_NUMBER() partitioned by assignment_id and ordered by submitted_at numbers each assignment's submissions in time order; the row numbered 1 is the earliest. Ordering by roll_no as a second key settles a tie on the same second the way the statement asks — without it, which of two simultaneous uploads gets number 1 is up to the engine.
A student who uploaded several times appears several times in the partition, but only their earliest upload can be first, so nothing special is needed for resubmissions. Joining to Assignment afterwards adds the title, and the inner join naturally leaves out assignments nobody submitted.
Without window functions, take the earliest timestamp per assignment with a correlated MIN and then the smallest roll number among the submissions at that time, or keep a submission only if NOT EXISTS an earlier (or equally early, lower-roll) one. The window version sorts each partition once and is the cheapest to read.
Reference solution (MySQL)
WITH ranked AS (
SELECT assignment_id, roll_no, submitted_at,
ROW_NUMBER() OVER (PARTITION BY assignment_id ORDER BY submitted_at, roll_no) AS rn
FROM Submission
)
SELECT a.assignment_id, a.title, r.roll_no, r.submitted_at
FROM ranked r
JOIN Assignment a ON a.assignment_id = r.assignment_id
WHERE r.rn = 1
ORDER BY a.assignment_idAnother way
SELECT a.assignment_id, a.title, s.roll_no, s.submitted_at FROM Assignment a JOIN Submission s ON s.assignment_id = a.assignment_id WHERE NOT EXISTS (SELECT 1 FROM Submission t WHERE t.assignment_id = s.assignment_id AND (t.submitted_at < s.submitted_at OR (t.submitted_at = s.submitted_at AND t.roll_no < s.roll_no))) GROUP BY a.assignment_id, a.title, s.roll_no, s.submitted_at ORDER BY a.assignment_idAnother way
SELECT a.assignment_id, a.title, MIN(s.roll_no) AS roll_no, s.submitted_at FROM Assignment a JOIN Submission s ON s.assignment_id = a.assignment_id WHERE s.submitted_at = (SELECT MIN(t.submitted_at) FROM Submission t WHERE t.assignment_id = a.assignment_id) GROUP BY a.assignment_id, a.title, s.submitted_at ORDER BY a.assignment_id← Students Who Accepted Offers of 10 LPA or More · Monthly Fee Collection by Payment Mode →