Optimised Approach || Group By || Joins
IntuitionWe are looking to calculate the total revenue for each department by month. This query uses conditional aggregation to calculate the sum of revenue for
# Intuition
We are looking to calculate the total revenue for each department by month. This query uses conditional aggregation to calculate the sum of revenue for each month separately by checking the month value for each row.

# Approach
1. Conditional Aggregation: For each month, we use the IF function to filter rows based on the month and sum the corresponding revenue. For rows where the month does not match the specified one, NULL is used, and it is ignored in the aggregation.
2. SUM(): The SUM function is used to calculate the total revenue for each department for each month.
3. GROUP BY id: The query groups the data by id, ensuring we get monthly revenue totals for each department (id).

# Code
```mysql []
# Write your MySQL query statement below
WITH temp AS
(
SELECT *, SUM(price) AS total
FROM Stocks
GROUP BY stock_name, operation
ORDER BY operation_day
)

SELECT t1.stock_name , (t2.total - t1.total) AS capital_gain_loss
FROM temp AS t1 JOIN temp AS t2
ON t1.stock_name = t2.stock_name AND t1.operation = 'Buy' AND t2.operation = 'Sell'
```
# PLEASE UPVOTE FOR SUCH SOLUTIONS...!!
['MySQL']