Placement Rate and Top Package by Branch — SQL Joins Problem
The training and placement officer's annual report shows, for each branch, how many final-year students were placed and the best package anyone accepted.
- Difficulty: Medium
- Topics: Joins, Aggregation
- Dialect: MySQL
- Problem: #137
Problem statement
The training and placement officer's annual report shows, for each branch, how many final-year students were placed and the best package anyone accepted. A student counts as placed when they hold at least one accepted offer; declined and revoked offers do not count.
Return one row per branch with at least one student: branch, students, placed, placement_pct (placed as a percentage of students, rounded to 2 decimal places) and top_ctc_lpa (the highest accepted CTC in the branch, NULL if nobody was placed). Order the rows by placement_pct highest first, then 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 |
| ctc_lpa | decimal |
| offer_status | enum(accepted, declined, revoked) |
Primary key: offer_id.
ctc_lpa is lakh rupees per annum with one decimal; a student may hold several accepted offers.
Examples
Example 1
Student
| roll_no | name | branch |
|---|---|---|
| 2101 | Aarav | CSE |
| 2102 | Diya | CSE |
| 2103 | Ishaan | CSE |
| 2104 | Kavya | ECE |
| 2105 | Neha | ECE |
| 2106 | Rohan | MECH |
PlacementOffer
| offer_id | roll_no | company | ctc_lpa | offer_status |
|---|---|---|---|---|
| 1 | 2101 | Atlassian | 32.5 | accepted |
| 2 | 2101 | TCS | 7 | accepted |
| 3 | 2102 | Zoho | 9 | accepted |
| 4 | 2104 | Wipro | 6.5 | accepted |
| 5 | 2105 | Razorpay | 18 | declined |
| 6 | 2106 | Infosys | 4.5 | revoked |
Output
| branch | students | placed | placement_pct | top_ctc_lpa |
|---|---|---|---|---|
| CSE | 3 | 2 | 66.67 | 32.5 |
| ECE | 2 | 1 | 50 | 6.5 |
| MECH | 1 | 0 | 0 | NULL |
How to solve Placement Rate and Top Package by Branch
Every branch's denominator is all of its students, placed or not, so the query must start from Student and LEFT JOIN the offers. The accepted condition belongs in the ON clause: there it decides which offers may match, and a student with only declined or revoked offers stays in the result with NULL offer columns. In WHERE it would remove that student altogether.
The join produces one row per accepted offer, so a student with two offers appears twice. COUNT(DISTINCT s.roll_no) counts students and COUNT(DISTINCT o.roll_no) counts the placed ones — the NULLs of unmatched students are ignored by COUNT. MAX(o.ctc_lpa) is the branch's top package, and it is NULL when no row matched, as the statement asks.
Collapsing offers to one row per student first (a CTE with each student's best accepted CTC) removes the duplicates before the join, so plain COUNTs work. Both are a pass over each table.
Reference solution (MySQL)
SELECT s.branch,
COUNT(DISTINCT s.roll_no) AS students,
COUNT(DISTINCT o.roll_no) AS placed,
ROUND(100 * COUNT(DISTINCT o.roll_no) / COUNT(DISTINCT s.roll_no), 2) AS placement_pct,
MAX(o.ctc_lpa) AS top_ctc_lpa
FROM Student s
LEFT JOIN PlacementOffer o ON o.roll_no = s.roll_no AND o.offer_status = 'accepted'
GROUP BY s.branch
ORDER BY placement_pct DESC, s.branchAnother way
WITH best AS (SELECT roll_no, MAX(ctc_lpa) AS best_ctc FROM PlacementOffer WHERE offer_status = 'accepted' GROUP BY roll_no) SELECT s.branch, COUNT(*) AS students, COUNT(b.roll_no) AS placed, ROUND(100 * COUNT(b.roll_no) / COUNT(*), 2) AS placement_pct, MAX(b.best_ctc) AS top_ctc_lpa FROM Student s LEFT JOIN best b ON b.roll_no = s.roll_no GROUP BY s.branch ORDER BY placement_pct DESC, branchAnother way
SELECT branch, COUNT(*) AS students, SUM(CASE WHEN EXISTS (SELECT 1 FROM PlacementOffer o WHERE o.roll_no = s.roll_no AND o.offer_status = 'accepted') THEN 1 ELSE 0 END) AS placed, ROUND(100 * SUM(CASE WHEN EXISTS (SELECT 1 FROM PlacementOffer o WHERE o.roll_no = s.roll_no AND o.offer_status = 'accepted') THEN 1 ELSE 0 END) / COUNT(*), 2) AS placement_pct, MAX((SELECT MAX(o.ctc_lpa) FROM PlacementOffer o WHERE o.roll_no = s.roll_no AND o.offer_status = 'accepted')) AS top_ctc_lpa FROM Student s GROUP BY branch ORDER BY 4 DESC, 1← Students Whose SGPA Fell by More Than One Point · Late Coursework Uploads and Average Hours Late per Course →