p1 | zyroq | Flipside

zyroqp1

Updated 2025-04-08

WITH prices AS (

SELECT

DATE_TRUNC('{{granularity}}', HOUR) AS date,

AVG(PRICE) AS avg_price

FROM aptos.price.ez_prices_hourly

WHERE TOKEN_ADDRESS = '0x1::aptos_coin::AptosCoin'

GROUP BY DATE_TRUNC('{{granularity}}', HOUR)

first_tx AS (

SELECT

IFF(direction = 'inbound', RECEIVER, SENDER) AS user,

MIN(block_timestamp) AS first_activity

FROM aptos.defi.ez_bridge_activity

GROUP BY 1

)

SELECT

DATE_TRUNC('{{granularity}}', b.block_timestamp) AS date,

b.platform,

SUM(b.AMOUNT_IN_USD) AS volume,

COUNT(DISTINCT b.TX_HASH) AS bridges,

COUNT(DISTINCT IFF(b.direction = 'inbound', b.RECEIVER, b.SENDER)) AS users,

COUNT(DISTINCT b.TOKEN_ADDRESS) AS tokens_bridged,

SUM(b.AMOUNT_IN_USD) / COALESCE(NULLIF(COUNT(DISTINCT b.TX_HASH), 0), 1) AS avg_vol_per_tx,

COUNT(DISTINCT b.TX_HASH) / COALESCE(NULLIF(COUNT(DISTINCT IFF(b.direction = 'inbound', b.RECEIVER, b.SENDER)), 0), 1) AS avg_bridges_per_user,

SUM((t.gas_used * t.gas_unit_price) / 1e8) AS bridging_fees_apt,

SUM((t.gas_used * t.gas_unit_price) / 1e8 * p.avg_price) AS bridging_fees_usd,

COUNT(DISTINCT CASE WHEN f.first_activity >= DATE_TRUNC('{{granularity}}', b.block_timestamp) THEN f.user END) AS new_users,

COUNT(DISTINCT CASE WHEN f.first_activity < DATE_TRUNC('{{granularity}}', b.block_timestamp) THEN f.user END) AS returning_users

FROM aptos.defi.ez_bridge_activity b

LEFT JOIN aptos.core.fact_transactions t

Last run: 18 days ago

	DATE	PLATFORM	VOLUME	BRIDGES	USERS	TOKENS_BRIDGED	AVG_VOL_PER_TX	AVG_BRIDGES_PER_USER	BRIDGING_FEES_APT	BRIDGING_FEES_USD	NEW_USERS	RETURNING_USERS
1	2025-01-08 00:00:00.000	wormhole	89409.441721081	63	33	10	1419.197487636	1.909091	0.022139	0.2041861521	2	31
2	2025-01-08 00:00:00.000	celer_cbridge		2	1	1		2	0.000988	0.009112241667	0	1
3	2025-01-08 00:00:00.000	layerzero	6660322.31975842	1349	1068	5	4937.229295596	1.263109	0.369357	3.406548831	631	437
4	2025-01-09 00:00:00.000	layerzero	1287822.58031746	714	435	7	1803.673081677	1.641379	0.05637	0.4966197	29	406
5	2025-01-09 00:00:00.000	celer_cbridge	14.67394804	1	1	1	14.67394804	1	0.000442	0.00389402	0	1
6	2025-01-09 00:00:00.000	wormhole	33378.928551769	73	44	13	457.2455966	1.659091	0.032354	0.28503874	6	38
7	2025-01-10 00:00:00.000	layerzero	1816723.13140612	629	410	7	2888.272069008	1.534146	0.056582	0.5058666558	32	378
8	2025-01-10 00:00:00.000	wormhole	255890.245658347	64	34	8	3998.285088412	1.882353	0.016666	0.1490009842	4	30
9	2025-01-11 00:00:00.000	wormhole	52547.947422315	102	39	9	515.175955121	2.615385	0.026648	0.2401873067	2	37
10	2025-01-11 00:00:00.000	layerzero	3136529.09041599	436	326	7	7193.874060587	1.337423	0.035579	0.3206853867	19	307
11	2025-01-12 00:00:00.000	wormhole	52748.155524707	50	34	9	1054.963110494	1.470588	0.012341	0.1109353058	2	32
12	2025-01-12 00:00:00.000	mover	26.297349733	3	2	2	8.765783244	1.5	0.000457	0.004108049167	0	2
13	2025-01-12 00:00:00.000	layerzero	318335.64483883	436	300	5	730.127625777	1.453333	0.04086	0.36729735	32	268
14	2025-01-13 00:00:00.000	layerzero	463166.063159003	519	372	5	892.420160229	1.395161	0.050653	0.4257595704	31	341
15	2025-01-13 00:00:00.000	wormhole	94664.485107353	65	45	13	1456.376693959	1.444444	0.019959	0.1677637112	2	43
16	2025-01-14 00:00:00.000	mover	11.891260088	3	2	2	3.963753363	1.5	0.000457	0.003964284583	1	1
17	2025-01-14 00:00:00.000	layerzero	2184985.20178675	851	699	5	2567.550178363	1.217454	0.260685	2.261333756	335	364
18	2025-01-14 00:00:00.000	wormhole	592876.905459992	82	41	10	7230.206164146	2	0.022691	0.1968349704	9	32
19	2025-01-15 00:00:00.000	wormhole	135769.573885671	71	36	12	1912.247519516	1.972222	0.02339	0.2123519625	6	30
20	2025-01-15 00:00:00.000	layerzero	2864784.64308323	432	311	5	6631.445933063	1.389068	0.059688	0.54189243	70	241

...

214

24KB

112s

flipside

flipside