lanokaia-tve
    Updated 2024-08-27
    with
    token_info AS (
    SELECT
    address,
    decimals
    FROM kaia.core.dim_contracts
    ),
    token_tx AS (
    SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    kaia.core.fact_token_transfers.contract_address,
    raw_amount / POWER(10, decimals) AS value
    FROM kaia.core.fact_token_transfers
    LEFT JOIN token_info
    ON kaia.core.fact_token_transfers.contract_address = token_info.address
    WHERE kaia.core.fact_token_transfers.contract_address IN (
    '0x19aac5f612f524b754ca7e7c41cbfa2e981a4432' -- wklay
    ,'0x5c13e303a62fc5dedf5b52d66873f2e59fedadc2'---usdt
    , '0x15d9f3ab1982b0e5a415451259994ff40369f584' --btcb
    , '0x98a8345bb9d3dda9d808ca1c9142a28f6b0430e1' -- weth
    , '0x574e9c26bda8b95d7329505b4657103710eb32ea' -- obnb
    , '0x34d21b1e550d73cee41151c77f3c73359527a396' -- oeth
    , '0x9eaefb09fe4aabfbe6b1ca316a3c36afc83a393f' --oxrp
    , '0xcee8faf64bb97a73bb51e115aa89c17ffa8dd167' --ousdt
    , '0x754288077d0ff82af7a5317c7cb8c444d421d103' -- ousdc
    , '0x16d0e1fbd024c600ca0380a4c5d57ee7a2ecbf9c' -- owbtc
    , '0x02cbe46fb8a1f579254a9b485788f2d86cad51aa' -- bora
    , '0xc6a2ad8cc6e4a7e08fc37cc5954be07d499e7654' -- ksp
    )
    ), token_price AS (
    SELECT
    DATE_TRUNC('day', hour) AS day,
    token_address,
    avg(price) AS price
    FROM kaia.price.ez_prices_hourly
    GROUP BY
    QueryRunArchived: QueryRun has been archived