MySQL Solution for Capital Gain Loss Problem
Intuition\n Describe your first thoughts on how to solve this problem. \nThe intuition behind the solution is to calculate the capital gain or loss for each sto
# Intuition\n<!-- Describe your first thoughts on how to solve this problem. -->\nThe intuition behind the solution is to calculate the capital gain or loss for each stock by summing the differences between the buying and selling prices.\n\n# Approach\n<!-- Describe your approach to solving the problem. -->\n- The query starts with the SELECT statement to retrieve the stock_name and the capital_gain_loss for each stock.\n- In the SELECT statement, we use the SUM function along with a CASE statement to calculate the capital gain/loss.\n- Inside the CASE statement, we check the value of the operation column:\n1. If the operation is \'Buy\', we negate the price (to represent a loss) because it\'s the cost of buying the stock.\n1. If the operation is \'Sell\', we use the price as it represents the gain from selling the stock.\n- The result is grouped by stock_name using the GROUP BY clause.\n- The query returns the stock_name and the corresponding capital_gain_loss for each stock in the Stocks table. The result is calculated based on the given conditions of buying and selling operations, and it matches the output provided in the example.\n\n# Complexity\n- Time complexity:\n<!-- Add your time complexity here, e.g. $$O(n)$$ -->\nThe time complexity of the solution depends on the size of the Stocks table and the indexes on the table. Assuming there are indexes on the stock_name and operation columns, the time complexity is typically O(n log n), where n is the number of rows in the table. This is because the query requires accessing and grouping the rows, which involves some logarithmic operations due to the index lookups. If the table is small or the necessary indexes are absent, the time complexity can be considered as O(n).\n\n- Space complexity:\n<!-- Add your space complexity here, e.g. $$O(n)$$ -->\nThe space complexity of the solution is O(1) because it does not require any additional space proportional to the input. The query only uses the existing table structure to calculate the capital gain/loss, so the space usage remains constant regardless of the input size.\n\n# Code\n```\n# Write your MySQL query statement below\nSELECT stock_name, SUM(\n    CASE\n        WHEN operation = \'Buy\' THEN -price\n        WHEN operation = \'Sell\' THEN price\n    END\n) AS capital_gain_loss\nFROM Stocks\nGROUP BY stock_name;\n\n```
['MySQL']