aggregate

datavortexaggregate

Updated 2025-03-31

WITH decoded_logs AS (

SELECT

l.tx_hash AS transaction_hash,

'0x' || SUBSTR(l.topics[2], 27) AS recipient,

'0x' || SUBSTR(l.topics[1], 27) AS sender,

l.event_index,

ROW_NUMBER() OVER (

PARTITION BY l.tx_hash

ORDER BY l.event_index ASC

) AS row_num,

l.contract_address AS pool_address,

CAST(ethereum.public.udf_hex_to_int(SUBSTRING(l.data, 3, 64)) AS DECIMAL(38,0)) AS amount0In,

CAST(ethereum.public.udf_hex_to_int(SUBSTRING(l.data, 67, 64)) AS DECIMAL(38,0)) AS amount1In,

CAST(ethereum.public.udf_hex_to_int(SUBSTRING(l.data, 131, 64)) AS DECIMAL(38,0)) AS amount0Out,

CAST(ethereum.public.udf_hex_to_int(SUBSTRING(l.data, 195, 64)) AS DECIMAL(38,0)) AS amount1Out,

t.block_timestamp

FROM boba.core.fact_event_logs l

JOIN boba.core.fact_transactions t ON l.tx_hash = t.tx_hash

WHERE l.contract_address IN (

'0xaf3da220bc03bdbf9b0280d6e2813ea0ffe03f69',

'0x78c6db2b6073e762f89a23eb3da71d2feeb18315',

'0x4f059f8d45230cd5b37544e87eebba033a5f1b17',

'0x6398a59ca706c11d02de1ea3d921742771bcd06f',

'0xce4956c398ba118f4eacabca1b32ae97bd31df2a',

'0x247442181b8baa03b3c7dc0d8e971bd4686db27c',

'0x48643395833882729032170078bf7791a0999f8c',

'0x3b444d1dbf68d1cfc64a8158affb6a24a6bdf038',

'0x2e014fe08247a080f2ed6d230b2594911f9f9a69',

'0x0ec9d6d21358221cccfc933530a8026038cedc12'

)

AND l.topics[0] = '0xd78ad95fa46c994b6551d0da85fc275fe613ce37657fb8d5e3d130840159d822'

pool_metadata AS (

SELECT

CONTRACT_ADDRESS AS pool_address,

SYMBOL_1,

Last run: about 1 month ago

	TRANSACTION_HASH	POOL_ADDRESS	DEX	RECIPIENT	SENDER	EVENT_INDEX	ROW_NUM	SYMBOL1	SYMBOL2	FINAL_AMOUNT_IN	FINAL_AMOUNT_OUT	TOKEN_IN_SYMBOL	TOKEN_OUT_SYMBOL	PRICE_IN_USD	PRICE_OUT_USD	AMOUNT_IN_USD	AMOUNT_OUT_USD
1	0x91b0b18e36ef0cbcee06c05ad8e3bac2ee31d00e292df0da4d6f1c47f6f37c40	0x4f059f8d45230cd5b37544e87eebba033a5f1b17	Sushiswap	0x9f034dddd9c6464e827f648110dda4b6a3a42844	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	4	1	USDC	WETH	173.706275	0.05414986612	USDC	WETH	1.000386708	3304.322916667	173.773448664	178.92864356
2	0xd3da388dd332bee3705fe4203263b173d0f69321d8b4e4fafbbfb5cf32c6fbcd	0x4f059f8d45230cd5b37544e87eebba033a5f1b17	Sushiswap	0x247442181b8baa03b3c7dc0d8e971bd4686db27c	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	6	1	USDC	WETH	0.931743	0.0002819510179	USDC	WETH	0.9997832083	3314.95625	0.9315410059	0.934655289
3	0x3d8e95c668189266a571a911f99ce5a057f1f635c0293e1ddf2734dc27c17b82	0x247442181b8baa03b3c7dc0d8e971bd4686db27c	Sushiswap	0x9f034dddd9c6464e827f648110dda4b6a3a42844	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	4	1	BOBA	WETH	811.400897459	0.05017153583	BOBA	WETH	0.2130689167	3429.644166667	172.884310204	172.070515209
4	0x089b79d25a4afb03b1a6082b80aca5dc65e107759e337f0618f2e9aa2d32e593	0x0ec9d6d21358221cccfc933530a8026038cedc12	Uniswap	0x4e190b7e827331b6b7f60f1b343e0e36e1413ee1	0x599fbdc7a399f359a683f5e1cdc05a373a345346	3	1	USDC	WETH	0.003	9.165425	WETH	USDC	3142.345416667	0.9999447917	9.42703625	9.164918992
5	0xfe2e6cac32b88a0619e9852f4148603dc9e0408c042fe36aee0c83f23de09535	0x247442181b8baa03b3c7dc0d8e971bd4686db27c	Sushiswap	0x8d611a80910bd289896e793d43d0ad0a34c233ea	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	13	1	BOBA	WETH	0.04993892067	666.307865094	WETH	BOBA	2973.364166667	0.2223830833	148.486597232	148.175597489
6	0xaeb9b0542cba69aa2bf30350eb889ef341938579578fcdf990500f1db099e651	0x247442181b8baa03b3c7dc0d8e971bd4686db27c	Sushiswap	0x9f034dddd9c6464e827f648110dda4b6a3a42844	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	3	1	BOBA	WETH	0.1941235852	3094.338699209	WETH	BOBA	3396.935	0.2022915833	659.425201047	625.958674833
7	0x095e890a9c68ea61424c462b1773d130dad5eff09d95af4fab0c8ce3ce22ea2f	0x247442181b8baa03b3c7dc0d8e971bd4686db27c	Sushiswap	0x952e6e48bdca828899b6e0002573f87efe5bbc5e	0x000000000003b57982426680c8eeeaf1920ffe21	3	1	BOBA	WETH	0.01751814467	225.896142714	WETH	BOBA	2633.507083333	0.2028615	46.134158084	45.825630355
8	0x469b4294ca2c5e857c4edabe7638b0469b9adbf03eaf6db7d48eb8b195554b22	0x0ec9d6d21358221cccfc933530a8026038cedc12	Uniswap	0x87c85c3109a6571ee5c2a353bbb8fdc707f3289b	0x599fbdc7a399f359a683f5e1cdc05a373a345346	3	1	USDC	WETH	0.001	3.059411	WETH	USDC	3142.345416667	0.9999447917	3.142345417	3.059242095
9	0x3b7eeab6bfd7d51b6c0798d9310b0d79074ec5386ea25dfcc77f8cadd407f03e	0x247442181b8baa03b3c7dc0d8e971bd4686db27c	Sushiswap	0x8d611a80910bd289896e793d43d0ad0a34c233ea	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	9	1	BOBA	WETH	0.1440114705	1112.48559643	WETH	BOBA	3218.958189024	0.4146057269	463.566902296	461.242899342
10	0xd0b75b73293ba8aa5630b8b641243c0594b3ae314f0d66b7a6788543b54c6213	0x4f059f8d45230cd5b37544e87eebba033a5f1b17	Sushiswap	0xbe811a0d44e2553d25d11cb8dc0d3f0d0e6430e6	0xbe811a0d44e2553d25d11cb8dc0d3f0d0e6430e6	4	1	USDC	WETH	334	0.1813148879	USDC	WETH		1844.511766242		334.437444217
11	0x59df42c8bee96a7fe906e196c5bd94cfd329082b0da37d79b7252ad1bc013118	0x6398a59ca706c11d02de1ea3d921742771bcd06f	Sushiswap	0x000000000003b57982426680c8eeeaf1920ffe21	0x000000000003b57982426680c8eeeaf1920ffe21	6	1	WBTC	WETH	0.00130168	0.03553639193	WBTC	WETH		3613.640416667		128.415742157
12	0xd611991934244f904f48d23ee90b53cf381e42bfc4fd7cf91634f0d3093bcd39	0x0ec9d6d21358221cccfc933530a8026038cedc12	Uniswap	0x361cf2208a15f92a6244d6dadf9db9250a7b05cc	0x599fbdc7a399f359a683f5e1cdc05a373a345346	4	1	USDC	WETH	11.037826	0.002982060776	USDC	WETH	0.9996728333	3650.497916667	11.034214791	10.886006649
13	0x1224b94cbc99b40232e713237fa0d994254f6948e51d32431b61063f5356b4c6	0x0ec9d6d21358221cccfc933530a8026038cedc12	Uniswap	0xf2630ca4592fc63afafd69a2cf92315df3caae82	0x599fbdc7a399f359a683f5e1cdc05a373a345346	4	1	USDC	WETH	10.987388	0.002982060239	USDC	WETH	0.999767125	3714.124583333	10.984829312	11.075743241
14	0x2c559cf409ffa70f515cc47f95243915664896561a02cf419cf492231a1be7a5	0x247442181b8baa03b3c7dc0d8e971bd4686db27c	Sushiswap	0x9f034dddd9c6464e827f648110dda4b6a3a42844	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	4	1	BOBA	WETH	658.768201289	0.05008025834	BOBA	WETH	0.304699125	3985.855	200.726094511	199.612648112
15	0xa0264df807d96fa0d951958efd4dbfb4f8725d841ae2a3f225cdae52d0c00322	0x4f059f8d45230cd5b37544e87eebba033a5f1b17	Sushiswap	0x9f034dddd9c6464e827f648110dda4b6a3a42844	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	4	1	USDC	WETH	320.431848	0.09059686392	USDC	WETH	0.9994014068	3503.120309588	320.240039689	317.371713981
16	0xedf7ec3986b3b2cb2949adca7e16f8aad0cb72f455163e582a2de848bbbc546f	0x0ec9d6d21358221cccfc933530a8026038cedc12	Uniswap	0x8eb38674a93d605c9730f9e65304483d6cce4d07	0x599fbdc7a399f359a683f5e1cdc05a373a345346	4	1	USDC	WETH	7.687704	0.00198803309	USDC	WETH	0.999970625	3881.776666667	7.687478174	7.71710046
17	0x6a21ac5c54b80e0186ab3e8406d0cfce6c2d5a3049332903f9283d785c8e93ee	0x4f059f8d45230cd5b37544e87eebba033a5f1b17	Sushiswap	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	4	1	USDC	WETH	47	0.03847026225	USDC	WETH		1208.515170525		46.491895547
18	0xd700b090e26624714bc20f6923be6088b16a3756f1c04dcc76452755fcf57acb	0x4f059f8d45230cd5b37544e87eebba033a5f1b17	Sushiswap	0xfce0c80aac0deb05c50d2026922dcd2c890d26d4	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	4	1	USDC	WETH	0.01669627292	33.1	WETH	USDC	1986.395833333	0.9999525417	33.165406953	33.098429129
19	0xc6cb1d754ca76c403660e27327a5bccc02161af3823b5fc077377992876adf08	0x247442181b8baa03b3c7dc0d8e971bd4686db27c	Sushiswap	0x8d611a80910bd289896e793d43d0ad0a34c233ea	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	9	1	BOBA	WETH	0.05000464101	587.198929611	WETH	BOBA	2543.725833333	0.2135293333	127.198097128	125.384195974
20	0x6f6ed44065a9a7ed0b390197102398625e33fef359d048a0feac5de282d5c92b	0x4f059f8d45230cd5b37544e87eebba033a5f1b17	Sushiswap	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	0x1b02da8cb0d097eb8d57a175b88c7d8b47997506	6	1	USDC	WETH	51.835512	0.01328083677	USDC	WETH	0.9995544167	3923.948333333	51.81241496	52.113317323

...

200

63KB

38s

flipside

flipside