iboo-jbj2MVthe distribution of transactions based on end up USDC or USDT
Updated 2022-03-31
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
--31566704 USDC | 312769 USDT
with from_usdc_or_usdt as (
select tx_group_id , swapper ,swap_from_amount,swap_to_amount,swap_to_asset_id , block_timestamp
from algorand.swaps
where swap_from_amount >0
and swap_from_asset_id in (31566704 ,312769)
and block_timestamp>= '2022-01-01'
),
to_usdc_or_usdt as (
select s.tx_group_id as tx, s.swapper as swapper , f.swap_from_amount as stable_from_amount ,
s.swap_to_amount as stable_to_amount , s.swap_to_asset_id as end_up_token
from algorand.swaps s join from_usdc_or_usdt f
on s.swapper = f.swapper
and s.swap_from_asset_id = f.swap_to_asset_id
and f.swap_from_amount <=s.swap_to_amount
and f.swap_to_amount = s.swap_from_amount
and date_trunc('second',f.block_timestamp) <date_trunc('second',s.block_timestamp)
where s.swap_from_amount >0
and s.swap_to_asset_id in (31566704 ,312769)
and s.block_timestamp>= '2022-01-01'
),
end_up as ( select
swapper,
case when end_up_token = 31566704 then 'USDC'
when end_up_token = 312769 then 'USDT'
end as End_up_token,
sum (stable_to_amount - stable_from_amount) as sum_gain,
count (DISTINCT tx) as transactions
from to_usdc_or_usdt
group by 1,2
)
select End_up_token ,
COUNT(DISTINCT swapper) as wallets,
sum (transactions ) as transactions ,
sum(sum_gain) as sum_gain
from end_up
Run a query to Download Data