SELECT

receiver as user,

'IBC_TRANSFER_IN' as user_type,

min(date_trunc('day', block_timestamp)) as min_day

FROM cosmos.core.fact_transfers

WHERE currency LIKE 'uatom'

AND TRANSFER_TYPE LIKE 'IBC_TRANSFER_IN'

GROUP BY 1,2

), tab2 as (

SELECT

sender as user,

'IBC_TRANSFER_OUT' as user_type,

min(date_trunc('day', block_timestamp)) as min_day

FROM cosmos.core.fact_transfers

WHERE currency LIKE 'uatom'

AND TRANSFER_TYPE LIKE 'IBC_TRANSFER_OUT'

GROUP BY 1,2

), tab3 as (

SELECT

sender as user,

'COSMOS' as user_type,

min(date_trunc('day', block_timestamp)) as min_day

FROM cosmos.core.fact_transfers

WHERE currency LIKE 'uatom'

AND TRANSFER_TYPE LIKE 'COSMOS'

GROUP BY 1,2

SELECT

user_type,

max(total_users)

FROM (

SELECT

*,

sum(new_users) over (partition by user_type ORDER by min_day) as total_users