Correct way to write this view? Total sales/purchases/profits by day
I ended up doing it this way:

SELECT temp1.hday, 
temp1.user_id, 
temp1.total_buy, 
temp1.total_sell, 
coalesce(temp2.total_profit,0) as total_profit,
coalesce(temp3.margin,0) as margin
from

(
select 
calendar.days as hday, 
users.iduser as user_id, 
coalesce(sum(CASE WHEN transaction.transaction_type = 'Buy' THEN transaction.price_total END),0) as total_buy,
coalesce(sum(CASE WHEN transaction.transaction_type = 'Sell' THEN transaction.price_total END),0) as total_sell
-- (total_profit/total_buy) * 100 AS margin
from
calendar
JOIN transaction ON date(transaction.time) = calendar.days
JOIN users on transaction.user_iduser = users.iduser
GROUP BY calendar.days, users.iduser
order by calendar.days ASC
) temp1

left JOIN

(
select calendar.days as hday, 
users.iduser as user_id,
coalesce(sum(profit.profit_unit*profit.quantity_profit),0) as total_profit
from calendar
JOIN profit on date(profit.timestamp_sell) = calendar.days
JOIN users on  profit.users_idusers_OUT = users.iduser
GROUP BY calendar.days, users.iduser
order by calendar.days ASC
) temp2

ON temp1.hday = temp2.hday

left join

(
select (sum(profit.profit_unit*profit.quantity_profit)/sum(t1.price_unit*profit.quantity_profit))*100 as margin, calendar.days as hday
                from profit
                join transaction t1 on profit.transaction_idbuy_buy = t1.idbuy
                join transaction t2 on profit.transaction_idbuy_sell = t2.idbuy
                join users on t2.user_iduser = users.iduser
                join calendar on calendar.days = date(profit.timestamp_sell)

                GROUP BY calendar.days, users.iduser

) temp3

on temp2.hday = temp3.hday


I had to create 2 temporary tables. The first gathers information for purchases and sales with conditional aggregates while the second one contains the profit information. profit margin comes from merging again with a 3rd table.

There's probably easier ways of doing this, though