Get the students which have attempted exam in current year and at least one previous attempt in last 12 months

Question

I have a task to get the common students who have taken the exam in the current month (November 2023) and must have taken at least one attempt in last 12 months as well. So the look back period is of 12 months.

There's another condition that the student should have got at-least 20 marks in previous attempt(s).

The attempt count can be >= 1. Subject doesn't matter. Roll_number would be the joining condition.

This is the query I tried :

select 
    roll_number, substr(Exam_Date, 1, 7) yr_mon, 
    count(roll_number) as total_count 
from 
    students_results 
where 
    exam_date between '2022-11-30' and '2023-11-30'
    and marks > 20 
    and exists (select roll_number 
                from students_results
                where marks > 20
                group by roll_number
                having count(SupplierID) >= 1);

Sample data:

Roll_number	Exam Subject	Exam_Date	Marks
100	English	2023-08-01	30
100	Science	2023-09-05	50
100	English	2023-11-15	80
101	English	2021-04-01	65
101	Science	2023-06-10	45
101	English	2023-11-17	85
102	English	2021-04-01	70
102	Mathematics	2023-09-01	15
102	Mathematics	2023-11-19	60
104	English	2023-05-01	40
104	English	2023-11-03	75
105	Mathematics	2023-04-01	10
105	English	2023-11-14	80

So, we are expecting to get Roll_number : 100, 101, 104

I am trying to learn to write the self join query.

Preferred language : HiveQL/ MySQL

Any help would be appreciated.

Answer 1

Since this looks like homework

We are looking for are records

FROM sample_data

WHERE the exam date is greater than one month subtracted from the current date (https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_date-sub)

AND there EXISTS (https://www.w3schools.com/mysql/mysql_exists.asp) another record in the table for the same roll_number and subject

• WHERE the exam_date is earlier

  AND the exam_date is greater than 1 year from the current date

  AND the mark is greater than 20