Tutor taking atleast 3 classes for a student

Tutor data is provided tutor_students table below. Table contains data of a month with tutor_id and student_id along with date of month on which tutor took class.
Write a SQL query that provides the pairs (tutor_id, student_id) where the tutor has taken at least 3 classes for a student in single month.


 

Input Table: tutor_students

tutor_id student_id month_date
1 1 1
1 1 2
1 2 3
1 2 4
2 1 5
2 1 6
1 1 7

Output Table:

tutor_id student_id
1 1

Explanation:

In above table tutor_id=1 has taught student_id=1 more than 3 times in single month. Hence, in output we have single pair.


DDLs:

CREATE TABLE IF NOT EXISTS tutor_students (
    tutor_id   INT,
    student_id INT,
    month_date INT
);
INSERT INTO tutor_students VALUES (1, 1, 1);
INSERT INTO tutor_students VALUES (1, 1, 2);
INSERT INTO tutor_students VALUES (1, 2, 3);
INSERT INTO tutor_students VALUES (1, 2, 4);
INSERT INTO tutor_students VALUES (2, 1, 5);
INSERT INTO tutor_students VALUES (2, 1, 6);
INSERT INTO tutor_students VALUES (1, 1, 7);

Solution:

SELECT 
    tutor_id, 
    student_id
FROM
    tutor_students
GROUP BY 
tutor_id , student_id
HAVING COUNT(*) >= 3
;

 

Leave a Comment