2️⃣ Top Traded Pairs (Last 30 Days)

permary2️⃣ Top Traded Pairs (Last 30 Days)

Updated 2025-02-18

WITH trade_summary AS (

SELECT

COALESCE(ft.SELLING_ASSET_CODE, 'XLM') as SELLING_ASSET_CODE,

COALESCE(ft.BUYING_ASSET_CODE, 'XLM') as BUYING_ASSET_CODE,

SUM(ft.SELLING_AMOUNT) AS total_sold,

SUM(ft.BUYING_AMOUNT) AS total_bought,

COUNT(*) AS trade_count,

CASE

WHEN SUM(ft.SELLING_AMOUNT) = 0 THEN NULL

ELSE SUM(ft.BUYING_AMOUNT)/SUM(ft.SELLING_AMOUNT)

END as avg_price

FROM stellar.defi.fact_trades ft

WHERE ft.LEDGER_CLOSED_AT >= DATEADD(DAY, -30, CURRENT_DATE)

GROUP BY

COALESCE(ft.SELLING_ASSET_CODE, 'XLM'),

COALESCE(ft.BUYING_ASSET_CODE, 'XLM')

)

SELECT

ts.SELLING_ASSET_CODE,

s_meta.name as selling_asset_name,

ts.BUYING_ASSET_CODE,

b_meta.name as buying_asset_name,

ts.total_sold,

ts.total_bought,

ts.trade_count,

ts.avg_price

FROM trade_summary ts

LEFT JOIN stellar.price.ez_asset_metadata s_meta

ON ts.SELLING_ASSET_CODE = s_meta.asset_code

LEFT JOIN stellar.price.ez_asset_metadata b_meta

ON ts.BUYING_ASSET_CODE = b_meta.asset_code

WHERE ts.total_sold > 0 -- Filter out any zero-value trades

ORDER BY ts.total_sold DESC

LIMIT 10;

Last run: 28 days ago

	SELLING_ASSET_CODE	BUYING_ASSET_CODE	BUYING_ASSET_NAME	TOTAL_SOLD	TOTAL_BOUGHT	TRADE_COUNT	AVG_PRICE
1	SGB			55732674563640.2	24563.7717165	24723	4.407427404e-10
2	SGB	SUNGOLD		31382925464328.3	62158778230.532	5096	0.001980655956
3	SGB	SDEXEX		24633651183336.4	42309933.6341111	8717	0.000001717566483
4	SGB	yXLM		21441239282691.7	7380.1609334	26813	3.442040283e-10
5	SGB	AQUA	Aquarius	21083176681541.8	2046621.4059423	1418	9.707367333e-8
6	SGB	VELO	Velo	14851386478044.1	61772.5539032	10643	4.159379597e-9
7	XPAL			13424837989371.3	2067.2892652	6365	1.539898855e-10
8	SGB	DOGET	Doge Token	10544877617494.7	10348992.3747209	2327	9.81423659e-7
9	RIPPLE			7271598231198	4011.2463685	2043	5.516320128e-10
10	AXLM	SGB		6956518091428.77	3891499379680.66	3717	0.5594033292

789B

flipside