题解 | 平均绩点大于3.6且总人数大于2的学校里学生情况

WITH university_name AS(
    SELECT university
    FROM user_profile
    GROUP BY university
    HAVING AVG(gpa)>3.6
    AND COUNT(id)>2
),
university_total AS(
    SELECT university,COUNT(id) total_cnt
    FROM user_profile
    WHERE university IN (SELECT university
                            FROM university_name)
    GROUP BY university
),
university_male AS(
    SELECT university,COUNT(id) male_cnt
    FROM user_profile
    WHERE university IN (SELECT university
                            FROM university_name)
    AND gender = 'male'
    GROUP BY university
),
university_female AS(
    SELECT university,COUNT(id) female_cnt
    FROM user_profile
    WHERE university IN (SELECT university
                            FROM university_name)
    AND gender = 'female'
    GROUP BY university
),
university_lt23 AS(
    SELECT university,COUNT(id) lt23_cnt
    FROM user_profile
    WHERE university IN (SELECT university
                            FROM university_name)
    AND age < 23
    GROUP BY university
)

SELECT t1.university,t1.total_cnt,t2.male_cnt,t3.female_cnt,t4.lt23_cnt
FROM university_total t1
INNER JOIN university_male t2
ON t1.university = t2.university
INNER JOIN university_female t3
ON t2.university = t3.university
INNER JOIN university_lt23 t4
ON t3.university = t4.university