select

distinct block_timestamp

,tx_from

from osmosis.core.fact_transactions

where block_timestamp between '2022-10-10' and '2022-10-16 23:59:59'

group by 2, 1

),

counting as (

select

tx_from

,case when block_timestamp ilike '%2022-10-10%' then '1' else '0' end as monday

,case when block_timestamp ilike '%2022-10-11%' then '1' else '0' end as tuesday

,case when block_timestamp ilike '%2022-10-12%' then '1' else '0' end as wednesday

,case when block_timestamp ilike '%2022-10-13%' then '1' else '0' end as thursday

,case when block_timestamp ilike '%2022-10-14%' then '1' else '0' end as friday

,case when block_timestamp ilike '%2022-10-15%' then '1' else '0' end as saturday

,case when block_timestamp ilike '%2022-10-16%' then '1' else '0' end as sunday

from dating

group by 1, 2, 3, 4, 5, 6, 7, 8

),

grouping as (

select

tx_from

,sum(monday + tuesday + wednesday + thursday + friday + saturday + sunday) as days_active

from counting

group by 1

)

select

count(tx_from)

,days_active

from grouping