--Alrighty, this unfortunately may have less tokens than the FACT_HOURLY_TOKEN_PRICES -- my suspicisions are that some tokens are so depricated/have so little user base that -- there exist no pools onchain trading for those tokens are probably only conducted on CEX -- one example of this being 0x946551dd05c5abd7cc808927480225ce36d8c475 WITH usdc AS (SELECT pool_name, pool_address, CASE WHEN token0 = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' THEN token0 ELSE token1 END AS currency0, CASE WHEN token1 = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' THEN token0 ELSE token1 END AS currency1, platform, 'usdc' AS paired_against FROM ethereum.core.dim_dex_liquidity_pools WHERE (token0 = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' OR token1 = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') AND ( --WETH currency1 <> '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' --USDT AND currency1 <> '0xdac17f958d2ee523a2206206994597c13d831ec7')), usdt AS (SELECT pool_name, pool_address, CASE WHEN token0 = '0xdac17f958d2ee523a2206206994597c13d831ec7' THEN token0 ELSE token1 END AS currency0, CASE WHEN token1 = '0xdac17f958d2ee523a2206206994597c13d831ec7' THEN token0 ELSE token1 END AS currency1, platform, 'usdt' AS paired_against FROM ethereum.core.dim_dex_liquidity_pools WHERE (token0 = '0xdac17f958d2ee523a2206206994597c13d831ec7' OR token1 = '0xdac17f958d2ee523a2206206994597c13d831ec7') AND ( --WETH currency1 <> '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' --USDC AND currency1 <> '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48')), weth AS (SELECT pool_name, pool_address, CASE WHEN token0 = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN token0 ELSE token1 END AS currency0, CASE WHEN token1 = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN token0 ELSE token1 END AS currency1, platform, 'weth' AS paired_against FROM ethereum.core.dim_dex_liquidity_pools WHERE (token0 = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' OR token1 = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') AND ( --usdt currency1 <> '0xdac17f958d2ee523a2206206994597c13d831ec7' --USDC AND currency1 <> '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48')), base_pairs AS ( SELECT pool_name, pool_address, token0 AS currency0, token1 AS currency1, platform, 'base pairs' AS paired_against FROM ethereum.core.dim_dex_liquidity_pools -- WETH, USDT & USDC contracts WHERE (token0 = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' OR token0 = '0xdac17f958d2ee523a2206206994597c13d831ec7' OR token0 = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') AND (token1 = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' OR token1 = '0xdac17f958d2ee523a2206206994597c13d831ec7' OR token1 = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48')), all_pools AS ( SELECT * FROM weth UNION SELECT * FROM usdt UNION SELECT * FROM usdc UNION SELECT * FROM base_pairs), non_base AS (SELECT pool_name, pool_address, token0 as currency0, token1 AS currency1, platform, 'non-base pairs' AS paired_against FROM ethereum.core.dim_dex_liquidity_pools WHERE pool_address NOT IN (SELECT pool_address FROM all_pools)), complete_pools AS ( SELECT * FROM non_base UNION SELECT * FROM all_pools), --Take base pools to determine the price of WETH weth_stable_pools AS ( SELECT * FROM complete_pools WHERE paired_against = 'base pairs' AND (CURRENCY0 = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' OR currency1 = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')), calc_eth_vol AS ( SELECT contract_address, CASE WHEN token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN SUM(amount_in) ELSE SUM(amount_out) END AS eth_volume FROM ethereum.CORE.EZ_DEX_SWAPS WHERE contract_address IN (SELECT pool_address FROM weth_stable_pools) AND block_timestamp > CURRENT_DATE - 14 GROUP BY contract_address, token_in), eth_vol AS ( SELECT contract_address, SUM(eth_volume) AS vol FROM calc_eth_vol GROUP BY contract_address), eth_prices AS ( SELECT tx_hash, date_trunc('hour',block_timestamp) AS date, pool_name, '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' AS token_address, 'WETH' AS symbol, CASE WHEN token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN amount_out / amount_in ELSE amount_in / amount_out END AS price FROM ethereum.CORE.EZ_DEX_SWAPS WHERE --This grabs the Stablecoin/ETH pool with highest vol to be used -- to calc the price of ETH contract_address IN (SELECT top 1 contract_address FROM eth_vol ORDER BY vol DESC) ), --Finally calculate the price of ETH eth_prices_hourly AS ( SELECT date,token_address, symbol, MAX(price) AS hourly_high, MIN(price) AS hourly_low, AVG(price) AS hourly_avg_price FROM eth_prices GROUP BY 1,2,3), get_weth_pools AS ( --Now that there are ETH Prices, we can use that to determine the prices of all assets paired against eth SELECT * FROM complete_pools WHERE paired_against = 'weth' ), get_weth_pool_vol AS ( SELECT contract_address, CASE WHEN token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN token_out ELSE token_in END AS token_address, CASE WHEN token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN symbol_out ELSE symbol_in END AS symbol, CASE WHEN token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN SUM(amount_in) ELSE SUM(amount_out) END AS eth_volume FROM ethereum.CORE.EZ_DEX_SWAPS WHERE contract_address IN (SELECT pool_address FROM get_weth_pools) AND block_timestamp > CURRENT_DATE - 14 GROUP BY contract_address, token_in, token_out, symbol_in, symbol_out), -- If there are multiple TokenX/WETH pools, select the pool with the most vol -- that pool will then be used to calc the price of TokenX weth_pool_vol AS ( SELECT t.* FROM get_weth_pool_vol t WHERE t.eth_volume = (SELECT MAX(t2.eth_volume) FROM get_weth_pool_vol t2 WHERE t2.token_address = t.token_address) ), --Grabs all swap in pools of TokenX/WETH to calc token price all_weth_swaps AS ( SELECT tx_hash, block_timestamp, date_trunc('hour',block_timestamp) AS date, contract_address, pool_name, CASE WHEN token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN token_out ELSE token_in END AS token_address, CASE WHEN token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN symbol_out ELSE symbol_in END AS symbol, CASE WHEN token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN amount_out / amount_in ELSE amount_in / amount_out END AS price FROM ethereum.core.EZ_DEX_SWAPS WHERE contract_address IN (SELECT contract_address FROM weth_pool_vol) ), -- Row numbers are needed to determine the opening/closing prices on the hour add_row_num AS ( SELECT *, ROW_NUMBER() over (PARTITION BY date,contract_address ORDER BY block_timestamp ASC) row_num FROM all_weth_swaps ), --Calcs Standard Deviation to remove outliers add_open_close AS ( SELECT *, first_value(price) over(PARTITION BY date,contract_address ORDER BY row_num) f1, last_value(price) over(PARTITION BY date,contract_address ORDER BY row_num) l1, STDDEV(price) over (PARTITION BY date,contract_address) AS deviation, AVG(price) over (PARTITION BY date, contract_address) AS avg_price, avg_price + (2*deviation) AS upper, avg_price - (2*deviation) AS lower FROM add_row_num ), --Outliers be gone remove_outliers AS ( SELECT * FROM add_open_close --Removes outliers, plus/minus 2SD WHERE price < upper AND price > lower ), weth_token_prices AS ( SELECT date, contract_address,token_address, symbol, MAX(price) AS hourly_high, MIN(price) AS hourly_low, AVG(price) AS hourly_avg_price, -- f1 -> open price -- l1 -> close price f1, l1 FROM remove_outliers GROUP BY date,contract_address, token_address, symbol,f1,l1,deviation ORDER BY date ASC), weth_pool_prices AS ( SELECT tp.date as hour, tp.contract_address AS price_source, tp.token_address, tp.symbol, tp.hourly_high, tp.hourly_low, eph.hourly_avg_price AS eth_price, eth_price / tp.hourly_high AS usd_lows, eth_price / tp.hourly_low AS usd_highs, eth_price / tp.hourly_avg_price AS token_price_usd, eth_price / tp.f1 AS open_usd, eth_price / tp.l1 AS close_usd FROM weth_token_prices tp LEFT OUTER JOIN eth_prices_hourly eph ON eph.date = tp.date), --From here upwards lies the code to get prices for TOKENs in the TOKEN/WETH pools. --Stables should be next and easy peasy stable_pools AS ( SELECT * FROM complete_pools WHERE (paired_against = 'usdt' OR paired_against = 'usdc') AND currency1 <> '0x0000000000000000000000000000000000000000' ), stable_pool_vol AS ( SELECT contract_address, CASE WHEN (token_in = '0xdac17f958d2ee523a2206206994597c13d831ec7' OR token_in = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') THEN token_out ELSE token_in END AS token_address, CASE WHEN (token_in = '0xdac17f958d2ee523a2206206994597c13d831ec7' OR token_in = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') THEN symbol_out ELSE symbol_in END AS symbol, CASE WHEN (token_in = '0xdac17f958d2ee523a2206206994597c13d831ec7' OR token_in = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') THEN SUM(amount_in) ELSE SUM(amount_out) END AS stable_volume FROM ethereum.CORE.EZ_DEX_SWAPS WHERE contract_address IN (SELECT pool_address FROM stable_pools) AND block_timestamp > CURRENT_DATE - 14 GROUP BY contract_address, token_in, token_out, symbol_in, symbol_out), --Same same as before, when token has multiple pools where it's -- paired against a stable, we will use the -- pool with greatest vol to calc the price high_volume_stable_pools AS ( SELECT t.* FROM stable_pool_vol t WHERE t.stable_volume = (SELECT MAX(t2.stable_volume) FROM stable_pool_vol t2 WHERE t2.token_address = t.token_address) ), --Grabs all swap in pools of TokenX/WETH to calc token price all_stable_swaps AS ( SELECT tx_hash, block_timestamp, date_trunc('hour',block_timestamp) AS date, contract_address, pool_name, CASE WHEN (token_in = '0xdac17f958d2ee523a2206206994597c13d831ec7' OR token_in = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') THEN token_out ELSE token_in END AS token_address, CASE WHEN (token_in = '0xdac17f958d2ee523a2206206994597c13d831ec7' OR token_in = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') THEN symbol_out ELSE symbol_in END AS symbol, CASE WHEN (token_in = '0xdac17f958d2ee523a2206206994597c13d831ec7' or token_in = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48') THEN amount_in / amount_out ELSE amount_out / amount_in END AS price FROM ethereum.core.EZ_DEX_SWAPS WHERE contract_address IN (SELECT contract_address FROM high_volume_stable_pools) ), add_row_num_stable AS ( SELECT *, ROW_NUMBER() over (PARTITION BY date,contract_address ORDER BY block_timestamp ASC) row_num FROM all_stable_swaps ), --Calcs Standard Deviation to remove outliers add_open_close_stable AS ( SELECT *, first_value(price) over(PARTITION BY date,contract_address ORDER BY row_num) f1, last_value(price) over(PARTITION BY date,contract_address ORDER BY row_num) l1, STDDEV(price) over (PARTITION BY date,contract_address) AS deviation, AVG(price) over (PARTITION BY date, contract_address) AS avg_price, avg_price + (2*deviation) AS upper, avg_price - (2*deviation) AS lower FROM add_row_num_stable ), remove_outliers_stables AS ( SELECT * FROM add_open_close_stable WHERE price < upper AND price > lower ), stable_token_prices AS ( SELECT date, contract_address,token_address, symbol, MAX(price) AS hourly_high, MIN(price) AS hourly_low, AVG(price) AS hourly_avg_price, f1, l1 FROM remove_outliers_stables GROUP BY date,2,3,4,f1,l1,deviation ORDER BY date ASC), stable_pool_prices AS ( SELECT tp.date as hour, tp.contract_address AS price_source, tp.token_address, tp.symbol, tp.hourly_high, tp.hourly_low, eph.hourly_avg_price AS eth_price, tp.hourly_high AS usd_lows, tp.hourly_low AS usd_highs, tp.hourly_avg_price AS token_price_usd, tp.f1 AS open_usd, tp.l1 AS close_usd FROM stable_token_prices tp LEFT OUTER JOIN eth_prices_hourly eph ON eph.date = tp.date), --Now that we have token prices calc from ETH pools and stable pools -- need to filter out duplciate token entries. If token has pool in both -- ETH and stables the price returnted will be from the pool with the -- most volume in the past 2 weeks stable_and_weth_pools AS ( SELECT * FROM stable_pool_prices UNION SELECT * FROM weth_pool_prices), unique_token_prices AS ( SELECT DISTINCT price_source, token_address, symbol FROM stable_and_weth_pools), stable_weth_pool_vols AS ( SELECT CASE WHEN (token_in = '0xdac17f958d2ee523a2206206994597c13d831ec7' or token_in = '0xa0b86991c6218b36c1d19d4a2e9eb0ce3606eb48' or token_in = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2') THEN token_out ELSE token_in END AS token_address, CONTRACT_ADDRESS, -- I think this is okay - it uses the CoinGecko price but the price here is not used in any price calc sum(amount_in_usd) as volume from ethereum.CORE.EZ_DEX_SWAPS where contract_address IN (SELECT price_source FROM unique_token_prices) and block_timestamp > CURRENT_DATE - 14 group by contract_address, token_address ), -- If there exist a TokenX/WETH pool and TokenX/Stablecoin pool -- select the pool with the greater volume -- FFIW: It seems like doing the price calc for both pool then choosing one -- is less than optimal. Not sure if trying to determine before hand would help with speed grab_correct_pools AS ( SELECT t.* FROM stable_weth_pool_vols t WHERE t.volume = (SELECT MAX(t2.volume) FROM stable_weth_pool_vols t2 WHERE t2.token_address = t.token_address) ), master_prices_v1 AS ( SELECT * FROM stable_and_weth_pools WHERE price_source IN (SELECT contract_address FROM grab_correct_pools)), get_token_token_pool AS ( SELECT pool_name, pool_address, CASE WHEN CURRENCY0 IN (SELECT token_address FROM master_prices_v1) THEN CURRENCY0 ELSE CURRENCY1 END AS base_pair, CASE WHEN CURRENCY0 IN (SELECT token_address FROM master_prices_v1) THEN CURRENCY1 ELSE CURRENCY0 END AS token_pair, platform FROM complete_pools WHERE paired_against = 'non-base pairs' AND (CURRENCY0 IN (SELECT token_address FROM master_prices_v1) OR CURRENCY1 IN (SELECT token_address FROM master_prices_v1)) ), -- if we already calculated the token_pair price in the ETH or Stable pools -- then no need to use compute power to do so again remove_already_calc_prices AS ( SELECT * FROM get_token_token_pool WHERE token_pair NOT IN (SELECT token_address FROM master_prices_v1) AND token_pair <> '0x0000000000000000000000000000000000000000' ), get_token_token_vol AS ( SELECT contract_address, CASE WHEN token_in in (select token_address from master_prices_v1) THEN token_out ELSE token_in END AS token_address, CASE WHEN token_in in (select token_address from master_prices_v1) THEN symbol_out ELSE symbol_in END AS symbol, CASE WHEN token_in in (select token_address from master_prices_v1) THEN SUM(amount_in) -- I think this is alright tbh -- this not used for direct price calc but to just help -- determine which pool I will use. Personally think appending the prices I calc to this -- table to determine volume myself will further slow down the query ELSE SUM(amount_out_usd) END AS total_volume FROM ethereum.CORE.EZ_DEX_SWAPS WHERE contract_address IN (SELECT pool_address FROM remove_already_calc_prices) AND block_timestamp > CURRENT_DATE - 14 GROUP BY contract_address, token_in, token_out, symbol_in, symbol_out), high_volume_token_token as ( SELECT t.* FROM get_token_token_vol t WHERE t.total_volume = (SELECT MAX(t2.total_volume) FROM get_token_token_vol t2 WHERE t2.token_address = t.token_address) ), token_token_swaps AS ( SELECT tx_hash, block_timestamp, date_trunc('hour',block_timestamp) AS date, contract_address, pool_name, CASE WHEN token_in in (select token_address from master_prices_v1) THEN token_out ELSE token_in END AS token_address, CASE WHEN token_in in (select token_address from master_prices_v1) THEN symbol_out ELSE symbol_in END AS symbol, case when token_in in (select token_address from master_prices_v1) THEN amount_out / amount_in ELSE amount_in / amount_out END AS price FROM ethereum.core.EZ_DEX_SWAPS WHERE contract_address IN (SELECT contract_address FROM high_volume_token_token)), add_row_num_token AS ( SELECT *, ROW_NUMBER() over (PARTITION BY date,contract_address ORDER BY block_timestamp ASC) row_num FROM token_token_swaps ), add_open_close_token AS ( SELECT *, first_value(price) over(PARTITION BY date,contract_address ORDER BY row_num) f1, last_value(price) over(PARTITION BY date,contract_address ORDER BY row_num) l1, STDDEV(price) over (PARTITION BY date,contract_address) AS deviation, AVG(price) over (PARTITION BY date, contract_address) AS avg_price, avg_price + (2*deviation) AS upper, avg_price - (2*deviation) AS lower FROM add_row_num_token ), remove_outliers_token AS ( SELECT * FROM add_open_close_token --Removes outliers, plus/minus 2SD WHERE price < upper AND price > lower ), token_token_prices AS ( SELECT date, contract_address,token_address, symbol, MAX(price) AS hourly_high, MIN(price) AS hourly_low, AVG(price) AS hourly_avg_price, f1, l1 FROM remove_outliers_token GROUP BY date,2,3,4,f1,l1,deviation ORDER BY date ASC), token_token_pool_prices AS ( SELECT tp.date as hour, tp.contract_address AS price_source, tp.token_address, tp.symbol, tp.hourly_high, tp.hourly_low, eph.hourly_avg_price AS eth_price, tp.hourly_high AS usd_lows, tp.hourly_low AS usd_highs, tp.hourly_avg_price AS token_price_usd, tp.f1 AS open_usd, tp.l1 AS close_usd FROM token_token_prices tp LEFT OUTER JOIN eth_prices_hourly eph ON eph.date = tp.date), master_prices_v2 AS ( SELECT * FROM master_prices_v1 UNION SELECT * FROM token_token_pool_prices) -- To get specific date range filter by 'hour' -- To get specific coin filter by token_address SELECT * FROM master_prices_v2 WHERE token_address = '0x4d224452801aced8b2f0aebe155379bb5d594381' ORDER BY hour ASC

nhat