[PostgreSQL] 가장 최근 날짜, 점수별 건수, 점수 평균 구하기

예시 테이블 구조 ( 테이블명: scores )

학생이름 (컬럼명: student_name /  타입: varchar)	점수 (컬럼명: score / 타입: smallint)	거래일자 (컬럼명: exam_date /  타입: date)
홍길동	90	2023-04-28

 

최근날짜 기준 1~50 건수, 50 ~ 100 건수, 점수 평균

WITH count_scores AS (
    SELECT
        COUNT(CASE WHEN score > 1 AND total_score <= 50 THEN id END) AS a1b50,
        COUNT(CASE WHEN score > 50 AND total_score <= 100 THEN id END) AS a50b100,
        FLOOR(AVG(score)) AS average
    FROM scores
    WHERE (student_name, exam_date)
    IN (
        SELECT student_name, MAX(exam_date) AS exam_date
        FROM scores
        GROUP BY student_name
    )
)

SELECT * FROM count_scores;