First Student to Submit Each Assignment

Medium
131.

First Student to Submit Each Assignment

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

ColumnType
assignment_idint
course_codevarchar
titlevarchar
due_atdatetime

Primary key: assignment_id.

One row per assignment released on the portal.

Table: Submission

ColumnType
submission_idint
assignment_idint
roll_noint
submitted_atdatetime

Primary key: submission_id.

One row per upload; assignment_id is always in Assignment.

Example

The rows must come back in the order shown.

Assignment

assignment_idcourse_codetitledue_at
1CS301Process Scheduling2024-09-10 23:59:00
2CS301Deadlock Detection2024-09-24 23:59:00
3MA201Bayes Problem Set2024-09-15 23:59:00

Submission

submission_idassignment_idroll_nosubmitted_at
1121032024-09-08 21:14:05
2121012024-09-08 19:02:40
3121022024-09-09 10:00:00
4121012024-09-07 22:30:00
5321042024-09-12 18:45:10
6321022024-09-12 18:45:10
7321052024-09-13 08:00:00

Output

assignment_idtitleroll_nosubmitted_at
1Process Scheduling21012024-09-07 22:30:00
3Bayes Problem Set21022024-09-12 18:45:10
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
assignment_idint
course_codevarchar
titlevarchar
due_atdatetime

Primary key: assignment_id.

One row per assignment released on the portal.

Table: Submission

ColumnType
submission_idint
assignment_idint
roll_noint
submitted_atdatetime

Primary key: submission_id.

One row per upload; assignment_id is always in Assignment.

Examples

Example 1

Assignment

assignment_idcourse_codetitledue_at
1CS301Process Scheduling2024-09-10 23:59:00
2CS301Deadlock Detection2024-09-24 23:59:00
3MA201Bayes Problem Set2024-09-15 23:59:00

Submission

submission_idassignment_idroll_nosubmitted_at
1121032024-09-08 21:14:05
2121012024-09-08 19:02:40
3121022024-09-09 10:00:00
4121012024-09-07 22:30:00
5321042024-09-12 18:45:10
6321022024-09-12 18:45:10
7321052024-09-13 08:00:00

Output

assignment_idtitleroll_nosubmitted_at
1Process Scheduling21012024-09-07 22:30:00
3Bayes Problem Set21022024-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_id

Another 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_id

Another 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 →