-- forked from 2023-06-26 05:08 AM @ https://flipsidecrypto.xyz/edit/queries/a8d8bb2d-4a3d-496f-afd4-9a5255f61fad

with labeled as (

select *

select

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

project_name as project,

count(distinct TX_HASH) as txs,

count(distinct TX_SIGNER) as users,

rank() over (partition by date order by users desc) as rank

from near.core.fact_transactions

join near.core.dim_address_labels on TX_RECEIVER = address

where block_timestamp >= '{{starting_date}}'

and tx_status='Success'

and PROJECT_NAME is not null

group by 1,2

order by 1 asc)

where rank <= 12

),

kaikai as (

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

'kaikai' as project,

count(distinct tx_hash) as txs,

count(distinct tx_signer) as users

from near.core.fact_transactions

where tx_status='Success'

and block_timestamp >= '{{starting_date}}'

and tx_signer ilike '%users.kaiching'

group by 1,2

order by 2 asc

),