SELECT

*,

withdrawal_fee + traders_settlement + trading_fees as total_mklp_profit

FROM

(

SELECT

date_trunc('day', BLOCK_TIMESTAMP) AS day,

SUM(

CASE

WHEN event_data ['fee_type'] = '2' THEN event_data ['amount'] / 1000000

ELSE 0

END

) AS withdrawal_fee,

SUM(

CASE

WHEN event_data ['fee_type'] = '3'

AND event_data ['amount_sign'] = TRUE THEN event_data ['amount'] / 1000000 -- TRUE when amount goes to the lp

WHEN event_data ['fee_type'] = '3'

AND event_data ['amount_sign'] = FALSE THEN - event_data ['amount'] / 1000000 -- False when amount taken from the lp

ELSE 0

END

) AS traders_settlement,

SUM(

CASE

WHEN event_data ['fee_type'] = '4' THEN event_data ['amount'] / 1000000

ELSE 0

END

) AS trading_fees

FROM

aptos.core.fact_events

WHERE

event_type = '0x5ae6789dd2fec1a9ec9cccfb3acaf12e93d432f0a3a42c92fe1a9d490b7bbc06::house_lp::FeeEvent'

AND account_address = '0x5ae6789dd2fec1a9ec9cccfb3acaf12e93d432f0a3a42c92fe1a9d490b7bbc06'

AND BLOCK_TIMESTAMP :: DATE >= '2023-10-28'

GROUP BY