SELECT

*,

dense_rank() OVER (

PARTITION BY blockchain

ORDER BY

date_part('epoch', date) / (30 * 24 * 60 * 60)

) as month_rank,

dense_rank() OVER (

PARTITION BY blockchain

ORDER BY

date_trunc('day', date)

) as day_rank,

dense_rank() OVER (

PARTITION BY blockchain

ORDER BY

date_part('epoch', date) / (7 * 24 * 60 * 60)

) as week_rank

FROM

(

SELECT

date_trunc('{{time_frame}}', block_Timestamp) as date,

'Blast' as blockchain,

count(DISTINCT from_address) as "# of New Wallets"

FROM

blast.core.fact_transactions

where

(from_address, block_Timestamp) in (

SELECT

from_address,

min(block_Timestamp)

from

blast.core.fact_transactions

group by

1

)