Correct way to write this view? Total sales/purchases/profits by day

Question

I'm currently trying to create a view that lists a sum of sales, purchases and profits done by each person per day. This data comes from 3 tables:

transaction:
idtransaction (pk)
user_iduser (fk)
item_iditem (fk)
total_price (bigint)
time(datetime)
transaction_type (enum: 'buy', 'sell')

profit:
idprofit (pk)
transaction_idtransaction_bought (fk)
transaction_idtransaction_sold (fk)
quantity_profit (int)
profit_unit (bigint)

calendar:
days (date)

Calendar table is a simple table created from a stored procedure. It consists on every date between 2000-2050. Profits are already calculated from the application side, the profits table simply stores the results. Note that different users MAY participate in the same 'profit', (e.x user 200 buys an item and user 201 sells it. Again this is handled by the application side). In this case you'd increment the value of sales and profit to user 201 and the value of purchases to user 200.

I'm trying to create a view that outputs this (a snapshot of sums for each day for each user)

date      userid  total_bought  total_sold  total_profit  profit_margin
01-01-2016   200   150000       2000000      50000         0.20
02-01-2016   200   512          14515        456           0.05
03-01-2016   200   150000       1000000      5000          0.14
04-01-2016   200   512          12515        4556          0.07

and so on (do the same for every user and date, so in total the number of rows should be users x dates)

However my implementation is outputting wrong data.

create view v_history_details as

select calendar.days as hday, 
users.iduser as user_id, 
sum(t1.price_total) as total_buy
sum(profit.profit_unit*profit.quantity_profit) as total_profit,
(sum(total_profit)/sum(total_buy)) * 100 AS margin

from
calendar

JOIN transaction t1 ON date(t1.time) = calendar.days
JOIN transaction t2 ON date(t2.time) = calendar.days


JOIN users on t1.iduser = users.iduser

where t1.transaction_type = 'Buy'
and t2.transaction_type = 'Sell'

GROUP BY calendar.days

order by calendar.days ASC

The issue comes from joining with the transaction table twice. I need a way to split the purchases and sales data but at the same time I also need them in the same view. I'm kind of new to all this

I'd really appreciate some guidance on how to structure this view here.

Answer 1

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

Answer 2

There's no join to the profit table?

You need conditional aggregates, i.e. join to transaction only once and all t1.column are replaced by

CASE WHEN transaction_type = 'Buy' THEN column END

Similar for t2.column:

CASE WHEN transaction_type = 'Sell' THEN column END