Day Each Branch Crossed 50% Placement — SQL Problem
The placement cell celebrates the day each branch gets half its students placed.
- Difficulty: Hard
- Topics: Window Functions, Dates, Subqueries
- Dialect: MySQL
- Problem: #147
Problem statement
The placement cell celebrates the day each branch gets half its students placed. A student is placed on the date of their earliest accepted offer; later offers to the same student change nothing, and declined or revoked offers never count.
For every branch that reached the mark, return branch, students (all students of the branch), crossed_on (the first date on which the number of placed students reached at least half of students) and placed_by_then (placed students as of the end of that date). Branches that never reached half are left out. Order the rows by branch.
Tables
Table: Student
| Column | Type |
|---|---|
| roll_no | int |
| name | varchar |
| branch | varchar |
Primary key: roll_no.
One row per final-year student.
Table: PlacementOffer
| Column | Type |
|---|---|
| offer_id | int |
| roll_no | int |
| company | varchar |
| offered_on | date |
| offer_status | enum(accepted, declined, revoked) |
Primary key: offer_id.
roll_no is always in Student; a student may accept more than one offer.
Examples
Example 1
Student
| roll_no | name | branch |
|---|---|---|
| 2101 | Aarav | CSE |
| 2102 | Diya | CSE |
| 2103 | Ishaan | CSE |
| 2104 | Kavya | CSE |
| 2105 | Neha | ECE |
| 2106 | Rohan | ECE |
| 2107 | Sneha | ECE |
| 2108 | Vikram | MECH |
| 2109 | Zara | MECH |
PlacementOffer
| offer_id | roll_no | company | offered_on | offer_status |
|---|---|---|---|---|
| 1 | 2101 | Atlassian | 2024-08-05 | accepted |
| 2 | 2101 | Zoho | 2024-08-20 | accepted |
| 3 | 2102 | TCS | 2024-08-12 | declined |
| 4 | 2103 | Razorpay | 2024-08-12 | accepted |
| 5 | 2105 | Wipro | 2024-08-07 | accepted |
| 6 | 2106 | Infosys | 2024-09-02 | accepted |
| 7 | 2107 | Accenture | 2024-09-02 | accepted |
| 8 | 2108 | Deloitte | 2024-08-30 | revoked |
Output
| branch | students | crossed_on | placed_by_then |
|---|---|---|---|
| CSE | 4 | 2024-08-12 | 2 |
| ECE | 3 | 2024-09-02 | 3 |
How to solve Day Each Branch Crossed 50% Placement
Break the question into a chain of CTEs. placed: one row per placed student with their placement date, MIN(offered_on) over accepted offers — a student accepting two offers is placed once, on the earlier day. daily: how many students of each branch were placed on each date. Grouping by date first matters, because several students placed on the same day must enter the running total together; a running sum over raw rows would report a date with only some of that day's placements counted.
running: SUM(newly_placed) OVER (PARTITION BY branch ORDER BY placed_on ROWS …) is the cumulative number placed by the end of each date; dates are unique per branch after grouping, so the frame is exact. sizes counts every student of the branch, placed or not. Finally keep the dates where placed_by_then * 2 >= students — integer arithmetic, so 2 of 4 counts — and take the earliest per branch with ROW_NUMBER(). Branches that never get there have no qualifying date and drop out.
The alternative replaces the window with a correlated count of students placed on or before each candidate date, and picks the minimum qualifying date with another subquery — the same logic, quadratic in the number of placements.
Reference solution (MySQL)
WITH placed AS (
SELECT s.branch, o.roll_no, MIN(o.offered_on) AS placed_on
FROM PlacementOffer o
JOIN Student s ON s.roll_no = o.roll_no
WHERE o.offer_status = 'accepted'
GROUP BY s.branch, o.roll_no
), daily AS (
SELECT branch, placed_on, COUNT(*) AS newly_placed
FROM placed
GROUP BY branch, placed_on
), running AS (
SELECT branch, placed_on,
SUM(newly_placed) OVER (PARTITION BY branch ORDER BY placed_on ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS placed_by_then
FROM daily
), sizes AS (
SELECT branch, COUNT(*) AS students FROM Student GROUP BY branch
), crossed AS (
SELECT r.branch, z.students, r.placed_on, r.placed_by_then,
ROW_NUMBER() OVER (PARTITION BY r.branch ORDER BY r.placed_on) AS rn
FROM running r
JOIN sizes z ON z.branch = r.branch
WHERE r.placed_by_then * 2 >= z.students
)
SELECT branch, students, placed_on AS crossed_on, placed_by_then
FROM crossed
WHERE rn = 1
ORDER BY branchAnother way
WITH placed AS (SELECT s.branch, o.roll_no, MIN(o.offered_on) AS placed_on FROM PlacementOffer o JOIN Student s ON s.roll_no = o.roll_no WHERE o.offer_status = 'accepted' GROUP BY s.branch, o.roll_no), cand AS (SELECT DISTINCT p.branch, p.placed_on, (SELECT COUNT(*) FROM placed q WHERE q.branch = p.branch AND q.placed_on <= p.placed_on) AS placed_by_then, (SELECT COUNT(*) FROM Student z WHERE z.branch = p.branch) AS students FROM placed p) SELECT c.branch, c.students, c.placed_on AS crossed_on, c.placed_by_then FROM cand c WHERE c.placed_by_then * 2 >= c.students AND c.placed_on = (SELECT MIN(d.placed_on) FROM cand d WHERE d.branch = c.branch AND d.placed_by_then * 2 >= d.students) ORDER BY c.branch← Clashing Exam Slots in Students' Timetables · Pure-Veg Restaurants Rated 4.2 or Higher →