WITH

news as (

SENDER_WALLET as new_wallet,

COUNT(*) AS tx_count,

SUM(STARGATE_SWAP_USD) AS total_volume_usd,

MIN(trunc(SOURCE_TIMESTAMP_UTC,'day')) AS debut

FROM

external.layerzero.fact_transactions_snapshot x

GROUP BY

1

),

news2 as (

SELECT debut, count(distinct new_wallet) as new_wallets,

sum(new_wallets) over (order by debut) as total_wallets,

avg(tx_count) as avg_transactions,

avg(total_volume_usd) as avg_volume_bridged

from news group by 1

),

transactions as (

select trunc(SOURCE_TIMESTAMP_UTC,'day') as date,

count(distinct sender_wallet) as active_wallets,

from external.layerzero.fact_transactions_snapshot

group by 1 order by 1 desc

),

layerzero as (

select

date,

active_wallets,

new_wallets,

total_wallets,

avg_transactions,

from transactions