Find Staff Covering Every Requirement

Return the employee_id of every employee holding a valid certification for every required skill on the single report.as_of_day. valid_until is inclusive; NULL means no expiry. Certificates may repeat, including an expired and valid certificate for the same skill. Irrelevant skills do not compensate for missing required ones. If requirements is empty, all employees qualify. Order by employee_id.

Schema (all columns are NOT NULL unless explicitly nullable):

CREATE TABLE employees (employee_id INTEGER PRIMARY KEY);
CREATE TABLE requirements (skill TEXT PRIMARY KEY);
CREATE TABLE certificates (employee_id INTEGER NOT NULL, skill TEXT NOT NULL, valid_until TEXT);
CREATE TABLE report (as_of_day TEXT NOT NULL);

Write one read-only SQLite query. The tables are already populated. Return exactly the requested columns and ordering, with no headers. Empty input produces no rows unless specified otherwise.

Example setup:

INSERT INTO report VALUES ('2024-06-01'); INSERT INTO employees VALUES (1),(2); INSERT INTO requirements VALUES ('sql'),('ops'); INSERT INTO certificates VALUES (1,'sql',NULL),(1,'ops','2024-06-01'),(2,'sql',NULL);

Expected output:

1

Timebox: 9 minutes. Focus on correctness at boundaries and duplicate handling.