WITH opt_cnt AS(SELECT block_id,COUNT(*)AS n
FROM option_keys
GROUPBY block_id
),big AS(SELECT*FROM opt_cnt WHERE n >=3-- 앞 CTE를 참조할 수 있다)SELECT b.block_id, b.name, big.n
FROM blocks b
JOIN big ON big.block_id = b.block_id
ORDERBY big.n DESC;
WITH RECURSIVE chainAS(SELECT block_id, depends_on,1AS depth -- 시작점 (앵커)FROM block_deps WHERE block_id ='TSM0004'UNIONALLSELECT d.block_id, d.depends_on, c.depth +1-- 반복 부분FROM block_deps d
JOINchain c ON d.block_id = c.depends_on
WHERE c.depth <10-- 안전장치: 순환 방지)SELECT*FROMchain;
함수()OVER(PARTITIONBY 그룹기준 -- 없으면 전체가 한 그룹ORDERBY 정렬기준 -- 순위·누적에 필요ROWSBETWEEN...AND...-- 프레임 (이동 평균 등))
WITH ranked AS(SELECT block_id, category, score, ROW_NUMBER()OVER(PARTITIONBY category ORDERBY score DESC)AS rn
FROM blocks
)SELECT*FROM ranked WHERE rn <=3;-- 카테고리별 상위 3개