theericstoneUniswap Protocol TVL
Updated 2021-05-24
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 pairs as (
select distinct pair
from (
select distinct pool_address as pair
from ethereum.dex_liquidity_pools
where platform = 'uniswap-v2'
UNION
select from_address as pair from (select count(tx_id) as ntx, from_address
from ethereum.udm_events where LOWER(origin_function_name) like 'swap%'
and from_label = 'uniswap'
and from_address <> '0x7a250d5630b4cf539739df2c5dacb4c659f2488d' -- v2 router
and block_timestamp > getdate() - interval '2 months'
group by 2) where ntx > 2
)
),
liquid AS (
SELECT
rawbals.*,
row_number() over (partition by day, pair order by liquidity desc) as rn from (
SELECT
date_trunc('day', balance_date) as day,
user_address as pair,
contract_address as token,
sum(amount_usd) as liquidity
--sum(balance) as native_liquidity,
--row_number() over(partition by date_trunc('day', balance_date), user_address order by balance_date) as rn
FROM ethereum.erc20_balances b
LEFT JOIN pairs ON b.user_address = pairs.pair
WHERE balance_date > getdate() - interval '2 weeks'
AND user_address IN (pairs.pair)
AND balance != 0
AND amount_usd IS NOT NULL
GROUP BY 1,2,3
) rawbals
),
Run a query to Download Data