zexianghuang
commented
2 years ago tvl计算: 将以太币转换为美元价值：不同的时间，不同的价格来计算 里面核心有几块： 交易数据 trade , 价格：price，不同时间的美元价格。
--for tokens where dune doesn't have the price, calculate the avg price in it's last hour of swaps (6 hr if no swaps in the last hour) --since we don't have an amount field for unmapped tokens, I'll calc it based on... raw amount -- through a few queries, this gets the unit price of each token (assuming 18 decimals) -- DO NOT USE THIS FOR KNOWN TOKENS - only for the ones we don't have, we'll assume 18 decimals
对于价格，确实有这几种方式
1.没有价格的代币，计算过去1个小时的平均价格；
2.没有价格的话，用原始金额计算： coalesce(amount_usd/token_sold_amount, amount_usd/(token_sold_amount_raw*power(10,decimals))) AS price,
3.查询几个代币的单价作为参考
第一种算法：根据dex的价格进行计算：
WITH
trades AS (
SELECT
DATE_TRUNC('day', t.block_time) AS dt,
project,
version,
CASE
WHEN version = '1' THEN project
ELSE project || ' V' || version || ' - ' || (ROUND(p.fee / 1e4, 2)) || '%'
END as proj_v,
t.project_contract_address as exchange_contract_address,
t.token_bought_address,
t.token_sold_address,
t.token_bought_symbol,
t.token_sold_symbol,
t.token_bought_symbol || '/' || t.token_sold_symbol AS pair,
t.token_bought_symbol || '/' || t.token_sold_symbol || ' - ' || (ROUND(p.fee / 1e4, 2)) || '%' AS pool,
SUM(t.token_bought_amount) AS token_bought_amount,
SUM(t.token_sold_amount) AS token_sold_amount,
SUM(t.amount_usd) AS usd_traded,
COUNT(t.amount_usd) AS num_trades,
COUNT(DISTINCT t.taker) AS num_users
FROM
uniswap_v3_ethereum.trades t
INNER JOIN uniswap_v3_ethereum.Factory_evt_PoolCreated p ON t.project_contract_address = p.pool
WHERE
t.block_date >= '2021-05-05'
and t.project_contract_address in (
'0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8',
'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640',
'0x7bea39867e4169dbe237d55c8242a8f2fcdcc387'
)
GROUP BY
1,
2,
3,
4,
5,
6,
7,
8,
9,
10,
11
),
prices AS (
SELECT
*
FROM
prices.usd p
WHERE
p.minute > '2021-05-05'
AND (
contract_address IN (
SELECT
token_sold_address
FROM
trades
GROUP BY
1
)
OR contract_address IN (
SELECT
token_bought_address
FROM
trades
GROUP BY
1
)
)
),
projects AS (
SELECT
DISTINCT exchange_contract_address,
proj_v,
pair,
pool,
token_bought_address,
token_sold_address,
token_bought_symbol,
token_sold_symbol
FROM
trades
),
gs AS (
SELECT
DISTINCT d.token_bought_address AS token,
gs.hour
FROM
trades d
INNER JOIN (
SELECT
explode(sequence(to_date('2021-05-05'), date_trunc('day', now()), interval 1 day)) as hour
) gs -- Generate all days since the first contract
ON 1 = 1
UNION
SELECT
DISTINCT d.token_sold_address AS token,
gs.hour
FROM
trades d
INNER JOIN (
SELECT
explode(sequence(to_date('2021-05-05'), date_trunc('day', now()), interval 1 day)) as hour
) gs -- Generate all days since the first contract
ON 1 = 1
),
tokens_in_prices_usd AS (
SELECT DISTINCT contract_address
FROM prices.usd
WHERE minute > now() - interval 10 minutes
),
dex_trades as(
SELECT
token_bought_address as contract_address,
coalesce(amount_usd/token_bought_amount, amount_usd/(token_bought_amount_raw*power(1/10,decimals))) AS price,
block_time
FROM dex.trades
LEFT JOIN tokens.erc20 ON contract_address = token_bought_address
WHERE 1=1
AND amount_usd > 0
AND token_bought_amount_raw > 0
AND token_bought_address NOT IN (SELECT contract_address FROM tokens_in_prices_usd)
UNION ALL
SELECT
token_sold_address as contract_address,
coalesce(amount_usd/token_sold_amount, amount_usd/(token_sold_amount_raw*power(1/10,decimals))) AS price,
block_time
FROM dex.trades
LEFT JOIN tokens.erc20 ON contract_address = token_sold_address
WHERE 1=1
AND amount_usd > 0
AND token_sold_amount_raw > 0
AND token_sold_address NOT IN (SELECT contract_address FROM tokens_in_prices_usd)
),
view_token_prices as (
SELECT
date_trunc('hour', block_time) as hour,
contract_address,
(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price)) AS median_price,
count(1) AS sample_size
FROM dex_trades
GROUP BY 1, 2
),
dex_price AS(
--for tokens where dune doesn't have the price, calculate the avg price in it's last hour of swaps (6 hr if no swaps in the last hour)
--since we don't have an amount field for unmapped tokens, I'll calc it based on... raw amount
-- through a few queries, this gets the unit price of each token (assuming 18 decimals)
-- DO NOT USE THIS FOR KNOWN TOKENS - only for the ones we don't have, we'll assume 18 decimals
SELECT
gs.hour,
gs.token,
symbol,
decimals,
median_price
FROM
(
SELECT
*,
lead(hour, 1, NOW()) OVER (
PARTITION BY token
ORDER BY
hour asc
) AS next_hour
FROM
(
SELECT
DATE_TRUNC('day', hour) AS hour,
t.contract_address AS token,
ea.symbol AS symbol,
ea.decimals AS decimals,
percentile_cont(0.5) WITHIN GROUP (
ORDER BY
median_price
) AS median_price
FROM
view_token_prices t
INNER JOIN (
SELECT
token
FROM
gs
GROUP BY
1
) c ON t.contract_address = c.token
INNER JOIN tokens.erc20 ea --both need to have known decimals, we're not going to assume anything.
ON ea.contract_address = t.contract_address
WHERE
(ea.decimals IS NOT NULL)
AND median_price > 0
AND DATE_TRUNC('day', hour) > '2021-05-05'
AND DATE_TRUNC('day', hour) <= NOW() + interval 1 day
GROUP BY
1,
2,
3,
4
) b
) c
INNER JOIN gs ON c.hour <= gs.hour
AND gs.hour < c.next_hour
AND gs.token = c.token
),
tvl AS (
SELECT
DISTINCT dt,
exchange_contract_address,
SUM(delta_tvl) OVER (PARTITION BY dt, exchange_contract_address) AS delta_tvl,
SUM(add_tvl) OVER (PARTITION BY dt, exchange_contract_address) AS add_tvl,
LAST_VALUE(running_tvl) OVER (
PARTITION BY dt,
exchange_contract_address
ORDER BY
hour ASC ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS running_tvl
FROM
(
SELECT
hour,
DATE_TRUNC('day', hour) AS dt,
exchange_contract_address,
COALESCE(SUM(val_usd), 0) AS delta_tvl,
COALESCE(SUM(addval_usd), 0) AS add_tvl,
COALESCE(SUM(sumval_usd), 0) AS running_tvl
FROM
(
SELECT
gs.hour AS hour,
gs.token,
exchange_contract_address,
CASE
WHEN gs.hour = c.hour THEN val
ELSE 0
END AS val,
CASE
WHEN gs.hour = c.hour THEN val*power(1/10,COALESCE(p.decimals, dp.decimals))
ELSE 0
END AS val_tokens,
CASE
WHEN gs.hour = c.hour THEN COALESCE(p.price, dp.median_price) * (val *power(1/10, COALESCE(p.decimals, dp.decimals)))
ELSE 0
END AS val_usd,
CASE
WHEN gs.hour = c.hour THEN addval
ELSE 0
END AS addval,
CASE
WHEN gs.hour = c.hour THEN addval * power(1/10 ,COALESCE(p.decimals, dp.decimals))
ELSE 0
END AS addval_tokens,
CASE
WHEN gs.hour = c.hour THEN COALESCE(p.price, dp.median_price) * (addval * power(1/10,COALESCE(p.decimals, dp.decimals)))
ELSE 0
END AS addval_usd,
sumval,
sumval * power(1/10,COALESCE(p.decimals, dp.decimals)) AS sumval_tokens,
COALESCE(p.price, dp.median_price) * (sumval * power(1/10,COALESCE(p.decimals, dp.decimals))) AS sumval_usd
FROM
(
SELECT
*,
lead(hour, 1, NOW()) OVER (
PARTITION BY contract_address,
exchange_contract_address
ORDER BY
hour asc
) AS next_hour,
SUM(val) OVER (
PARTITION BY contract_address,
exchange_contract_address
ORDER BY
hour ASC
) AS sumval
FROM
(
SELECT
hour,
contract_address,
exchange_contract_address,
SUM(val) AS val,
SUM(
CASE
WHEN val > 0 THEN val
ELSE 0
END
) AS addval --SUM(val) OVER (PARTITION BY contract_address, exchange_contract_address ORDER BY hour ASC) AS sumval
--SUM( price*(val/(10^decimals)) ) AS usd_tvl
FROM
(
SELECT
DATE_TRUNC('day', e.evt_block_time) AS hour,
to AS exchange_contract_address,
e.contract_address,
SUM(value) AS val
FROM
erc20_ethereum.evt_Transfer e
WHERE
to IN (
SELECT
exchange_contract_address
FROM
trades
GROUP BY
1
)
GROUP BY
1,
2,
3
UNION ALL
SELECT
DATE_TRUNC('day', eb.evt_block_time) AS hour,
from AS exchange_contract_address,
eb.contract_address,
SUM(- value) AS val
FROM
erc20_ethereum.evt_Transfer eb
WHERE
from IN (
SELECT
exchange_contract_address
FROM
trades
GROUP BY
1
)
GROUP BY
1,
2,
3
) tr
GROUP BY
1,
2,
3
) mod
) c
INNER JOIN gs ON c.hour <= gs.hour
AND gs.hour < c.next_hour
AND gs.token = c.contract_address
LEFT JOIN prices p ON p.contract_address = gs.token
AND CASE
WHEN DATE_TRUNC('day', gs.hour) = DATE_TRUNC('day', NOW()) THEN p.minute = DATE_TRUNC('day', NOW())
ELSE p.minute = DATE_TRUNC('day', gs.hour) + interval 1 day
END
LEFT JOIN dex_price dp ON gs.token = dp.token
AND dp.hour = gs.hour
AND p.price IS NULL
WHERE
gs.hour > '2021-05-05'
) a
GROUP BY
1,
2,
3
) b
)
SELECT
tvl.dt,
--pr.proj_v,
COALESCE(tr.usd_traded, 0) AS usd_traded,
tvl.running_tvl,
COALESCE(tr.num_trades, 0) AS num_trades,
tvl.delta_tvl,
tvl.add_tvl,
'<a href=https://info.uniswap.org/#/pools/' || REPLACE(
tvl.exchange_contract_address,
'\\',' 0 ') || ' >' || REPLACE(tvl.exchange_contract_address,'\\','0') || '</a>' AS pool_url,
CASE WHEN tvl.exchange_contract_address = '0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8' THEN '0.30%'
WHEN tvl.exchange_contract_address = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' THEN '0.05%'
WHEN tvl.exchange_contract_address = '0x7bea39867e4169dbe237d55c8242a8f2fcdcc387' THEN '1%' END AS uni_pool,
SUM(usd_traded) OVER (PARTITION BY tvl.exchange_contract_address ORDER BY tvl.dt ASC) AS running_usd,
SUM(num_trades) OVER (PARTITION BY tvl.exchange_contract_address ORDER BY tvl.dt ASC) AS running_trades,
pr.pair,
pr.pool,
pr.token_bought_address,
pr.token_sold_address,
pr.token_bought_symbol,
pr.token_sold_symbol
FROM tvl
INNER JOIN projects pr
ON pr.exchange_contract_address = tvl.exchange_contract_address
LEFT JOIN trades tr
ON tr.exchange_contract_address = tvl.exchange_contract_address
AND tr.dt = tvl.dt
ORDER BY dt DESC --this is important for counters第二种算法：根据trades中的 amount_usd进行计算
WITH
trades as ( -- uniswap_v3 trades data
select
block_date as dt
,project
,version
,project_contract_address
,token_bought_address
,token_sold_address
,token_bought_symbol
,token_sold_symbol
,token_pair as pair
,sum(token_bought_amount) as token_bought_amount
,sum(token_sold_amount) as token_b_amount
,sum(amount_usd) as usd_traded
,count(amount_usd) as num_trades
,count(distinct taker) as num_users
from uniswap_v3_ethereum.trades
where block_date >= '2021-05-05'
and project_contract_address in (
'0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8',
'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640',
'0x7bea39867e4169dbe237d55c8242a8f2fcdcc387'
)
group by
block_date
,project
,version
,project_contract_address
,token_bought_address
,token_sold_address
,token_bought_symbol
,token_sold_symbol
,token_pair
) ,
dex_price as(
select
date_trunc('hour', block_time) as hour,
contract_address,
decimals,
symbol,
(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY price)) AS median_price,
count(1) AS sample_size
from
(
select
t1.token_bought_address as contract_address
,t2.decimals
,t2.symbol
,coalesce(t1.amount_usd/t1.token_bought_amount, t1.amount_usd/(t1.token_bought_amount_raw*power(10,t2.decimals))) as price
,t1.block_time
from (
select
block_time
,token_bought_address
,token_bought_amount
,amount_usd
,token_bought_amount_raw
from dex.trades
where amount_usd>0 and token_bought_amount_raw > 0
) t1
left join
(
select
blockchain
,contract_address
,decimals
,symbol
from tokens.erc20
) t2
on(t1.token_bought_address = t2.contract_address)
left join
(
select distinct contract_address
from prices.usd
where minute > now() - interval '10 minutes'
)t3
on(t1.token_bought_address = t3.contract_address)
where t3.contract_address is null
union all
select
t4.token_sold_address as contract_address,
t5.decimals,
t5.symbol,
coalesce(t4.amount_usd/t4.token_sold_amount, t4.amount_usd/(t4.token_sold_amount_raw*power(10,t5.decimals))) as price,
t4.block_time
from (
select
block_time
,token_sold_address
,token_sold_amount
,amount_usd
,token_sold_amount_raw
from dex.trades
where amount_usd>0 and token_sold_amount_raw > 0
) t4
left join
(
select
blockchain
,contract_address
,decimals
,symbol
from tokens.erc20
) t5
on(t4.token_sold_address = t5.contract_address)
left join
(
select distinct contract_address
from prices.usd
where minute > now() - interval '10 minutes'
)t6
on(t4.token_sold_address = t6.contract_address)
where t6.contract_address is null
) t7
group by date_trunc('hour', block_time),contract_address,decimals,symbol
),
tvl as (
select
dt,
exchange_contract_address,
sum(delta_tvl) over (partition by dt, exchange_contract_address) as delta_tvl,
sum(add_tvl) over (partition by dt, exchange_contract_address) AS add_tvl,
last_value(running_tvl) over (partition by dt,exchange_contract_address
order by hour asc rows between unbounded preceding and unbounded following) as running_tvl
from (
select
hour,
DATE_TRUNC('day', hour) as dt,
exchange_contract_address,
coalesce(sum(val_usd), 0) as delta_tvl,
coalesce(sum(addval_usd), 0) as add_tvl,
coalesce(sum(sumval_usd), 0) as running_tvl
from(
-- 根据不同时间 不同的价格计算 以太币转换为 美元价值
select
hour,
exchange_contract_address,
val,
0 as val_tokens,
val_usd,
addval,
0 as addval_tokens,
addval_usd,
sumval,
0 as sumval_tokens,
sumval_usd
from(
select
hour,
contract_address,
exchange_contract_address,
val,
addval, -- 交易流入
val_usd,
addval_usd,
lead(hour, 1, now()) over (partition by contract_address,exchange_contract_address order by hour asc) as next_hour,
sum(val) over (partition by contract_address,exchange_contract_address order by hour asc) as sumval, -- 累计求和值
sum(val_usd) over (partition by contract_address,exchange_contract_address order by hour asc) as sumval_usd
from (
select
t1.hour,
t1.contract_address,
t1.exchange_contract_address,
sum(t1.val) as val,
sum(case when t1.val > 0 then t1.val else 0 end) as addval,
sum(t1.amount_usd) as val_usd,
sum(case when t1.amount_usd > 0 then t1.amount_usd else 0 end) as addval_usd
from
(
select
DATE_TRUNC('day', block_time) as hour,
tx_to as exchange_contract_address,
project_contract_address as contract_address,
sum(amount_usd) as val,
sum(amount_usd) as amount_usd
from uniswap_v3_ethereum.trades
where block_date >= '2021-05-05'
and project_contract_address in (
'0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8',
'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640',
'0x7bea39867e4169dbe237d55c8242a8f2fcdcc387'
)
group by DATE_TRUNC('day', block_time), tx_to,contract_address
) t1
group by t1.hour,
t1.exchange_contract_address,
t1.contract_address
union all
select
t3.hour,
t3.contract_address,
t3.exchange_contract_address,
sum(t3.val) AS val,
sum(case when t3.val > 0 then t3.val else 0 end) as addval,
sum(t3.amount_usd) AS val_usd,
sum(case when t3.amount_usd > 0 then t3.amount_usd else 0 end) as addval_usd
from
(
select
DATE_TRUNC('day', block_time) as hour,
tx_from as exchange_contract_address,
project_contract_address as contract_address,
sum(-amount_usd) as val,
sum(-amount_usd) as amount_usd
from uniswap_v3_ethereum.trades
where block_date >= '2021-05-05'
and project_contract_address in (
'0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8',
'0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640',
'0x7bea39867e4169dbe237d55c8242a8f2fcdcc387'
)
group by DATE_TRUNC('day', block_time), tx_from,contract_address
) t3
group by t3.hour,
t3.exchange_contract_address,
t3.contract_address
)t4
)t5
) t16
group by hour,
DATE_TRUNC('day', hour),
exchange_contract_address
)t17
)
select
tvl.dt
,coalesce(trades.usd_traded, 0) as usd_traded
,tvl.running_tvl
,coalesce(trades.num_trades, 0) as num_trades
,tvl.delta_tvl
,tvl.add_tvl
,trades.pair
,trades.token_bought_address
,trades.token_sold_address
,trades.token_bought_symbol
,trades.token_sold_symbol
,case when tvl.exchange_contract_address = '0x8ad599c3a0ff1de082011efddc58f1908eb6e6d8' then '0.30%'
when tvl.exchange_contract_address = '0x88e6a0c2ddd26feeb64f039a2c41296fcb3f5640' then '0.05%'
when tvl.exchange_contract_address = '0x7bea39867e4169dbe237d55c8242a8f2fcdcc387' then '1%' end as uni_pool
,sum(trades.usd_traded) over (partition by tvl.exchange_contract_address order by tvl.dt asc) as running_usd
,sum(trades.num_trades) over (partition by tvl.exchange_contract_address order by tvl.dt asc) as running_trades
from tvl
left join trades
on trades.project_contract_address = tvl.exchange_contract_address
and trades.dt = tvl.dt
order by dt desc
zhangmaosen
纬度：时间、钱包地址、币对儿 Metric：tvl，成交量、流动性佣金提成，gas费 fyi：https://www.bitpush.news/articles/3154196