CODEWITHSUNDEEP
sqlmysqlself-join
Jaideep
Jaideep

Reputation: 15

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

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.

Upvotes: -2

Views: 86

Answers (1)

Error_2646
Error_2646

Reputation: 3801

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

Upvotes: 1

Related Questions