daus as (

SELECT

distinct from_address as users,

trunc(block_timestamp,'week') as weeks,

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

from avalanche.core.fact_transactions

group by 1,2

having active_days>=4

),

active_users as (

SELECT

trunc(x.block_timestamp,'week') as date,

contract_address,

count(distinct from_address) as n_user,

count(distinct x.tx_hash) as txs

from avalanche.core.fact_transactions x

join avalanche.core.fact_decoded_event_logs y on x.tx_hash=y.tx_hash

where from_address in (select users from daus)

group by 1,2

),

final as (

select

date,

name,

sum(n_user) as n_users,

sum(txs) as txs,

rank() over (partition by date order by n_users desc) as ranks

from active_users x

join avalanche.core.dim_contracts y on x.contract_address=y.address

where date>=current_date-interval '{{Months}} MONTHS'

group by 1,2

order by 1 asc

)

date,