theericstoneTVL by Hour by Pool V3
Updated 2021-07-16
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 hourly as (
SELECT
DISTINCT date_trunc('hour',pstat.block_timestamp) as block_hour,
pstat.pool_address,
pool_name,
last_value( VIRTUAL_RESERVES_TOKEN0_ADJUSTED * pow(VIRTUAL_RESERVES_TOKEN1_ADJUSTED,-1) ) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as native_price0,
last_value( VIRTUAL_RESERVES_TOKEN1_ADJUSTED * pow(VIRTUAL_RESERVES_TOKEN0_ADJUSTED,-1) ) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as native_price1,
last_value(tph0.price) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as price0,
last_value(tph1.price) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as price1,
last_value(token0_balance_adjusted) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as gross_reserves_token0_adjusted,
last_value(token1_balance_adjusted) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as gross_reserves_token1_adjusted,
last_value( sqrt(virtual_reserves_token0_usd * virtual_reserves_token1_usd) ) OVER (PARTITION BY block_hour, pool_address, pool_name ORDER BY block_timestamp) as virtual_liquidity,
price0 * gross_reserves_token0_adjusted as token0_gross_usd,
price1 * gross_reserves_token1_adjusted as token1_gross_usd,
CASE WHEN price0 IS NULL and price1 IS NULL THEN 'no prices'
WHEN price0 IS NULL and price1 IS NOT NULL THEN 'price1'
WHEN price1 IS NULL and price0 IS NOT NULL THEN 'price0'
ELSE 'both prices'
END AS price_status
FROM uniswapv3.pool_stats pstat
LEFT JOIN ethereum.token_prices_hourly tph0
ON tph0.hour = date_trunc('hour',pstat.block_timestamp)
AND pstat.token0_address = tph0.token_address
LEFT JOIN ethereum.token_prices_hourly tph1
ON tph1.hour = date_trunc('hour',pstat.block_timestamp)
AND pstat.token1_address = tph1.token_address
WHERE pstat.block_timestamp >= getdate() - interval '1 days'
Run a query to Download Data