Aptos L0 4

Updated 2025-01-14

-- forked from 0xham-d / in total @ https://flipsidecrypto.xyz/0xham-d/q/ugfze9yitufw/in-total

with pricet as (

select

hour::date as p_date,

symbol,

avg(price) as usd_price

from crosschain.price.ez_prices_hourly

where blockchain = 'ethereum'

group by 1 ,2

)

select

--date_trunc('day',block_timestamp) as date,

distinct b.symbol, --inbound/outbound

count(distinct tx_hash) as txs,

count(distinct receiver) as receivers,

count(distinct sender) as senders,

--count(distinct source_chain_name) as sources,

--count(distinct destination_chain_name) as destinations,

--token_address,

--count(distinct b.symbol) as tokens

--(amount_unadj/pow(10, decimals)) as amount

round(sum((amount_unadj/pow(10, decimals))*usd_price),0) as amount_usd

from aptos.defi.fact_bridge_activity a

join aptos.core.dim_tokens b using(token_address)

join pricet c on block_timestamp::date = p_date::date and lower(b.symbol) = lower(c.symbol)

where platform = 'layerzero'

and block_timestamp::date >= '2024-01-01'

group by 1

order by 1 asc

Last run: about 2 months ago

	SYMBOL	TXS	RECEIVERS	SENDERS	AMOUNT_USD
1	Cake	15410	8823	5647	81591149
2	SBTC	37	21	11	25638753
3	STONE	71	27	16	3520031
4	USDC	526681	296145	142701	1240271598
5	USDD	163	137	64	22820
6	USDT	610405	336432	157797	1306437562
7	WBTC	148	103	47	5707159
8	WETH	374033	232427	73797	478927346
9	sDAI	13	6	3	14
10	wUSDM	12	7	5	868

304B

31s