defillama dex volume summary stats

theericstonedefillama dex volume summary stats

Updated 9 days ago

WITH daily_stats AS (

SELECT

protocol,

chain,

date,

volume,

-- Rolling statistics for volatility calculations

LAG(volume, 1) OVER (PARTITION BY protocol, chain ORDER BY date) as prev_day_volume,

AVG(volume) OVER (PARTITION BY protocol, chain ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as ma7

FROM external.defillama.fact_dex_volume

WHERE date >= DATEADD(day, -90, CURRENT_DATE)

volatility_calc AS (

SELECT

protocol,

chain,

-- Daily return calculation for volatility

STDDEV((volume - prev_day_volume)/NULLIF(prev_day_volume, 0)) as daily_volatility,

-- Deviation from 7-day moving average

AVG(ABS(volume - ma7)/NULLIF(ma7, 0)) as avg_deviation_from_ma

FROM daily_stats

GROUP BY 1, 2

volume_windows AS (

SELECT

protocol,

chain,

SUM(CASE WHEN date >= DATEADD(day, -1, CURRENT_DATE) THEN volume ELSE 0 END) as volume_1d,

SUM(CASE WHEN date >= DATEADD(day, -7, CURRENT_DATE) THEN volume ELSE 0 END) as volume_7d,

SUM(CASE WHEN date >= DATEADD(day, -30, CURRENT_DATE) THEN volume ELSE 0 END) as volume_30d,

SUM(CASE WHEN date >= DATEADD(day, -90, CURRENT_DATE) THEN volume ELSE 0 END) as volume_90d,

AVG(volume) as avg_daily_volume,

MAX(volume) as max_daily_volume,

MIN(volume) as min_daily_volume

Last run: 9 days ago

	PROTOCOL	CHAIN	VOLUME_1D	VOLUME_7D	VOLUME_30D	VOLUME_90D	AVG_DAILY_VOLUME	DAILY_VOLATILITY_PCT	AVG_DEVIATION_FROM_MA_PCT	MAX_DAILY_VOLUME	MIN_DAILY_VOLUME
1	pancakeswap amm	bsc	1423950572	6115416631	45640942218	117078992870	1984389709.66	56.67	40.06	7079091980	263598087
2	raydium	solana	0	0	0	106948248732	4649923857.91	39.55	40	13015929714	1876766807
3	uniswap v3	ethereum	3576019601	13359330308	48280940815	105378888452	1786082855.12	68.41	31.66	5057977793	561705637
4	orca	solana	1262159482	4291480256	18455148958	68728522986	1164890220.1	81.3	39.85	10706990133	211778810
5	meteora dlmm	solana	907431747	3835532378	21117213611	51583865028	1011448333.88	62.1	31.82	7396625797	242101764
6	raydium amm	solana	1058180846	4537198162	29694006340	41132415852	1142567107	31.15	20.52	3475673580	389567538
7	lifinity v1	solana	575355103	2009671506	9885300321	36046955875	610965353.81	75.84	33.47	4222182024	122333241
8	aerodrome slipstream	base	598937421	2377922528	14404130895	35821533437	628447955.04	45.16	19.55	1115465643	240447985
9	uniswap v3	arbitrum	883277543	3964886437	14619605070	31982802518	542081398.61	86.55	34.28	1945299952	131950324
10	pancakeswap amm v3	bsc	966105481	3094633757	18656010182	30911535733	523924334.46	63.77	29.84	1606148428	160074644
11	curve dex	ethereum	774593020	2194816787	8078676026	19880100004	336950847.53	85.03	30.88	1597078794	105579470
12	fluid dex	ethereum	1310714092	3747686182	13090309275	18470694990	313062626.95	106.47	34.72	939516317	33046671
13	uniswap v3	base	570536853	1876396763	5938561683	15655764002	265351932.24	39.64	22.03	584584768	94138143
14	sparkdex v3.1	flare	4966090	14638034	14486065115	14555193034	255354263.75	133880.28	58.42	14430513304	707733
15	thorchain	thorchain	175558784	765742185	7499162222	12077763996	204707864.34	45.43	44.07	1068743741	52362755
16	pump	solana	144553407	671018292	4070851073	10878715887	184385015.03	36.85	16.32	390295136	71031595
17	cetus amm	sui	165247192	821094392	4047407583	9799754437	171925516.44	232.86	26.25	421846566	4898224
18	stabble	solana	125312736	648086109	3778509438	8716728766	147741165.53	68.71	30.58	614729340	29424490
19	camelot v3	arbitrum	396934827	1439994424	4107324983	7900759075	133911170.76	81.53	32	359517097	37135185
20	uniswap v2	base	178890871	534484988	2523910959	7707673059	130638526.42	113.2	26.34	354505456	13060691

...

948

77KB

flipside

flipside