select count(distinct trunc(block_timestamp, 'day')) as Activity, tx_from as user,

case

when Activity >= 60 then 'Active users'

when Activity >= 30 and Activity < 60 then 'Casual users'

when Activity < 30 then 'Not active users'

end as situation

from osmosis.core.fact_transactions

where block_timestamp < CURRENT_DATE

and TX_STATUS = 'SUCCEEDED'

group by 2

order by 1 desc),

active as (select user, Activity

from users

where situation = 'Active users')

select TRADER, count(distinct TX_ID) as Total, rank () over ( order by total desc) rank

from osmosis.core.fact_swaps

where TX_STATUS = 'SUCCEEDED'

and TRADER in (SELECT user from active)

and BLOCK_TIMESTAMP < CURRENT_DATE

group by 1

order by 2 DESC