240719_TIL

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

SQL 코드카타

문제 링크

WITH rate
     AS (SELECT company_id,
                Max(salary) AS "max_salary",
                CASE
                  WHEN Max(salary) < 1000 THEN 0
                  WHEN Max(salary) BETWEEN 1000 AND 10000 THEN 0.24
                  WHEN Max(salary) > 10000 THEN 0.49
                END         AS "tax_rate"
         FROM   salaries
         GROUP  BY 1)
SELECT s.company_id,
       s.employee_id,
       s.employee_name,
       Round(s.salary * ( 1 - r.tax_rate )) AS "salary"
FROM   salaries s
       LEFT JOIN rate r
              ON s.company_id = r.company_id;

문제 링크
피벗테이블을 만드는 난이도 hard 문제.
지난 주 금요일에 풀었던 코드카타도 참고했다.

CTE를 몇 개 만들어서 카테고리별로 sum을 하는 등
연산에 필요한 준비물들은 최대한 final_result 안에 몰아놓고,
피벗테이블은 final_result에 있는 정보로만 만들었다.

이 때 상품 카테고리가 몇 개가 있을지 모르기 때문에
case when 구문에 'Book' 등으로 카테고리명을 명시하면
오류가 생기게 된다는 점에 주의.

WITH result
     AS (SELECT o.order_date,
                Dayofweek(o.order_date) AS "dayofweek",
                o.item_id,
                i.item_category,
                o.quantity
         FROM   orders o
                INNER JOIN items i
                        ON o.item_id = i.item_id),
     unique_category
     AS (SELECT DISTINCT item_category AS "Category"
         FROM   items),
     final_result
     AS (SELECT uc.category,
                Ifnull(dayofweek, 0)     AS "dayofweek2",
                Ifnull(Sum(quantity), 0) AS "amount"
         FROM   unique_category uc
                LEFT JOIN result r
                       ON uc.category = r.item_category
         GROUP  BY 1,
                   2)
SELECT category AS "CATEGORY",
       Max(CASE
             WHEN dayofweek2 = '2' THEN amount
             ELSE 0
           END) AS "MONDAY",
       Max(CASE
             WHEN dayofweek2 = '3' THEN amount
             ELSE 0
           END) AS "TUESDAY",
       Max(CASE
             WHEN dayofweek2 = '4' THEN amount
             ELSE 0
           END) AS "WEDNESDAY",
       Max(CASE
             WHEN dayofweek2 = '5' THEN amount
             ELSE 0
           END) AS "THURSDAY",
       Max(CASE
             WHEN dayofweek2 = '6' THEN amount
             ELSE 0
           END) AS "FRIDAY",
       Max(CASE
             WHEN dayofweek2 = '7' THEN amount
             ELSE 0
           END) AS "SATURDAY",
       Max(CASE
             WHEN dayofweek2 = '1' THEN amount
             ELSE 0
           END) AS "SUNDAY"
FROM   final_result
GROUP  BY 1
ORDER  BY 1;

문제 링크
MySQL에서 공백 제거할 때는 trim, 소문자로 변환할 때는 lower.

SELECT Trim(Lower(product_name))       AS "product_name",
       Date_format(sale_date, '%Y-%m') AS "sale_date",
       Count(sale_id)                  AS "total"
FROM   sales
GROUP  BY 1,
          2
ORDER  BY 1,
          2;

문제 링크

WITH result
     AS (SELECT paid_by      AS "user_id",
                -Sum(amount) AS "minus"
         FROM   transactions
         GROUP  BY 1
         UNION
         SELECT paid_to     AS "user_id",
                Sum(amount) AS "plus"
         FROM   transactions
         GROUP  BY 1),
     balance
     AS (SELECT user_id,
                Sum(minus) AS "balance"
         FROM   result
         GROUP  BY 1)
SELECT u.user_id,
       u.user_name,
       Ifnull(u.credit + balance, u.credit) AS "credit",
       CASE
         WHEN Ifnull(u.credit + balance, u.credit) > 0 THEN "No"
         ELSE "Yes"
       END                                  AS "credit_limit_breached"
FROM   users u
       LEFT JOIN balance b
              ON u.user_id = b.user_id;

문제 링크

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