SQL문제풀기

[SQL 문제 풀이] Capital Gain/Loss

May 08, 2025

[SQL 문제 풀이] Capital Gain/Loss

Contents

내가 작성한 쿼리

Capital Gain/Loss - LeetCode

Can you solve this real interview question? Capital Gain/Loss - Table: Stocks +---------------+---------+ | Column Name | Type | +---------------+---------+ | stock_name | varchar | | operation | enum | | operation_day | int | | price | int | +---------------+---------+ (stock_name, operation_day) is the primary key (combination of columns with unique values) for this table. The operation column is an ENUM (category) of type ('Sell', 'Buy') Each row of this table indicates that the stock which has stock_name had an operation on the day operation_day with the price. It is guaranteed that each 'Sell' operation for a stock has a corresponding 'Buy' operation in a previous day. It is also guaranteed that each 'Buy' operation for a stock has a corresponding 'Sell' operation in an upcoming day. Write a solution to report the Capital gain/loss for each stock. The Capital gain/loss of a stock is the total gain or loss after buying and selling the stock one or many times. Return the result table in any order. The result format is in the following example. Example 1: Input: Stocks table: +---------------+-----------+---------------+--------+ | stock_name | operation | operation_day | price | +---------------+-----------+---------------+--------+ | Leetcode | Buy | 1 | 1000 | | Corona Masks | Buy | 2 | 10 | | Leetcode | Sell | 5 | 9000 | | Handbags | Buy | 17 | 30000 | | Corona Masks | Sell | 3 | 1010 | | Corona Masks | Buy | 4 | 1000 | | Corona Masks | Sell | 5 | 500 | | Corona Masks | Buy | 6 | 1000 | | Handbags | Sell | 29 | 7000 | | Corona Masks | Sell | 10 | 10000 | +---------------+-----------+---------------+--------+ Output: +---------------+-------------------+ | stock_name | capital_gain_loss | +---------------+-------------------+ | Corona Masks | 9500 | | Leetcode | 8000 | | Handbags | -23000 | +---------------+-------------------+ Explanation: Leetcode stock was bought at day 1 for 1000$ and was sold at day 5 for 9000$. Capital gain = 9000 - 1000 = 8000$. Handbags stock was bought at day 17 for 30000$ and was sold at day 29 for 7000$. Capital loss = 7000 - 30000 = -23000$. Corona Masks stock was bought at day 1 for 10$ and was sold at day 3 for 1010$. It was bought again at day 4 for 1000$ and was sold at day 5 for 500$. At last, it was bought at day 6 for 1000$ and was sold at day 10 for 10000$. Capital gain/loss is the sum of capital gains/losses for each ('Buy' --> 'Sell') operation = (1010 - 10) + (500 - 1000) + (10000 - 1000) = 1000 - 500 + 9000 = 9500$.

Capital Gain/Loss - LeetCode

https://leetcode.com/problems/capital-gainloss/description/

Capital Gain/Loss - LeetCode

notion image


각 주식에 대한 자본 이득/손실을 보고하는 솔루션을 작성하세요.

주식의 자본 이득/손실은 주식을 한 번 또는 여러 번 사고 팔았을 때의 총 이득 또는 손실을 말합니다.

결과 테이블을 순서에 상관없이 반환하세요.

내가 작성한 쿼리

MySQL, Oracle


-- 1. Buy 목록 정렬해서 순서 붙이기
WITH buy_list AS (
    SELECT 
        stock_name,
        price AS buy_price,
        ROW_NUMBER() OVER (PARTITION BY stock_name ORDER BY operation_day) AS rn
    FROM Stocks
    WHERE operation = 'Buy'
),

-- 2. Sell 목록 정렬해서 순서 붙이기
sell_list AS (
    SELECT 
        stock_name,
        price AS sell_price,
        ROW_NUMBER() OVER (PARTITION BY stock_name ORDER BY operation_day) AS rn
    FROM Stocks
    WHERE operation = 'Sell'
)

-- 3. 같은 stock_name + 같은 rn끼리 매칭
SELECT 
    b.stock_name,
    SUM(s.sell_price - b.buy_price) AS capital_gain_loss
FROM buy_list b
JOIN sell_list s
  ON b.stock_name = s.stock_name AND b.rn = s.rn
GROUP BY b.stock_name;

Share article

More articles

[JAVA 문제 풀이] 245. 행렬의 덧셈

[JAVA 문제 풀이] 245. 행렬의 덧셈

[JAVA 문제 풀이] 244. 문자열 다루기 기본

[JAVA 문제 풀이] 244. 문자열 다루기 기본

[SQL 문제 풀이] Rearrange Products Table

[SQL 문제 풀이] Rearrange Products Table

[JAVA 문제 풀이] 243. JadenCase 문자열 만들기

[JAVA 문제 풀이] 243. JadenCase 문자열 만들기

stupefyee

RSS·Powered by Inblog