(SQL) WITH 서브쿼리 연습문제

ERD

예제) 각 channel의 하루 평균 이벤트 수

• 먼저, DATE_TRUNC를 이용해 날짜별로 그룹을 만들어줍니다.
• DATE_TRUNC개념 🔻

(SQL) DATE_TRUNC DATE_PART 개념 및 연습문제

SELECT DATE_TRUNC('day', occurred_at) AS day, channel, COUNT(*) AS events
FROM web_events
GROUP BY 1,2

• 이 쿼리를 WITH 문에 넣습니다. 별칭을 'events'로 지정합니다.

WITH events AS (SELECT DATE_TRUNC('day', occurred_at) AS day, channel, COUNT(*) AS events
		FROM web_events
                GROUP BY 1,2)

• 새로 생성된 'events' 테이블을 데이터베이스의 다른 테이블처럼 사용할 수 있습니다.
• 각 channel 별 평균을 구하면 아래와 같습니다.

WITH events AS (SELECT DATE_TRUNC('day', occurred_at) AS day, channel, COUNT(*) AS events
		FROM web_events
    		GROUP BY 1,2)
    
SELECT channel, AVG(events) AS average_events
FROM events
GROUP BY channel
ORDER BY 2 DESC;

 

 

🔻지난 글🔻에서 풀었던 서브쿼리 문제를 WITH 문으로 모두 다시 풀어보겠습니다.

 

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

 

Q1) total_amt_usd 판매액이 가장 많은 각 region의 sales_rep의 name은?

WITH t1 AS(
	SELECT s.name rep_name, r.name region_name, SUM(o.total_amt_usd) total_amt
        FROM sales_reps s
        JOIN accounts a
        ON a.sales_rep_id = s.id
        JOIN orders o
        ON o.account_id = a.id
        JOIN region r
        ON r.id = s.region_id
        GROUP BY 1,2
        ORDER BY 3 DESC),
    t2 AS(
        SELECT region_name, MAX(total_amt) total_amt
        FROM t1
        GROUP BY 1)
SELECT t1.rep_name, t1.region_name, t1.total_amt
FROM t1
JOIN t2
ON t1.region_name = t2.region_name AND t1.total_amt = t2.total_amt;

 

Q2) total_amt_usd 매출이 가장 높은 지역의 경우 total 주문이 몇 개나 됩니까?

WITH t1 AS(
	SELECT r.name region_name, SUM(o.total_amt_usd) total_amt
        FROM sales_reps s
        JOIN accounts a
        ON a.sales_rep_id = s.id
        JOIN orders o
        ON o.account_id = a.id
        JOIN region r
        ON r.id = s.region_id
        GROUP BY 1),
     t2 AS(
    	SELECT MAX(total_amt)
        FROM t1)
SELECT r.name, COUNT(o.total) total_orders
FROM sales_reps s
JOIN accounts a 
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id
JOIN region r
ON r.id = s.region_id
GROUP BY 1
HAVING SUM(o.total_amt_usd) = (SELECT * FROM t2);

Q3) 몇 개의 계정이 고객으로서 평생 동안 가장 많은 standard_qty 용지를 구매한 name 계정보다 total 구매가 더 많았습니까?

WITH t1 AS (
        SELECT a.name account_name, SUM(o.standard_qty) total_std, SUM(o.total) total
        FROM accounts a
        JOIN orders o
        ON o.account_id = a.id
        GROUP BY 1
        ORDER BY 2 DESC
        LIMIT 1),
    t2 AS (
   	SELECT a.name
        FROM orders o
        JOIN accounts a
        ON a.id = o.account_id
        GROUP BY 1 
        HAVING SUM(o.total) > (SELECT total FROM t1))
SELECT COUNT(*)
FROM t2;

Q4) 평생 동안 가장 많은 시간을 소비한 고객 total_amt_usd의 경우 각 채널에 대해 web_events가 몇 개 있었습니까?

WITH t1 AS (
    SELECT a.id, a.name, SUM(o.total_amt_usd) tot_spent
    FROM orders o
    JOIN accounts a
    ON a.id = o.account_id
    GROUP BY a.id, a.name
    ORDER BY 3 DESC
    LIMIT 1)
SELECT a.name, w.channel, COUNT(*)
FROM accounts a
JOIN web_events w
ON a.id = w.account_id AND a.id = (SELECT id FROM t1)
GROUP BY 1, 2
ORDER by 3 DESC;

Q5) 상위 10개 총 지출 accounts에 대해 total_amt_usd로 환산한 평생 평균 금액은 얼마입니까?

WITH t1 AS (
    SELECT a.id, a.name, SUM(o.total_amt_usd) tot_spent
    FROM orders o
    JOIN accounts a
    ON a.id = o.account_id
    GROUP BY a.id, a.name
    ORDER BY 3 DESC
    LIMIT 10)
SELECT AVG(tot_spent)
FROM t1;

Q6) 모든 주문의 평균보다 평균적으로 주문당 더 많은 비용을 지출한 회사를 포함하여 total_amt_usd 측면에서 지출한 평생 평균 금액은 얼마입니까?

WITH t1 AS (
    SELECT AVG(o.total_amt_usd) avg_all
    FROM orders o
    JOIN accounts a
    ON a.id = o.account_id),
    t2 AS (
    SELECT o.account_id, AVG(o.total_amt_usd) avg_amt
    FROM orders o
    GROUP BY 1
    HAVING AVG(o.total_amt_usd) > (SELECT * FROM t1))
SELECT AVG(avg_amt) 
FROM t2