anomoneCheck Uni V3 TVL
Updated 2022-02-20
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 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
) ,
Balances as (
SELECT
BALANCE_DATE,
USER_ADDRESS,
LABEL,
ADDRESS_NAME,
LABEL_TYPE,
LABEL_SUBTYPE,
CONTRACT_ADDRESS,
CONTRACT_LABEL,
a.SYMBOL,
a.PRICE,
DECIMALS,
NON_ADJUSTED_BALANCE,
BALANCE,
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