Parameters

token_A (address as hex string, default: 0xB8c77482e45F1F44dE1745F52C74426C631bDD52)
1. The token that we want to price.
token_B(address as hex string, default: 0xdAC17F958D2ee523a2206206994597C13D831ec7)
1. The token in which we want the price to be denominated in. For USD, use USDT or USDC.
block_number (int, default: 0)
1. 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.

Returns

a_to_weth_ts (timestamp)
1. This is the timestamp of the swap between tokenA and weth that was used to generate the price.
weth_to_b_ts (timestamp)
1. This is the timestamp of the swap between weth and tokenB that was used to generate the price.
a_price_in_b (decimal)
1. 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;