UptoSkills Logo
Login
Top 3 Interns by Monthly Stipend in Each DepartmentMedium

Top 3 Interns by Monthly Stipend in Each Department

MediumSQLPractice library · +20 XPtop n per groupsqlwindow functions

UptoSkills Arena is helping companies find the best talent. We have data on interns, their departments, and their monthly stipends.

Tables:

  • departments: dept_id (PK), dept_name
  • interns: intern_id (PK), intern_name, dept_id (FK), monthly_stipend (numeric(10,2))

Return the department name, intern name, and monthly stipend for the top 3 highest-paid interns in each department. If a department has fewer than 3 interns, return all of them. If there's a tie in stipend for the 3rd position, include all interns with that stipend. Handle NULL stipends by excluding them from ranking.

The final output should be ordered by department name and then by intern's monthly stipend in descending order.

Tables

CREATE TABLE departments (
    dept_id SERIAL PRIMARY KEY,
    dept_name VARCHAR(100) NOT NULL
);

CREATE TABLE interns (
    intern_id SERIAL PRIMARY KEY,
    intern_name VARCHAR(100) NOT NULL,
    dept_id INTEGER NOT NULL REFERENCES departments(dept_id),
    monthly_stipend NUMERIC(10, 2)
);

Example data

INSERT INTO departments (dept_id, dept_name) VALUES (1, 'Engineering'), (2, 'Marketing'), (3, 'HR');
INSERT INTO interns (intern_id, intern_name, dept_id, monthly_stipend) VALUES (1, 'Alice', 1, 50000.00), (2, 'Bob', 1, 60000.00), (3, 'Charlie', 1, 55000.00), (4, 'David', 1, 60000.00), (5, 'Eve', 2, 45000.00), (6, 'Frank', 2, 50000.00), (7, 'Grace', 2, 48000.00), (8, 'Heidi', 3, 40000.00), (9, 'Ivan', 3, 42000.00);

Your query is also checked on hidden datasets with different data.

Hints

Similar problems

More in: SQL and Databases problems