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 ;