select

"Project",

"New Users",

case

when "NUs Change %">0 then '🟩 '||"NUs Change %"::string||'%'

when "NUs Change %"<0 then '🟥 '||"NUs Change %"::string||'%'

else "NUs Change %"::string||'%' end as "NUs Change %"

from

(select

date_trunc({{period_type}}, min_date) as "Date",

"Project",

count(distinct sender) as "New Users",

lag("New Users") over (partition by "Project" order by "Date") as lag_AUs,

round(100*("New Users"-lag_AUs)/lag_AUs,2) as "NUs Change %"

from

(select

sender,

iff( INITCAP(LABEL)='Move Dollar','Thala',INITCAP(LABEL)) as "Project",

BLOCK_TIMESTAMP as min_date,

row_number() over (partition by sender order by BLOCK_TIMESTAMP asc) as rank

from aptos.core.fact_events

join aptos.core.dim_labels on address = event_address

join aptos.core.fact_transactions using(tx_hash)

where SUCCESS=true

and tx_type='user_transaction'

)

where rank=1

group by 1,2

where date_trunc({{period_type}}, "Date")=date_trunc({{period_type}}, '{{target_day}}'::date)

order by "New Users" desc