shadilProfit for AMMs
Updated 2022-04-16
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
›
⌄
with tbl_in_sushiswap as(
select t3.*
from (
select ROW_NUMBER() over(partition by tx_id order by event_index ASC)as rownum, s1.*
from ethereum.dex_swaps s1
where direction='IN'
and date(block_timestamp) BETWEEN '2022-01-01' and CURRENT_DATE - 2
and platform = 'sushiswap'
and amount_usd > 0
) t3
where t3.rownum = 1
),
-- fee for sushiswap pools = 0.0025
sushi as (
SELECT
date(block_timestamp) as date, sum(amount_usd) * 0.0025 as total_amount_usd, COUNT(DISTINCT tx_id) as swap_count, avg(amount_usd) as avg_amount_usd, 'sushiswap' as type
FROM tbl_in_sushiswap
GROUP BY date
),
tbl_in_uniswap as(
select t3.*
from (
select ROW_NUMBER() over(partition by tx_id order by event_index ASC)as rownum, s1.*
from ethereum.dex_swaps s1
where direction='IN'
and date(block_timestamp) BETWEEN '2022-01-01' and CURRENT_DATE - 2
and platform like '%uniswap%'
and amount_usd > 0
and amount_usd < 100000000 -- there are some huge swaps amount USD!
) t3
where t3.rownum = 1
),
uniswap as (
SELECT
date(block_timestamp) as date, sum(amount_usd) * 0.003 as total_amount_usd, COUNT(DISTINCT tx_id) as swap_count, avg(amount_usd) as avg_amount_usd, 'uniswap' as type
Run a query to Download Data