anomoneUniswap V3 daily TVL
Updated 2022-02-21
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
›
⌄
SELECT * FROM
(
WITH token_prices as (
SELECT token_address,SYMBOL, PRICE, date_trunc('day', hour) as Day FROM
(SELECT
PRICE,HOUR, token_address, SYMBOL,
row_number() over(partition by token_address , date_trunc('day', hour)
order by hour desc) as rn
FROM ethereum.token_prices_hourly
WHERE date_trunc('day', hour) > date_trunc('day', getdate()) - interval'3 months'
)as t
WHERE t.rn = 1
)
SELECT
BALANCE_DATE,
'uniswap-v3' as platform,
sum(CASE
WHEN (a.price/p.price) > 10 THEN non_adjusted_balance/(POWER(10, a.decimals)) * p.PRICE
WHEN CONTRACT_ADDRESS in ('0xfd957f21bd95e723645c07c48a2d8acb8ffb3794','0x8765b1a0eb57ca49be7eacd35b24a574d0203656','0x5aa7c403c7de4b3bb0cc07079a03e389671a4771')
THEN non_adjusted_balance/(POWER(10, a.decimals)) * p.PRICE
WHEN (a.PRICE is null and DECIMALS is not null) then non_adjusted_balance/(POWER(10, a.decimals)) * p.PRICE
WHEN (a.PRICE is not null and DECIMALS is null ) then 0
WHEN (DECIMALS is null) then 0
ELSE amount_usd END )as amount_usd
FROM ethereum.erc20_balances a
INNER JOIN ethereum.dex_liquidity_pools b
ON b.pool_address = a.USER_ADDRESS
INNER JOIN token_prices p
ON a.CONTRACT_ADDRESS = p.token_address and date_trunc('day', p.Day) = date_trunc('day', a.BALANCE_DATE)