SQLPad
Sign In

200. Last week's absent students

medium snap

***********

  • *******************************************************************************************
  • ***************************************
  • ***************************************

Table: snap_test_results

Students test scores on 3 different subjects, a student can take the same subject multiple times. The exam is online and student can take any subject at any time during the two weeks from Sep 10, 2023 and Sep 23, 2023.

   col_name     | col_type
----------------+--------------------------
 student_id     | int
 subject        | varchar(30)
 score          | integer
 date           | date

Sample results 

 student_id
------------
         11
         13
          7
          5
          6
         14
          3
         17

Solution postgres

SELECT DISTINCT student_id
FROM snap_test_results
WHERE date BETWEEN '2023-09-17' AND '2023-09-23'  -- This week
AND student_id NOT IN (
    SELECT DISTINCT student_id
    FROM snap_test_results
    WHERE date BETWEEN '2023-09-10' AND '2023-09-16'  -- Last week
);

Explanation

This SQL query is designed to retrieve a list of unique (DISTINCT) student IDs from a table named snap_test_results that meet certain criteria.

The first part of the criteria is that the date of the test result must be between '2023-09-17' and '2023-09-23'. This is set by the WHERE date BETWEEN '2023-09-17' AND '2023-09-23' clause, which filters the records to only include those where the date is within this specific week.

The second part of the criteria is that the student ID must not be in a list of student IDs that had test results between '2023-09-10' and '2023-09-16'. This is set by the AND student_id NOT IN (...) clause, which further filters the records to exclude those where the student ID is in the list produced by the subquery.

The subquery (SELECT DISTINCT student_id FROM snap_test_results WHERE date BETWEEN '2023-09-10' AND '2023-09-16') generates this list of student IDs to exclude. It retrieves unique student IDs from snap_test_results where the date is between '2023-09-10' and '2023-09-16', i.e., the previous week.

In simpler terms, this query is asking: "Give me a list of students who have test results this week but did not have any test results last week."

Expected results


More Snap questions

ID Title Level FTPR
194 Daily active users report for new market snap easy
20%
195 MoM user growth snap hard
14%
196 User segments report snap hard
18%
197 Students who didn't finish all subjects snap easy
25%
198 Average score per subject snap easy
50%
199 Students improvement snap hard
67%
201 Top 3 students for each subject snap hard
67%