3 SOLUTIONS ( SIMPLE SUM, WDFUNCTION SUM, WDFUNCITON VALUE - WITH NOTE ) 🥶🥶🥶
Intuition\n Describe your first thoughts on how to solve this problem. \n\n# Approach\n Describe your approach to solving the problem. \n\n# Complexity\n- Time 
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\n\n# Approach\n<!-- Describe your approach to solving the problem. -->\n\n# Complexity\n- Time complexity:\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\n\n- Space complexity:\n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\n\n# SIMPLE SUM \uD83D\uDE1A\uD83D\uDE1A\uD83D\uDE1A\n```\nSELECT\n    stock_name,\n    SUM(CASE WHEN operation = \'Sell\' THEN price ELSE NULL END) - \n    SUM(CASE WHEN operation = \'Buy\' THEN price ELSE NULL END) capital_gain_loss\nFROM\n    Stocks\nGROUP BY \n    stock_name\n\n```\n\n# WINDOW FUNCTION SUM() OVER() \uD83D\uDE1A\uD83D\uDE1A\uD83D\uDE1A\n```\nSELECT \n     DISTINCT\n     stock_name,\n     SUM(CASE WHEN operation = \'Sell\' THEN price ELSE NULL END) \n        OVER(PARTITION BY Stock_name ORDER BY (SELECT NULL)) -\n     SUM(CASE WHEN operation = \'Buy\' THEN price ELSE NULL END) \n        OVER(PARTITION BY Stock_name ORDER BY (SELECT NULL)) capital_gain_loss\nFROM\n    Stocks\n```\n\n# WINDOW FUNCTION FIRST, LAST VALUE \uD83D\uDE1A\uD83D\uDE1A\uD83D\uDE1A\n```\n    SELECT DISTINCT\n    stock_name,\n    -- data from tblStockGroup \n    -- LAST_VALUE is Sell data, FIRST_VALUE is Buy data\n    -- result = LAST_VALUE - FIRST_VALUE\n    LAST_VALUE(price) OVER(PARTITION BY stock_name ORDER BY (SELECT NULL))\n    -\n    FIRST_VALUE(price) OVER( PARTITION BY stock_name ORDER BY (SELECT NULL)) capital_gain_loss\nFROM\n    (\n        SELECT\n            stock_name,\n            SUM(price) price\n            -- per stock_name has 2 rows\n            -- 1 row for sell and 1 row for Buy\n        FROM\n            Stocks\n        GROUP BY\n            stock_name,\n            operation\n    )tblStockGroup;\n\n```
['MySQL', 'MS SQL Server']