select tx_hash,

case

when ORIGIN_FUNCTION_SIGNATURE='0xb95cac28' then 'Join'

else 'Exit' end as action

from polygon.core.ez_decoded_event_logs

where CONTRACT_ADDRESS='0x97469e6236bd467cd147065f77752b00efadce8a'

and ORIGIN_FUNCTION_SIGNATURE in ('0xb95cac28','0x8bdb3913','0x3bd9ef28')

and TX_STATUS='SUCCESS'

and BLOCK_TIMESTAMP>='2022-01-01'

),

ECLP_TUSD_USDC_Join_Exit as (

select a.BLOCK_TIMESTAMP, action, a.AMOUNT as USDC_AMOUNT, b.AMOUNT as TUSD_AMOUNT,

ifnull(sum(c.amount),0) as ECLP_AMOUNT_1,

ifnull(sum(-1*d.amount),0) as ECLP_AMOUNT_2,

ECLP_AMOUNT_1+ECLP_AMOUNT_2 as ECLP_TUSD_USDC_AMOUNT,

case when action='Join' then a.FROM_ADDRESS else a.TO_ADDRESS end as USER,

a.tx_hash

from polygon.core.ez_token_transfers a

join polygon.core.ez_token_transfers b

on a.tx_hash=b.tx_hash

left join polygon.core.ez_token_transfers c

on a.tx_hash=c.tx_hash and c.symbol='ECLP-TUSD-USDC'

and c.FROM_ADDRESS='0x0000000000000000000000000000000000000000'

left join polygon.core.ez_token_transfers d

on a.tx_hash=d.tx_hash and d.symbol='ECLP-TUSD-USDC'

and d.TO_ADDRESS='0x0000000000000000000000000000000000000000'

join ECLP_TUSD_USDC_actions x

on a.tx_hash=x.tx_hash

and a.BLOCK_TIMESTAMP>='2022-01-01'

and a.symbol='USDC' and b.symbol='TUSD'

and action in ('Join','Exit')

group by 1,2,3,4,8,9)

select date_trunc({{time_interval}}, BLOCK_TIMESTAMP) as date,

sum(case when action='Join' then 1 else 0 end) as "Joins",