UptoSkills Logo
Login
Internship Performance by DepartmentMedium

Internship Performance by Department

MediumSQLPractice library · +20 XPmulti-table joinssqlaggregation

UptoSkills Arena helps students find internships. We want to analyze the performance of interns who have been with us for a significant period. Write a query to find the average internship score for each department, considering only interns who have completed at least 90 days.

Tables:

  • Departments: dept_id (INT, PK), dept_name (VARCHAR(50))
  • Interns: intern_id (INT, PK), intern_name (VARCHAR(100)), dept_id (INT, FK to Departments), start_date (DATE), score (INTEGER)

Return the department name and the calculated average score for interns who meet the duration criteria. Handle departments with no qualifying interns by excluding them from the result. If a department has qualifying interns but all have a NULL score, treat their average as NULL.

Order the results by department name alphabetically.

Tables

CREATE TABLE Departments (
  dept_id INTEGER PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE Interns (
  intern_id INTEGER PRIMARY KEY,
  intern_name VARCHAR(100) NOT NULL,
  dept_id INTEGER REFERENCES Departments(dept_id),
  start_date DATE NOT NULL,
  score INTEGER
);

Example data

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

INSERT INTO Interns (intern_id, intern_name, dept_id, start_date, score)
VALUES
(101, 'Alice', 1, DATE '2024-05-01', 85),
(102, 'Bob', 1, DATE '2024-08-15', 92),
(103, 'Charlie', 2, DATE '2024-01-10', 78),
(104, 'David', 3, DATE '2024-09-01', 88),
(105, 'Eve', 1, DATE '2024-06-20', 95),
(106, 'Frank', 2, DATE '2024-07-01', NULL),
(107, 'Grace', 4, DATE '2024-09-14', 82);

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

Hints

Similar problems

More in: SQL and Databases problems