Returns the price of any token (denominated in any other) using Uniswap V3
Parameters
token_A
(address as hex string, default: 0xB8c77482e45F1F44dE1745F52C74426C631bDD52)- The token that we want to price.
token_B
(address as hex string, default: 0xdAC17F958D2ee523a2206206994597C13D831ec7)- The token in which we want the price to be denominated in. For USD, use USDT or USDC.
block_number
(int, default: 0)- If you want the price quote at a particular block height, input that height here (and also choose a specific
chain_id
). For the latest price, use the default 0.
- If you want the price quote at a particular block height, input that height here (and also choose a specific
Returns
a_to_weth_ts
(timestamp)- This is the timestamp of the swap between tokenA and weth that was used to generate the price.
weth_to_b_ts
(timestamp)- This is the timestamp of the swap between weth and tokenB that was used to generate the price.
a_price_in_b
(decimal)- The price of tokenA in units of tokenB.
Query
WITH latest_token_a_to_weth AS (
SELECT
MAX(block_timestamp) AS max_ts,
CASE
WHEN token0 = LOWER($token_a) THEN
(1.0 * CAST(zero_to_one AS DOUBLE)) / POWER(10, token1_dec)
ELSE
(1.0 * CAST(one_to_zero AS DOUBLE)) / POWER(10, token0_dec)
END AS token_a_weth_price,
current_tick_liquidity
FROM @sim.uniswap_v3_prices
WHERE
token0 IN (LOWER($token_a), '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')
AND token1 IN (LOWER($token_a), '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2')
AND (block_number <= $block_number OR $block_number = 0)
GROUP BY 2, 3
ORDER BY 1 DESC, 3 DESC
LIMIT 1
),
latest_weth_to_token_b AS (
SELECT
block_timestamp,
CASE
WHEN token0 = '0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2' THEN
(1.0 * CAST(zero_to_one AS DOUBLE)) / POWER(10, token1_dec)
ELSE
(1.0 * CAST(one_to_zero AS DOUBLE)) / POWER(10, token0_dec)
END AS weth_token_b_price,
current_tick_liquidity
FROM @sim.uniswap_v3_prices
WHERE
token0 IN ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', LOWER($token_b))
AND token1 IN ('0xc02aaa39b223fe8d0a0e5c4f27ead9083c756cc2', LOWER($token_b))
AND (block_number <= $block_number OR $block_number = 0)
ORDER BY 1 DESC, 3 DESC
LIMIT 1
)
SELECT
ToDateTime(aw.max_ts * 1000, 'yyyy-MM-dd HH:mm:ss') AS a_to_weth_ts,
ToDateTime(wb.block_timestamp * 1000, 'yyyy-MM-dd HH:mm:ss') AS weth_to_b_ts,
(aw.token_a_weth_price * wb.weth_token_b_price) AS a_price_in_b
FROM latest_token_a_to_weth aw
JOIN latest_weth_to_token_b wb
ON 1 = 1;