--First query forked from adriaparcerisas / flow Daily Active User @ https://flipsidecrypto.xyz/adriaparcerisas/q/osmosis-daily-active-user-1wkODP

daus as (

SELECT distinct

proposer as users,

trunc(block_timestamp, 'week') as weeks,

count(distinct trunc(block_timestamp, 'day')) as active_days

from

flow.core.fact_transactions

group by

1,

2

having

active_days >= 4

),

active_users as (

SELECT

case

when proposer in (

select

users

from

daus

) then 'DAU'

else 'Other'

end as

type,

count(distinct proposer) as n_users,

count(distinct tx_id) as txs,

txs / n_users as tx_per_user

from

flow.core.fact_transactions

group by

1