Trado swaps by asset

adriaparcerisasTrado swaps by asset

Updated 3 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: 3 days ago

	DATE	ASSET	ACTIVE_SWAPPERS	NEW_SWAPPERS	TOTAL_SWAPPERS	SWAPS	TOTAL_SWAPS	FLOW_FEES	AVG_TX_FLOW_FEE
1	2025-05-05 00:00:00.000	wFLOW	2	0	271	36	51124	1.4426216e-11	2.003641111e-13
2	2025-05-05 00:00:00.000	USDC.e	2	0	157	36	48525	7.213108e-12	2.003641111e-13
3	2025-05-04 00:00:00.000	wFLOW	3	0	271	50	51088	1.9466428e-11	1.9466428e-13
4	2025-05-04 00:00:00.000	USDC.e	2	0	157	49	48489	9.537103e-12	1.946347551e-13
5	2025-05-04 00:00:00.000	ankrFLOWEVM	1	0	142	1	2043	3.92222e-13	1.96111e-13
6	2025-05-03 00:00:00.000	USDC.e	3	0	157	83	48440	1.581239e-11	1.905107229e-13
7	2025-05-03 00:00:00.000	stFlow	1	0	69	1	546	6.26576e-13	3.13288e-13
8	2025-05-03 00:00:00.000	wFLOW	4	0	271	84	51038	3.2251356e-11	1.919723571e-13
9	2025-05-02 00:00:00.000	stFlow	2	1	69	3	545	5.987540289e-8	1.197508058e-8
10	2025-05-02 00:00:00.000	USDC.e	4	0	157	49	48357	1.405351169e-7	2.755590528e-9
11	2025-05-02 00:00:00.000	wFLOW	6	0	271	54	50954	2.004212621e-7	1.855752427e-9
12	2025-05-02 00:00:00.000	ankrFLOWEVM	1	0	142	2	2042	1.05693e-12	2.642325e-13
13	2025-05-01 00:00:00.000	wFLOW	4	1	271	48	50900	1.9200164e-11	2.000017083e-13
14	2025-05-01 00:00:00.000	ankrFLOWEVM	2	1	142	5	2040	2.22068e-12	2.22068e-13
15	2025-05-01 00:00:00.000	USDC.e	2	0	157	43	48308	8.489742e-12	1.974358605e-13
16	2025-04-30 00:00:00.000	wFLOW	8	0	270	113	50852	0.0001690936482	7.482019831e-7
17	2025-04-30 00:00:00.000	ankrFLOWEVM	5	0	141	23	2035	0.00016909361	0.000003675948044
18	2025-04-30 00:00:00.000	USDC.e	5	0	157	89	48265	2.0794519e-11	2.188896737e-13
19	2025-04-29 00:00:00.000	ankrFLOWEVM	4	1	141	6	2012	0.0001707432016	0.00001422860013
20	2025-04-29 00:00:00.000	USDC.e	4	0	157	86	48176	0.00003725861711	4.282599668e-7

...

708

52KB

14s