241014_TIL

아무리 사소하더라도 배움이 없는 날은 없다.

SQL 문제풀이 복습

문제 링크
순차적으로 left join을 써 가면서 풀면 되는 문제.
left join을 쓴 이유는
Transaction 테이블에 내역이 없는 유저도 Output에는 반영해야 하기 때문.

1트(7/19)에서는 CTE를 만들어서 풀었지만
서브쿼리를 써서 풀어도 충분함.

SELECT a.user_id,
       a.user_name,
       Ifnull(temp1 + Sum(amount), temp1) AS "credit",
       CASE
         WHEN Ifnull(temp1 + Sum(amount), temp1) < 0 THEN 'Yes'
         ELSE 'No'
       end                                AS "credit_limit_breached"
FROM   (SELECT u.user_id,
               u.user_name,
               Ifnull(u.credit - Sum(amount), u.credit) AS "temp1"
        FROM   Users u
               LEFT JOIN Transactions t
                      ON u.user_id = t.paid_by
        GROUP  BY 1) a
       LEFT JOIN Transactions t2
              ON a.user_id = t2.paid_to
GROUP  BY 1;

문제 링크

SELECT Date_format(order_date, '%Y-%m') AS "month",
       Count(order_id)         AS "order_count",
       Count(DISTINCT customer_id)      AS "customer_count"
FROM   Orders
WHERE  invoice > 20
GROUP  BY 1;

문제 링크
왜 이렇게 했지?ㅋㅋㅋㅋ 잠이 덜 깼나

SELECT w.name                      AS "warehouse_name",
       Sum(units * volume_of_unit) AS "volume"
FROM   Warehouse w
       JOIN (SELECT product_id,
                    width * length * height AS "volume_of_unit"
             FROM   Products) a
         ON w.product_id = a.product_id
GROUP  BY 1;

그냥 1트(7/16) 때처럼

SELECT w.name                                       AS "warehouse_name",
       Sum(w.units * p.width * p.length * p.height) AS "volume"
FROM   Warehouse w
       JOIN Products p
         ON w.product_id = p.product_id
GROUP  BY 1;

이렇게만 해도 된다.
join과 집계함수를 섞어 쓰기만 하면 되는 문제.