d3 | zyroq | Flipside

Updated 3 days ago

WITH filtered_swaps AS (

SELECT

tx_hash,

sender AS swapper,

LOWER(event_data:from_token) AS token_in,

LOWER(event_data:to_token) AS token_out,

event_data:amount_in / 1e6 AS amount_in,

event_data:amount_out / 1e6 AS amount_out,

block_timestamp

FROM aptos.core.fact_events

JOIN aptos.core.fact_transactions USING (tx_hash, block_timestamp)

WHERE block_timestamp > CURRENT_DATE - INTERVAL '{{trading_period}} days'

AND event_type =

'0x4bf51972879e3b95c4781a5cdcb9e1ee24ef483e7d22f2d903626f126df62bd1::liquidity_pool::SwapEvent'

AND success = TRUE

AND (

event_data:from_token ILIKE '%0xbae207659db88bea0cbead6da0ed00aac12edcdda169e591cd41c94180b46f3b%'

OR event_data:to_token ILIKE '%0xbae207659db88bea0cbead6da0ed00aac12edcdda169e591cd41c94180b46f3b%'

)

first_swaps AS (

SELECT

swapper,

MIN(block_timestamp) AS first_swap_time

FROM filtered_swaps

GROUP BY swapper

token_symbols AS (

SELECT

fs.tx_hash,

fs.swapper,

fs.amount_in,

fs.amount_out,

fs.token_in,

fs.token_out,

fs.block_timestamp,

Last run: 3 days ago

	PAIR	TOKEN_ADDRESS_PAIR	TOTAL_SWAPS	TOTAL_SWAPPERS	NEW_SWAPPERS	TOTAL_VOLUME
1	APT - USDC	0x1::aptos_coin::aptoscoin - 0xbae207659db88bea0cbead6da0ed00aac12edcdda...	39068	399	298	14651657.178786
2	USDC - USDt	0xbae207659db88bea0cbead6da0ed00aac12edcdda169e591cd41c94180b46f3b - 0x3...	35578	211	156	2486090.862041
3	USDt - USDC	0x357b0b74bc833e95a115ad22604854d6b0fca151cecd94111770e5d6ffc9dc2b - 0xb...	35404	110	63	2466943.522518
4	USDC - APT	0xbae207659db88bea0cbead6da0ed00aac12edcdda169e591cd41c94180b46f3b - 0x1...	39141	279	181	726040.677635
5	lzUSDC - USDC	0xf22bede237a07e121b56d91a491eb7bcdfd1f5907926a9e58338f964a01b17fa::asse...	530	214	134	43387.018047
6	USDC - lzUSDC	0xbae207659db88bea0cbead6da0ed00aac12edcdda169e591cd41c94180b46f3b - 0xf...	899	221	168	28043.268803

1KB

flipside