Placement Rate and Top Package by Branch

Medium
137.

Placement Rate and Top Package by Branch

MediumJoinsAggregation

MySQL

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

ColumnType
roll_noint
namevarchar
branchvarchar

Primary key: roll_no.

One row per final-year student.

Table: PlacementOffer

ColumnTypeValues
offer_idint
roll_noint
companyvarchar
ctc_lpadecimal
offer_statusenumaccepted, declined, revoked

Primary key: offer_id.

ctc_lpa is lakh rupees per annum with one decimal; a student may hold several accepted offers.

Example

The rows must come back in the order shown.

Student

roll_nonamebranch
2101AaravCSE
2102DiyaCSE
2103IshaanCSE
2104KavyaECE
2105NehaECE
2106RohanMECH

PlacementOffer

offer_idroll_nocompanyctc_lpaoffer_status
12101Atlassian32.5accepted
22101TCS7accepted
32102Zoho9accepted
42104Wipro6.5accepted
52105Razorpay18declined
62106Infosys4.5revoked

Output

branchstudentsplacedplacement_pcttop_ctc_lpa
CSE3266.6732.5
ECE21506.5
MECH100NULL
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.

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

ColumnType
roll_noint
namevarchar
branchvarchar

Primary key: roll_no.

One row per final-year student.

Table: PlacementOffer

ColumnType
offer_idint
roll_noint
companyvarchar
ctc_lpadecimal
offer_statusenum(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_nonamebranch
2101AaravCSE
2102DiyaCSE
2103IshaanCSE
2104KavyaECE
2105NehaECE
2106RohanMECH

PlacementOffer

offer_idroll_nocompanyctc_lpaoffer_status
12101Atlassian32.5accepted
22101TCS7accepted
32102Zoho9accepted
42104Wipro6.5accepted
52105Razorpay18declined
62106Infosys4.5revoked

Output

branchstudentsplacedplacement_pcttop_ctc_lpa
CSE3266.6732.5
ECE21506.5
MECH100NULL

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.branch

Another 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, branch

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