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

ERD

1. 문제

1) total_amt_usd 판매액이 가장 많은 각 region에서 sales_rep의 name을 제공하십시오.

• sales_rep과 관련된 total_amt_usd 총계, region 나타내기

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;

 

• 각 region에 대한 total_amt_usd 가져오기

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

• 정답은, 지역과 금액이 일치하는 두 테이블의 JOIN으로 구할 수 있습니다.

SELECT t3.rep_name, t3,region_name, t3.total_amt
FROM(SELECT region_name, MAX(total_amt) total_amt
	FROM(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) t1
	GROUP BY 1) t2
JOIN(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) t3
ON t3.region_name = t2.region_name AND t3.total_amt = t2.total_amt;

 

 

2) total_amt_usd 판매액이 가장 많은(합계) 지역에 대해 total(개수) 주문은 얼마입니까?

• 각 region에 대한 total_amt_usd 가져오기

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 r.name

• 서브쿼리 이용하여 최댓값 가져오기

SELECT MAX(total_amt)   
FROM(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 r.name) sub;

• 해당 region의 총 주문 가져오기

SELECT r.name, COUNT(o.total) total_orders
FROM sales_reps s
JOIN account 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 r.name
HAVING SUM(o.total_amt_usd) = 
	(SELECT MAX(total_amt)
    	FROM (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 r.name) sub);

 

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

• standard_qty 용지가 가장 많은 계정과 총 개수 가져오기

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;

• 총 매출이 더 많은 모든 계정 가져오기

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 (SELECT a.name act_name, SUM(o.standard_qty) tot_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) sub);

• 개수 구하기

SELECT COUNT(*)
FROM (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 (SELECT a.name act_name, SUM(o.standard_qty) tot_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) inner_tab)
) counter_tab;

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

• 가장 많이 소비한 고객 찾기

SELECT a.id, a.name SUM(o.total_amt_usd) tot_spent
FROM order o
JOIN account a
ON a.id = o.account_id
GROUP BY a.id, a.name
ORDER BY 3 DESC
LIMIT 1;

• 이 회사의 각 채널에서 id와 일치시킬 수 있는 이벤트의 수 찾기

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 (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) inner_table)
GROUP BY 1,2
ORDER BY 3 DESC;

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

• total_amt_usd가 가장 높은 상위 10개 계정 찾기

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;

• 10개의 평균 금액 구하기

SELECT AVG(tot_spent)
FROM (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) temp;

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

• total_amt_usd 에서 모든 계정의 평균 구하기

SELECT AVG(o.total_amt_usd) avg_all
FROM orders o

• 구한 이 금액보다 많은 계정만 가져오기

SELECT o.account_id, AVG(o.total_amt_usd)
FROM orders o
GROUP BY 1
HAVING AVG(o.total_amt_usd) > (SELECT
	AVG(o.total_amt_usd) avg_all
        FROM orders o);

• 이 값들의 평균 구하기

SELECT AVG(avg_amt)
FROM (SELECT o.account_id, AVG(o.total_amt_usd) avg_amt
    FROM orders o
    GROUP BY 1
    HAVING AVG(o.total_amt_usd) > (SELECT AVG(o.total_amt_usd) avg_all
  	  FROM orders o)) temp_table;