[LeetCode/SQL] 1393. Capital Gain/Loss

[LeetCode/SQL]

---

📌 1393. Capital Gain/Loss

문제

풀이

주식의 매수 및 매도 거래를 확인하고, 
각 거래에 대한 이익/손실을 계산한 다음 주식별로 합산하여 출력하라 

방법 I

(WITH 문)

WITH loss AS (
    SELECT stock_name 
           ,SUM(price) AS loss
    FROM Stocks
    WHERE operation ='Buy'
    GROUP BY stock_name
),
gain AS (
    SELECT stock_name
           ,SUM(price) AS gain
    FROM Stocks
    WHERE operation='Sell'
    GROUP BY stock_name
)

SELECT loss.stock_name
       ,gain.gain -loss.loss AS capital_gain_loss
    

FROM loss
INNER JOIN gain ON loss.stock_name=gain.stock_name
GROUP BY loss.stock_name

방법 II

(집계)

SELECT stock_name
      SUM(CASE WHEN operation='Sell' THEN price ELSE -price END) AS capital_gain_loss
FROM Stocks
GROUP BY stock_name

방법 III

(window 함수)

SELECT DISTINCT stock_name
      ,SUM(CASE WHEN operation='Sell' THEN price ELSE -price END)
      OVER (PARTITION BY stock_name) AS capital_gain_loss
FROM Stocks