240702_TIL

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

SQL 코드카타
leetcode의 무료 문제는 다음 주가 오기 전에 다 풀 수 있을 것 같다.
이제 진짜 유료결제를 고민해야 할 시점🤔

문제 링크

SELECT id,
       Sum(IF(month = 'Jan', revenue, NULL)) AS Jan_Revenue,
       Sum(IF(month = 'Feb', revenue, NULL)) AS Feb_Revenue,
       Sum(IF(month = 'Mar', revenue, NULL)) AS Mar_Revenue,
       Sum(IF(month = 'Apr', revenue, NULL)) AS Apr_Revenue,
       Sum(IF(month = 'May', revenue, NULL)) AS May_Revenue,
       Sum(IF(month = 'Jun', revenue, NULL)) AS Jun_Revenue,
       Sum(IF(month = 'Jul', revenue, NULL)) AS Jul_Revenue,
       Sum(IF(month = 'Aug', revenue, NULL)) AS Aug_Revenue,
       Sum(IF(month = 'Sep', revenue, NULL)) AS Sep_Revenue,
       Sum(IF(month = 'Oct', revenue, NULL)) AS Oct_Revenue,
       Sum(IF(month = 'Nov', revenue, NULL)) AS Nov_Revenue,
       Sum(IF(month = 'Dec', revenue, NULL)) AS Dec_Revenue
FROM   department
GROUP  BY 1;

문제 링크

WITH sell_position
     AS (SELECT stock_name,
                Sum(price) AS gain
         FROM   stocks
         WHERE  operation = 'Sell'
         GROUP  BY 1),
     buy_position
     AS (SELECT stock_name,
                Sum(price) AS loss
         FROM   stocks
         WHERE  operation = 'Buy'
         GROUP  BY 1)
SELECT s.stock_name,
       gain - loss AS "capital_gain_loss"
FROM   sell_position s
       INNER JOIN buy_position b
               ON s.stock_name = b.stock_name
GROUP  BY 1;

문제 링크

SELECT u.name,
       Ifnull(Sum(r.distance), 0) AS travelled_distance
FROM   users u
       LEFT JOIN rides r
              ON u.id = r.user_id
GROUP  BY u.name,
          u.id
ORDER  BY 2 DESC,
          1 ASC;

출력 시에는 name만 뜨면 되지만 id가 다른 동명이인을 거르기 위해
group by를 수행할 때는 u.id까지 조건에 포함시켰다.

문제 링크

SELECT u.name,
       Sum(t.amount) AS balance
FROM   transactions t
       INNER JOIN users u
               ON t.account = u.account
GROUP  BY 1
HAVING balance > 10000;

문제 링크

SELECT date_id,
       make_name,
       Count(DISTINCT lead_id)    AS "unique_leads",
       Count(DISTINCT partner_id) AS "unique_partners"
FROM   dailysales
GROUP  BY 1,
          2;