MySQL | Multiple approaches (Aggregation, Window function, Common Table Expressions)
Aggregation (GROUP BY):\n\nSELECT stock_name, \n\t   SUM(CASE WHEN operation = \'Sell\' THEN price\n\t\t   ELSE -price END) AS capital_gain_loss\nFROM Stocks\nG
* Aggregation (`GROUP BY`):\n```\nSELECT stock_name, \n\t   SUM(CASE WHEN operation = \'Sell\' THEN price\n\t\t   ELSE -price END) AS capital_gain_loss\nFROM Stocks\nGROUP BY stock_name\n```\n\n* Window Function (`SUM OVER`):\n```\nSELECT DISTINCT stock_name, \n       SUM(CASE WHEN operation = \'Sell\' THEN price ELSE -price END)\n       OVER(PARTITION BY stock_name) AS capital_gain_loss\nFROM Stocks\n```\n\n* Common Table Expressions:\n```\nWITH TotalBought AS (\n    SELECT stock_name, SUM(price) AS spent\n    FROM Stocks\n    WHERE operation = \'Buy\'\n    GROUP BY stock_name\n),\nTotalSold AS (\n    SELECT stock_name , SUM(price) AS gained\n    FROM Stocks\n    WHERE operation = \'Sell\'\n    GROUP BY stock_name\n)\n\nSELECT B.stock_name, gained - spent AS capital_gain_loss\nFROM TotalBought AS B\nJOIN TotalSold AS S\nON B.stock_name = S.stock_name\n```\nThe last approach is the least efficient but I decided to try it just for fun.
['MySQL']