UptoSkills Logo
Login
Active Interns in the Last 30 DaysMedium

Active Interns in the Last 30 Days

MediumSQLPractice library · +20 XPdate functionssqlinternships

UptoSkills Arena is tracking intern activity to ensure productivity. We need to identify which interns have been actively contributing to projects recently.

Tables:

  • interns (intern_id INT PRIMARY KEY, name VARCHAR(100))
  • projects (project_id INT PRIMARY KEY, intern_id INT, start_date DATE, end_date DATE)

Return the intern_id and name of interns who have at least one project with an end_date within the last 30 days from '2025-04-01'. The results should be ordered by intern_id.

Handle ties by ordering by intern_id. NULL end_date values mean the project is ongoing and should be considered if the start_date is within the last 30 days. Empty results are possible. If an intern has no projects, they should not appear in the output.

Tables

CREATE TABLE interns (
  intern_id integer PRIMARY KEY,
  name varchar(100) NOT NULL
);
CREATE TABLE projects (
  project_id integer PRIMARY KEY,
  intern_id integer,
  start_date date NOT NULL,
  end_date date
);

Example data

INSERT INTO interns (intern_id, name) VALUES
(101, 'Alice'),
(102, 'Bob'),
(103, 'Charlie'),
(104, 'David'),
(105, 'Eve');

INSERT INTO projects (project_id, intern_id, start_date, end_date) VALUES
(1, 101, '2025-03-01', '2025-03-20'),
(2, 101, '2025-03-15', '2025-04-05'),
(3, 102, '2025-03-25', NULL),
(4, 103, '2025-02-01', '2025-03-10'),
(5, 104, '2025-03-28', '2025-04-10'),
(6, 104, '2025-04-01', '2025-04-15'),
(7, 105, '2025-02-15', '2025-03-05');

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

Hints

Similar problems

More in: SQL and Databases problems