flipside

    2024-06-06 02:09 PM
    Afonso_Diaz2024-06-06 02:09 PM
    Updated 2024-06-06
    WITH pricet AS(
    SELECT
    hour :: DATE AS dt,
    token_address,
    decimals,
    AVG(price) AS price_daily
    FROM
    solana.price.ez_prices_hourly
    GROUP BY
    1,
    2,
    3
    )

    -- select distinct event_resource from aptos.core.fact_events
    -- where account_address = '0x7e783b349d3e89cf5931af376ebeadbfab855b3fa239b7ada8f5a92fbea6b387'
    -- limit 100

    -- select * from aptos.core.fact_transactions
    -- where tx_hash = '0xe6099840b891738fabb32c5072d6b72b5019ea801cbc870b37be211d8c3def27'

    SELECT
    BLOCK_TIMESTAMP,
    SWAPPER,
    TX_ID,
    'Jupiter' AS PROGRAM,
    swap_from_amount,
    price_daily,
    decimals,
    (SWAP_FROM_AMOUNT * price_daily) / pow(10, decimals) AS AMOUNT_USD
    FROM
    solana.defi.fact_swaps
    JOIN pricet ON swap_from_mint = token_address
    AND dt = block_timestamp :: DATE
    WHERE
    BLOCK_TIMESTAMP >= CURRENT_DATE - INTERVAL '6 MONTHS'
    QueryRunArchived: QueryRun has been archived