daveliuLiquidity Token Prices
Updated 2022-11-11
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
›
⌄
-- we can calculate the historical price of a given LP token if we have
-- total supply and native TVL for the token
-- native tvl / total supply == LP token price
WITH prices AS (
SELECT date_trunc('day',hour) AS balance_date,
symbol,
avg(price) AS price
FROM ethereum.token_prices_hourly
WHERE token_address in (
'0x956f47f50a910163d8bf957cf5846d573e7f87ca', -- fei
'0xc7283b66eb1eb5fb86327f08e1b5816b0720212b') -- tribe
AND hour > '2021-04-01'
GROUP BY 1, 2
)
SELECT
etb.balance_date,
sum(etb.balance) AS total_supply,
avg(tvlhist.tvl) as native_tvl,
native_tvl / total_supply as price
FROM
ethereum.erc20_balances etb
JOIN (
SELECT tvld.balance_date, sum(tvl_usd) as tvl FROM (
SELECT detb.balance_date,
detb.contract_address,
detb.symbol,
sum(detb.balance) as tvl,
avg(prices.price) as pricey,
tvl * pricey as tvl_usd
FROM ethereum.erc20_balances detb
JOIN prices ON prices.balance_date = detb.balance_date AND detb.symbol = prices.symbol
WHERE detb.balance_date > '2021-04-01'
AND contract_address IN (
LOWER('0x956f47f50a910163d8bf957cf5846d573e7f87ca'), -- FEI
Run a query to Download Data