datavortexcomparable-peach
    Updated 2024-10-18
    /*
    WITH sol_price AS (
    SELECT
    symbol,
    price,
    hour
    FROM solana.price.ez_prices_hourly
    WHERE symbol = 'SOL'
    AND hour = (SELECT MAX(hour) FROM solana.price.ez_prices_hourly WHERE symbol = 'SOL')
    AND price > 1
    ORDER BY price DESC
    LIMIT 1
    )
    SELECT
    symbol,
    {{usd_amount}} / price AS sol_amount,
    price AS sol_price_in_usd,
    hour
    FROM sol_price;
    */
    WITH latest_prices AS (
    SELECT
    symbol,
    price,
    hour,
    ROW_NUMBER() OVER (PARTITION BY symbol ORDER BY hour DESC) AS rn -- Get the latest price per symbol
    FROM solana.price.ez_prices_hourly
    WHERE price > 0.01 -- Filter for tokens with a price greater than 0.01 USD
    )
    SELECT
    symbol,
    {{usd_amount}} / price AS token_amount, -- Calculate the amount of token for the input USD amount
    price AS token_price_in_usd,
    hour
    FROM latest_prices
    WHERE rn = 1 -- Keep only the latest price per symbol
    QueryRunArchived: QueryRun has been archived