WITH prices AS ( SELECT * FROM ethereum.token_prices_hourly), swap_raw AS ( SELECT date_trunc('day', swaps.block_timestamp) AS block_hour, pool_name, pool_address, CASE WHEN amount0_adjusted < 0 THEN amount0_adjusted * p0.price ELSE amount1_adjusted * p1.price END AS swap_usd_amount_out, CASE WHEN amount0_adjusted >= 0 THEN amount0_adjusted * p0.price ELSE amount1_adjusted * p1.price END AS swap_usd_amount_in FROM uniswapv3.swaps swaps LEFT JOIN prices p0 ON p0.hour = date_trunc('day', swaps.block_timestamp) AND p0.token_address = swaps.token0_address LEFT JOIN prices p1 ON p1.hour = date_trunc('day', swaps.block_timestamp) AND p1.token_address = swaps.token1_address WHERE swaps.block_timestamp > getdate() - interval '1 days'), volume AS ( SELECT pool_name, pool_address, SUM(swap_usd_amount_in) AS swap_usd_amount_in FROM swap_raw WHERE swap_usd_amount_in is not null GROUP BY 1,2 ORDER BY 3 DESC), --Grabs fee percent pool_fees AS ( SELECT fs.pool_address, vl.swap_usd_amount_in AS vol, fee_percent/100 AS fee FROM uniswapv3.pools fs JOIN volume vl ON fs.pool_address = vl.pool_address), --Gets the most recent price for from the swap pools price_table AS( SELECT pool_name, pool_address, price_0_1, price_1_0, ROW_NUMBER() OVER (Partition By pool_name ORDER BY block_id DESC) rn FROM uniswapv3.swaps), recent_price as ( SELECT pool_name, pool_address, CASE WHEN (price_0_1) < (price_1_0) THEN (price_1_0) ELSE (1/price_1_0) END AS price FROM price_table WHERE price_table.rn = 1 AND price_1_0 > 0), --Gets the sd that is used to calcuate upper/lower bounds --Based on a 3SD of the 5-day average swap price bounds AS ( SELECT sp.pool_address, sp.pool_name, fs.fee, fs.vol, CASE WHEN AVG(price_0_1) < AVG(price_1_0) THEN STDDEV(price_1_0) ELSE STDDEV(1/price_1_0) END as sd FROM uniswapv3.swaps sp JOIN pool_fees fs ON sp.pool_address = fs.pool_address WHERE block_timestamp > getdate() - INTERVAL '5 days' AND price_1_0 > 0 GROUP BY sp.pool_address, sp.pool_name, fs.fee, fs.vol), --The upper and lower bounds for the position bounds_calc AS ( SELECT bd.pool_address, bd.pool_name, bd.fee, bd.vol, bd.sd, rp.price, (rp.price + 3*bd.sd) AS upper, (rp.price - 3*bd.sd) AS lower FROM bounds bd JOIN recent_price rp ON bd.pool_address = rp.pool_address), usd_price AS( SELECT pool_name, pool_address, TOKEN0_BALANCE_USD, TOKEN1_BALANCE_USD, TOKEN0_BALANCE_ADJUSTED, TOKEN1_BALANCE_ADJUSTED, VIRTUAL_LIQUIDITY_ADJUSTED as liq, ROW_NUMBER() OVER (Partition By pool_address ORDER BY block_id DESC) rn FROM uniswapv3.pool_stats), recent_usd_price as ( SELECT pool_name, pool_address, liq, TOKEN0_BALANCE_USD / TOKEN0_BALANCE_ADJUSTED AS token0_price, TOKEN1_BALANCE_USD / TOKEN1_BALANCE_ADJUSTED AS token1_price FROM usd_price WHERE usd_price.rn = 1 AND TOKEN0_BALANCE_ADJUSTED > 0 AND TOKEN1_BALANCE_ADJUSTED > 0), pool_stats as ( Select bc.pool_name, bc.pool_address, bc.fee, bc.vol, bc.price, bc.upper, bc.lower, rp.token0_price, rp.token1_price, rp.liq from bounds_calc bc join recent_usd_price rp on rp.pool_address = bc.pool_address), liquidity AS ( SELECT pool_name, pool_address, fee, vol, price, lower, upper, ((sqrt(price) - sqrt(lower)) / ((1/sqrt(price)) - (1/sqrt(upper)))) AS relation, 1000 / (token0_price + (relation*token1_price)) AS token0_amt_original, 1000 / (token1_price + (relation*token0_price)) AS token1_amt_mod, token0_amt_original * relation AS token1_amt_original, token1_amt_mod * relation as token0_amt_mod, CASE WHEN token0_price < token1_price THEN token0_amt_mod ELSE token0_amt_original END AS token0_amt, CASE WHEN token0_price < token1_price THEN token1_amt_mod ELSE token1_amt_original END AS token1_amt, token0_amt * token0_price AS token0_amt_usd, token1_amt * token1_price AS token1_amt_usd, token0_amt_usd + token1_amt_usd AS check_total_usd, liq, // token0_amt * (sqrt(upper) * sqrt(lower)) / (sqrt(upper) - sqrt(lower)) AS caseOne, // token0_amt * (sqrt(upper) * sqrt(price)) / (sqrt(upper) - sqrt(price)) AS caseTwo0, // token1_amt / (sqrt(price) - sqrt(lower)) AS caseTwo1, // token1_amt / (sqrt(upper) - sqrt(lower)) AS caseThree token0_amt * (sqrt(upper) * sqrt(lower)) / (sqrt(upper) - sqrt(lower)) AS caseOne, token0_amt * (sqrt(upper) * sqrt(price)) / (sqrt(upper) - sqrt(price)) AS caseTwo0, token1_amt / (sqrt(1/price) - sqrt(1/lower)) AS caseTwo1a, token1_amt / (sqrt(price) - sqrt(lower)) AS caseTwo1b, CASE WHEN token0_price < token1_price then caseTwo1a else caseTwo1b end as caseTwo1, token1_amt / (sqrt(upper) - sqrt(lower)) AS caseThree FROM pool_stats WHERE token0_price is not null AND token1_price is not null AND lower > 0 AND upper > 0 AND lower > 0 ORDER BY price ASC), fee_estimation as ( SELECT pool_name, pool_address, fee, price, lower, upper, token0_amt, token1_amt, token0_amt_usd, token1_amt_usd, vol AS volume_24hours, --Get the liqudity share here --Case 1: cprice <= lower --> L = amt0 * (sqrt(upper) * sqrt(lower)) / (sqrt(upper) - sqrt(lower)) --Case 2: lower < cprice <= upper --Case 3: upper < cprice liq, CASE WHEN price < lower THEN abs(caseOne) WHEN lower < price AND price <= upper AND caseTwo0 < caseTwo1 THEN abs(caseTwo0) WHEN lower < price AND price <= upper AND caseTwo0 > caseTwo1 THEN abs(caseTwo1) WHEN price >= upper THEN abs(caseThree) END AS liqudity_value, liqudity_value / (liqudity_value + liq) as liquidity_share, (liquidity_share * vol * (fee)) AS est_fees_earned FROM liquidity WHERE liqudity_value IS NOT null and liq > 0 ORDER BY est_fees_earned DESC ) SELECT pool_name, est_fees_earned AS projected_fees_earned, pool_address, fee*100 as pool_fee, price, lower AS lower_bound, upper AS upper_bound, token0_amt, token1_amt, token0_amt_usd, token1_amt_usd, volume_24hours FROM fee_estimation ORDER BY projected_fees_earned DESC

Uniswap V3 Pool Fee Estimator