select *

from

(

select block_timestamp as time, decoded_log['primaryAccount'] as account,

1 as init_trade, 0 as init_liquidity

from blast.core.fact_decoded_event_logs

where contract_address = lower('0x0b71cBBAd974B9DF8BDF6A83973B710AAa48e7ac')

and event_name = 'LogDepositCollateral'

)

union all

(

select block_timestamp as time, decoded_log['account'] as account,

0 as init_trade, 1 as init_liquidity

from blast.core.fact_decoded_event_logs

where contract_address = lower('0xF0D92907236418Fa8Ee900E384b4c6928f7cADfc')

and event_name = 'AddLiquidity'

)

),

unique_users as

select account,

max(init_trade) as init_trade,

max(init_liquidity) as init_liquidity

from all_users

group by 1

SELECT init_trade,

init_liquidity,

CASE WHEN init_trade = 1 AND init_liquidity = 1 THEN 'Both'

WHEN init_trade = 1 THEN 'Traders'

WHEN init_liquidity = 1 THEN 'Liquidity Providers'

END as type_,

COUNT(DISTINCT account) as users