SQL: Counting the Number of Distinct Schools a Student Attended

I am working with Netezza SQL.

I have the following table (my_table) of student GPA over different years and what school they attended:

    student_id year gpa school_year
1           1 2010   6    School A
2           1 2011   4    School B
3           1 2012   4    School A
4           2 2010   5    School A
5           2 2011   1    School B
6           2 2012   2    School B
7           3 2009   5    School A
8           3 2010   6    School A
9           3 2011   5    School A
10          4 2010   5    School A
11          4 2011   7    School B
12          4 2014   6    School C
13          5 2010   4    School C
14          5 2011   2    School A
15          5 2012   2    School A

My question: I want to find out:

• For any student that attended "School A" in 2010 and had a GPA > 5
• Between the years 2010-2015, how many distinct schools did these students attend?

Here is my attempt to write a query for this problem - I first identified a subset of all rows between 2010 and 2015, then I "tagged" students who attended School A in 2010 and had a GPA > 5. Finally, I used a join to bring all this together - and a two layered count aggregation to get the final answer:

with cte_a as
(select * from my_table where school_year >= 2010 and school_year<=2015),

cte_b as
(select distinct student_id from cte_a
where school = 'School A' and gpa>5 and school_year = 2010)

select count_1, count(student_id) from(
select t.student_id, count(distinct school) as count_1
from my_table t
join cte_b
on t.student_id = cte_b.student_id
group by t.student_id)a
group by count_1;

I am confused if in the last chunk of code, do I need to use cte_b or my_table.

Can someone please show me how to do this correctly? In the end, I am expecting the final answer in this format:

  count_distinct_schools      count(student_id)
       2                           1
       1                           1

Thanks!

I am not sure why you need double aggregation, but shouldn't this work?

SELECT t.student_id, COUNT(DISTINCT school)
FROM my_table t
JOIN (
	SELECT DISTINCT student_id
	FROM my_table
	WHERE school = 'School A' AND gpa > 5 AND year = 2010
) s ON s.student_id = t.student_id
WHERE t.year BETWEEN 2010 AND 2015
GROUP BY t.student_id

At first we find students that attended "School A" in 2010 and had a GPA > 5 using CTE_Students.

Then in the main table find all rows with the found student IDs and group them and count the schools.

WITH
CTE_Students
AS
(
	SELECT student_id
	FROM my_table
	WHERE
		school = 'School A' 
		and gpa > 5 
		and school_year = 2010
)
SELECT
	my_table.student_id
	,COUNT(DISTINCT school) AS SchoolCount
FROM
	my_table
WHERE
	my_table.student_id IN (SELECT CTE_Students.student_id FROM CTE_Students)
    AND my_table.year >= 2010
    AND my_table.year <= 2015
GROUP BY
	my_table.student_id
;

If you use IN instead of JOIN, then it is not necessary to get DISTINCT list of student IDs in the first CTE.

Actually, it doesn't matter. Even if you join my_table with the CTE_Students and CTE_Students has several rows with the same student ID, in the end the count of schools is DISTINCT, so all possible duplicates will be eliminated.

So, there is no need to have DISTINCT when finding students that attended "School A" in 2010 and had a GPA > 5, the DISTINCT in the COUNT() will take care of it.

I think this should work.

;WITH cte
AS (
	SELECT *
	FROM my_table s
	WHERE s.school = 'school_a'
		AND s.school_year = 2010
		AND s.gpa > 5.0
	)
SELECT s.student_id
	,count(DISTINCT s.school) AS NumOfDistinctSchools
FROM my_table s
WHERE s.student_id IN (
		SELECT t.student_id
		FROM cte t
		)
	AND s.school_year <= 2015
	AND s.school_year >= 2010
GROUP BY s.student_id