uniswap流动性套利分析的数据表设计 #15
Assignees
Comments
|
tvl计算: 将以太币转换为美元价值：不同的时间，不同的价格来计算 --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) 对于价格，确实有这几种方式 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
|
|
fee 流动性佣金提成 计算：trades.amount_usd * (poolCreated.fee / 1e6) as fees_collected_usd select
block_date
,sum(fees_collected_usd) as fees
,sum(protocol_buySell * (markout5m - swapPrice) * eth_swapped) as pnl_5m
,sum(protocol_buySell * (markout24h - swapPrice) * eth_swapped) as pnl_24h
,sum(protocol_buySell * (markout1h - swapPrice) * eth_swapped) as pnl_1h
,sum(case when protocol_buySell * (markout5m - swapPrice) * eth_swapped >= 0 then swapPrice * eth_swapped else 0 end) as pnl_5m_positive
,sum(case when protocol_buySell * (markout5m - swapPrice) * eth_swapped < 0 then swapPrice * eth_swapped else 0 end) as pnl_5m_negative
,sum(case when protocol_buySell * (markout1h - swapPrice) * eth_swapped >= 0 then swapPrice * eth_swapped else 0 end) as pnl_1h_positive
,sum(case when protocol_buySell * (markout1h - swapPrice) * eth_swapped < 0 then swapPrice * eth_swapped else 0 end) as pnl_1h_negative
,sum(swapPrice * eth_swapped) as volume
from (
select
trades.block_time
,trades.block_date
,trades.project_contract_address -- exchange_contract_address is the v3 LP Position
,trades.tx_hash -- transaction addr (for spot checks)
,trades.amount_usd
,trades.swapPrice
,trades.protocol_buySell
,trades.markout1h
,trades.markout5m
,trades.markout24h
,trades.eth_swapped
,poolCreated.fee / 1e6 as fee_tier
,trades.amount_usd * (poolCreated.fee / 1e6) as fees_collected_usd
from
(
select
block_time
,block_date
,project_contract_address -- exchange_contract_address is the v3 LP Position
,tx_hash -- transaction addr (for spot checks)
,amount_usd
,case when token_sold_symbol = 'USDC' then token_sold_amount / token_bought_amount
when token_sold_symbol = 'WETH' then token_bought_amount / token_sold_amount
end as swapPrice
,case when token_sold_symbol = 'WETH' then 1 when token_sold_symbol = 'USDC' then -1
end as protocol_buySell
,last_value( case when token_sold_symbol = 'USDC' then token_sold_amount / token_bought_amount
when token_sold_symbol = 'WETH' then token_bought_amount / token_sold_amount end
) over (order by block_time asc range between current row and interval '1' hour following
) as markout1h
,last_value(
case when token_sold_symbol = 'USDC' then token_sold_amount / token_bought_amount
when token_sold_symbol = 'WETH' then token_bought_amount / token_sold_amount
end) over (order by block_time asc range between current row and interval '5' minutes following
) as markout5m
,last_value(
case when token_sold_symbol = 'USDC' then token_sold_amount / token_bought_amount
when token_sold_symbol = 'WETH' then token_bought_amount / token_sold_amount
end) OVER (order by block_time asc range between current row and interval '24' hours following
) as markout24h
,case
when token_sold_symbol = 'WETH' then token_sold_amount
when token_bought_symbol = 'WETH' then token_bought_amount
else 0
end as eth_swapped
from uniswap_v3_ethereum.trades
where block_date >= '2021-05-05'
and (token_bought_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
and token_sold_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
or (token_bought_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
and token_sold_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
)
)
and case when token_sold_symbol = 'USDC' then token_sold_amount / token_bought_amount
when token_sold_symbol = 'WETH' then token_bought_amount / token_sold_amount
end between 500 and 5000
) trades
left join
(
select
contract_address,
evt_block_number,
evt_block_time,
evt_index,
evt_tx_hash,
fee,
pool,
tickSpacing,
token0,
token1
from uniswap_v3_ethereum.Factory_evt_PoolCreated
) poolCreated
on(trades.project_contract_address = poolCreated.pool)
) t1
group by block_date
order by block_date desc
|
|
https://zhuanlan.zhihu.com/p/426860108
这里面提到了对于价格处理的三种办法，我估计我们要出三个变种计算逻辑了
zexianghuang ***@***.***>于2022年10月12日 周三20:33写道：
… tvl计算：
select
t2.block_date
,t2.hour
,t2.project_contract_address
,t2.exchange_contract_address
,COALESCE(sum(t2.amount_usd), 0) AS delta_tvl
,COALESCE(sum(t2.addval_usd), 0) AS add_tvl
,COALESCE(sum(t2.sumval_usd), 0) AS running_tvl
from
(
select
t1.block_date
,t1.hour
,t1.project_contract_address
,t1.exchange_contract_address
,t1.amount_usd
,case when t1.amount_usd >0 then t1.amount_usd else 0 end as addval_usd
,sum(amount_usd) OVER ( PARTITION BY
project_contract_address,exchange_contract_address ORDER BY hour ASC ) AS
sumval_usd
from
(
select
block_date
,DATE_TRUNC('day',block_time) AS hour
,project_contract_address
,tx_to as exchange_contract_address
,sum(amount_usd) as amount_usd
from uniswap_v3_ethereum.trades
group by
block_date,DATE_TRUNC('day',block_time),project_contract_address,tx_to
union all
select
block_date
,DATE_TRUNC('day',block_time) AS hour
,project_contract_address
,tx_from as exchange_contract_address
,sum(-amount_usd) as amount_usd
from uniswap_v3_ethereum.trades
group by block_date,DATE_TRUNC('day',block_time),project_contract_address,tx_from
) t1
)t2
group by t2.block_date
,t2.hour
,t2.project_contract_address
,t2.exchange_contract_address
—
Reply to this email directly, view it on GitHub
<#15 (comment)>,
or unsubscribe
<https://github.com/notifications/unsubscribe-auth/AAH7YM3ELVKQ7R2NN4TGHFTWC2VZZANCNFSM6AAAAAARB3S7L4>
.
You are receiving this because you were assigned.Message ID:
***@***.***>
|
对于价格，确实有这几种方式 |
|
明细数据查询： select
trades.block_time
,trades.block_date
,trades.project_contract_address -- exchange_contract_address is the v3 LP Position
,trades.tx_hash -- transaction addr (for spot checks)
,trades.amount_usd
,trades.swapPrice
,trades.eth_swapped
,poolCreated.fee / 1e6 as fee_tier
,trades.amount_usd * (poolCreated.fee / 1e6) as fees_collected_usd
,gas.tx_fee_native
,gas.tx_fee_usd
,(trades.swapPrice * trades.eth_swapped)as volume
,trades.token_sold_symbol
,trades.token_bought_symbol
,trades.token_bought_amount
,trades.token_sold_amount
,trades.token_bought_address
,trades.token_sold_address
from (
select
block_time
,block_date
,project_contract_address -- exchange_contract_address is the v3 LP Position
,tx_hash -- transaction addr (for spot checks)
,amount_usd
,case when token_sold_symbol = 'USDC' then token_sold_amount / token_bought_amount
when token_sold_symbol = 'WETH' then token_bought_amount / token_sold_amount
end as swapPrice
,case
when token_sold_symbol = 'WETH' then token_sold_amount
when token_bought_symbol = 'WETH' then token_bought_amount
else 0
end as eth_swapped
,token_sold_symbol
,token_bought_symbol
,token_bought_amount
,token_sold_amount
,token_bought_address
,token_sold_address
from uniswap_v3_ethereum.trades
where block_date >= '2021-05-05'
and (token_bought_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
and token_sold_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
or (token_bought_address = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48'
and token_sold_address = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2'
)
)
and case when token_sold_symbol = 'USDC' then token_sold_amount / token_bought_amount
when token_sold_symbol = 'WETH' then token_bought_amount / token_sold_amount
end between 500 and 5000
)trades
left join
(
select
contract_address,
evt_block_number,
evt_block_time,
evt_index,
evt_tx_hash,
fee,
pool,
tickSpacing,
token0,
token1
from uniswap_v3_ethereum.Factory_evt_PoolCreated
) poolCreated
on(trades.project_contract_address = poolCreated.pool)
left join
(
select
tx_hash
,tx_fee_native
,tx_fee_usd
from gas.fees
) gas
on(trades.tx_hash = gas.tx_hash) |
|
汇总统计： WITH
trades AS (
SELECT
DATE_TRUNC('day', t.block_time) AS dt,
t.block_time,
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,
SUM(t.amount_usd * (p.fee / 1e6)) as fees_collected_usd,
sum(case when token_sold_symbol = 'USDC' then token_sold_amount / token_bought_amount
when token_sold_symbol = 'WETH' then token_bought_amount / token_sold_amount
end
*
case when token_sold_symbol = 'WETH' then token_sold_amount
when token_bought_symbol = 'WETH' then token_bought_amount
else 0 end
) as volume
--SUM(tx_fee_native) as gas_fee_native,
---SUM(tx_fee_usd) as gas_fee_usd
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,
12
),
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,
evt_block_time as block_time,
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,
evt_block_time,
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,
evt_block_time,
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,
evt_block_time,
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,
e.evt_block_time,
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,
4
UNION ALL
SELECT
DATE_TRUNC('day', eb.evt_block_time) AS hour,
eb.evt_block_time,
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,
4
) tr
GROUP BY
1,
2,
3,
4
) 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,
4
) b
)
SELECT
tvl.block_time,
pr.pair,
pr.pool,
tvl.exchange_contract_address,
pr.token_bought_address,
pr.token_sold_address,
pr.token_bought_symbol,
pr.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,
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,
tr.fees_collected_usd,
tr.volume,
SUM(tr.usd_traded) OVER (PARTITION BY tvl.exchange_contract_address ORDER BY tvl.dt ASC) AS running_usd,
SUM(tr.num_trades) OVER (PARTITION BY tvl.exchange_contract_address ORDER BY tvl.dt ASC) AS running_trades
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
纬度：时间、钱包地址、币对儿
Metric：tvl，成交量、流动性佣金提成，gas费
fyi：https://www.bitpush.news/articles/3154196
The text was updated successfully, but these errors were encountered: