SELECT DISTINCT swapper, platform, block_timestamp, tx_hash

FROM aptos.defi.ez_dex_swaps

),

daus as (

SELECT

distinct sender as users,

trunc(block_timestamp,'week') as weeks,

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

from aptos.core.fact_transactions

group by 1,2

having active_days>=4

),

active_users AS (

SELECT

TRUNC(block_timestamp, 'week') AS date,

platform,

COUNT(DISTINCT swapper) AS n_users,

COUNT(DISTINCT tx_hash) AS txs

FROM nfts

GROUP BY 1, 2

SELECT

date,

platform,

n_users,

txs,

ranks

FROM (

SELECT

date,

platform,

n_users,

txs,

RANK() OVER (PARTITION BY date ORDER BY n_users DESC) AS ranks

FROM active_users