-- forked from Near Top Projects - Growth with Baseline May @ https://flipsidecrypto.xyz/edit/queries/f97d7ee4-bb80-4862-b629-2552b3b05008

-- forked from Near Top Projects - Growth with Baseline @ https://flipsidecrypto.xyz/edit/queries/221d7a67-446b-4722-8957-58b9e39c0ba0

-- forked from Near project types AVG WAU @ https://flipsidecrypto.xyz/edit/queries/a1d38b6a-97ae-48ed-9433-2bdb2d87c643

-- forked from Near project types daily @ https://flipsidecrypto.xyz/edit/queries/9c76fe8e-a134-4967-8560-49ebee6aa86d

-- forked from Near project types @ https://flipsidecrypto.xyz/edit/queries/8b0a42ee-4f08-46bd-9fd3-96458fa33676

-- forked from Near Top Projects 2023 @ https://flipsidecrypto.xyz/edit/queries/c61278e0-c6a4-4123-9a16-2cf2466f065e

with top10 as (

select

initcap(project_name) as project_name,

'Top 10 Projects' as type,

count(distinct tx_signer) as user_count

from near.core.fact_transactions

join near.core.dim_address_labels on tx_receiver = address

where block_timestamp >= '2023-01-01'

and project_name is not null

and tx_status = 'Success'

and tx_signer not in (select address from near.core.dim_address_labels)

and label_type not in ('token', 'fungible_token')

group by 1,2

order by user_count desc

limit 10

, base as (

select date_trunc('week', block_timestamp) as date,

initcap(b.project_name) as top_project,

tx_hash,

tx_signer as user