If we are given a database that looks like this:
Say table name: 'tbl'
| row # | course_id | eval_type | eval_date | Passed? |
|---|---|---|---|---|
| 1 | 000 | test1 | 2020-09-01 | Y |
| 2 | 001 | test2 | 2020-10-01 | N |
| 3 | 000 | test1 | 2020-09-02 | Y |
| 4 | 000 | test1 | 2020-10-11 | Y |
| 5 | 000 | test2 | 2020-09-01 | Y |
| 6 | 001 | test1 | 2020-10-01 | Y |
How can we write a query so that we get the number of courses where we passed both evaluations, test1 and test2, on the first try? (we can do the same evaluations multiple times)
For instance, from the data above, I want my query to print 1 - since we passed test1 of course 000 on the first try (row 1), and passed test2 of course 000 on the first try (row 5). But, even though test1 of course 001 has passed on the first try (row 6), since test2 of course 001 has failed on the first try (row 2), we don't include course 001 as a count to our query result.
This is what I currently have:
SELECT course_id, eval_type, min(date)
FROM tbl
GROUP BY course_id, eval_type
Using a subquery to find the earliest date for each exam type and then finding the total number of passes:
select t.course_id from (select t1.course_id, sum(t2.passed = 'Y') r
from (select t.course_id, t.eval_type, min(t.eval_date) d from tbl t group by t.course_id, t.eval_type) t1
join tbl t2 on t1.course_id = t2.course_id and t1.eval_type = t2.eval_type and t1.d = t2.eval_date
group by t1.course_id) t where t.r = 2