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

Top 2 Interns by Monthly Stipend in Each Department

MediumSQLPractice library · +20 XPtop n per groupsqlwindow functions

UptoSkills Arena is organizing internships for students across various departments. We have internship offers with details about the intern, the department, and their monthly stipend.

Tables:

  • Departments (department_id INT PRIMARY KEY, department_name VARCHAR(100) NOT NULL)
  • Interns (intern_id INT PRIMARY KEY, intern_name VARCHAR(100) NOT NULL)
  • InternshipOffers (offer_id INT PRIMARY KEY, intern_id INT, department_id INT, monthly_stipend NUMERIC(10, 2), start_date DATE, FOREIGN KEY (intern_id) REFERENCES Interns(intern_id), FOREIGN KEY (department_id) REFERENCES Departments(department_id))

Return the department_name, intern_name, and monthly_stipend for the top 2 interns in each department, ranked by monthly_stipend in descending order. If there are fewer than 2 interns in a department, return all of them. If multiple interns share the same stipend and would qualify for the top 2, include all of them. NULL stipends should be ignored. The final output should be ordered by department_name and then by monthly_stipend in descending order.

Tables

CREATE TABLE Departments (
    department_id integer PRIMARY KEY,
    department_name varchar(100) NOT NULL
);
CREATE TABLE Interns (
    intern_id integer PRIMARY KEY,
    intern_name varchar(100) NOT NULL
);
CREATE TABLE InternshipOffers (
    offer_id integer PRIMARY KEY,
    intern_id integer REFERENCES Interns(intern_id),
    department_id integer REFERENCES Departments(department_id),
    monthly_stipend numeric(10, 2),
    start_date date
);

Example data

INSERT INTO Departments (department_id, department_name) VALUES
(1, 'Engineering'),
(2, 'Human Resources'),
(3, 'Marketing'),
(4, 'Finance');

INSERT INTO Interns (intern_id, intern_name) VALUES
(101, 'Alice'),
(102, 'Bob'),
(103, 'Charlie'),
(104, 'David'),
(105, 'Eve'),
(106, 'Frank'),
(107, 'Grace'),
(108, 'Heidi');

INSERT INTO InternshipOffers (offer_id, intern_id, department_id, monthly_stipend, start_date) VALUES
(1, 101, 1, 50000.00, '2024-07-01'),
(2, 102, 1, 45000.00, '2024-07-01'),
(3, 103, 1, 45000.00, '2024-07-01'),
(4, 104, 1, 40000.00, '2024-07-01'),
(5, 105, 2, 60000.00, '2024-07-01'),
(6, 106, 2, 55000.00, '2024-07-01'),
(7, 107, 3, 52000.00, '2024-07-01'),
(8, 108, 3, 48000.00, '2024-07-01'),
(9, 101, 2, 58000.00, '2024-07-01');

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

Hints

Similar problems

More in: SQL and Databases problems