(SQL) 서브쿼리 연습문제 및 풀이

ERD

1. 문제

• 각 계정에서 제품을 마케팅하는 데 가장 많이 사용한 (최댓값) 채널은 무엇입니까?
• 같은 채널이 얼마나 자주 사용되었습니까?

1) channel과 각 channel이 발생환 횟수 파악하기

SELECT a.id, a.name, we.channel, COUNT(*) ct
FROM accounts a
JOIN web_events we
ON a.id = we.account_id
GROUP BY a.id, a.name, we.channel
ORDER BY a.id;

 

2) 위의 서브쿼리를 t1으로 하고 계정 이름별 채널 집계치의 최댓값 구하기

SELECT t1.id, t1.name, MAX(ct)
FROM (SELECT a.id, a.name, we.channel, COUNT(*) ct
	FROM accounts a
    JOIN web_events we
    ON a.id = we.account_id
    GROUP BY a.id, a.name, we.channel) t1
GROUP BY t1.id, t1.name

 

3) 최댓값과 계정 이름(t2) 을 원래 테이블(t3)과 연결하기 (ct와 max가 일치하는 테이블 반환)

SELECT t3.id, t3.name, t3.channel, t3.ct
FROM (SELECT a.id, a.name, we.channel, COUNT(*) ct
    FROM accounts a
    JOIN web_events we
    ON a.id = we.account_id
    GROUP BY a.id, a.name, we.channel) t3 
JOIN (SELECT t1.id, t1.name, MAX(ct) max_chan
    FROM (SELECT a.id, a.name, we.channel, COUNT(*) ct
        FROM accounts a
        JOIN web_events we
        ON a.id = we.account_id
        GROUP BY a.id, a.name, we.channel) t1
    GROUP BY t1.id, t1.name) t2
ON t2.id = t3.id AND t2.max_chan = t3.ct
ORDER BY t3.id;