Trado swaps by asset

adriaparcerisasTrado swaps by asset

Updated 5 days ago

-- forked from Trado swaps @ https://flipsidecrypto.xyz/studio/queries/eb696c0e-a44f-4591-801f-100bcdcd6dd8

-- unrwap wflow to flow 0x9db24dee5d3d7cca9ebbbf738ec78722c24f49bb16de4f23f45906b61efab267

-- wrap flow to wflow 0x6f81ff66eaaea5214b89c7c22ca37a1b0a2010f22438fd9419968d768d9f371d

with

swaps as (

select x.tx_hash, x.origin_from_address, x.contract_address, y.tx_fee, x.block_timestamp

from flow.core_evm.fact_event_logs x

join flow.core_evm.fact_transactions y

on x.tx_hash=y.tx_hash

where (y.to_address='0x3EF68D3f7664b2805D4E88381b64868a56f88bC4' or x.origin_function_signature='0xac9650d8')

and x.TX_SUCCEEDED='TRUE'

and x.contract_address in ('0x1b97100ea1d7126c4d60027e231ea4cb25314bdb','0x7f27352d5f83db87a5a3e00f4b07cc2138d8ee52','0xd3bf53dac106a0290b0483ecbc89d40fcc961f3e','0x5598c0652b899eb40f169dd5949bdbe0bf36ffde')

news as (

select distinct origin_from_address as swapper, contract_address, min(trunc(block_timestamp,'day')) as debut

from swaps group by 1,2

)

select

trunc(block_timestamp,'day') as date,

case when x.contract_address='0x1b97100ea1d7126c4d60027e231ea4cb25314bdb' then 'ankrFLOWEVM'

when x.contract_address='0x7f27352d5f83db87a5a3e00f4b07cc2138d8ee52' then 'USDC.e'

when x.contract_address='0x5598c0652b899eb40f169dd5949bdbe0bf36ffde' then 'stFlow'

else 'wFLOW'

end as asset,

count(distinct origin_from_address) as active_swappers,

count(distinct swapper) as new_swappers,

sum(new_swappers) over (partition by asset order by date) as total_swappers,

count(DISTINCT tx_hash) as swaps,

sum(swaps) over (partition by asset order by date) as total_swaps,

sum(tx_fee) as flow_fees,

avg(tx_fee) as avg_tx_flow_fee

from swaps x left join news y on trunc(block_timestamp,'day')=debut and x.contract_address=y.contract_address

group by 1,2 order by 1 desc

Last run: 5 days ago

	DATE	ASSET	ACTIVE_SWAPPERS	NEW_SWAPPERS	TOTAL_SWAPPERS	SWAPS	TOTAL_SWAPS	FLOW_FEES	AVG_TX_FLOW_FEE
1	2025-04-22 00:00:00.000	USDC.e	4	0	153	46	47471	9.787676e-12	2.082484255e-13
2	2025-04-22 00:00:00.000	wFLOW	4	0	263	46	50005	1.8969672e-11	2.06192087e-13
3	2025-04-21 00:00:00.000	wFLOW	3	0	263	64	49959	2.6355828e-11	2.059049062e-13
4	2025-04-21 00:00:00.000	USDC.e	3	0	153	64	47425	1.3177914e-11	2.059049062e-13
5	2025-04-20 00:00:00.000	ankrFLOWEVM	2	1	137	12	1981	2.367042e-12	1.820801538e-13
6	2025-04-20 00:00:00.000	USDC.e	3	0	153	82	47361	1.615736e-11	1.970409756e-13
7	2025-04-20 00:00:00.000	wFLOW	5	1	263	94	49895	3.6699312e-11	1.952091064e-13
8	2025-04-19 00:00:00.000	USDC.e	3	0	153	52	47279	1.0455718e-11	2.010715e-13
9	2025-04-19 00:00:00.000	wFLOW	3	0	262	52	49801	2.0911436e-11	2.010715e-13
10	2025-04-18 00:00:00.000	USDC.e	3	0	153	37	47227	7.41679e-12	2.004537838e-13
11	2025-04-18 00:00:00.000	wFLOW	3	0	262	37	49749	1.483358e-11	2.004537838e-13
12	2025-04-17 00:00:00.000	ankrFLOWEVM	2	0	136	6	1969	0.0002630755	0.00002391595455
13	2025-04-17 00:00:00.000	wFLOW	6	1	262	67	49712	0.0004223876231	0.000003152146441
14	2025-04-17 00:00:00.000	USDC.e	5	2	153	61	47190	0.0002826212231	0.000002207978305
15	2025-04-16 00:00:00.000	USDC.e	3	0	151	146	47129	2.9567859e-11	2.025195822e-13
16	2025-04-16 00:00:00.000	wFLOW	3	0	261	146	49645	5.9135718e-11	2.025195822e-13
17	2025-04-15 00:00:00.000	ankrFLOWEVM	2	0	136	3	1963	1.107564e-12	2.215128e-13
18	2025-04-15 00:00:00.000	wFLOW	5	1	261	90	49499	0.0001064121961	5.911788674e-7
19	2025-04-15 00:00:00.000	USDC.e	3	1	151	87	46983	0.00008059117743	9.158088344e-7
20	2025-04-14 00:00:00.000	wFLOW	5	0	260	121	49409	1.0214786e-11	4.22098595e-14

...

669

48KB