Sniperknown-yellow
    Updated 2025-01-14
    -- select * from ink.core.fact_event_logs WHERE
    -- tx_hash = '0x659b843f87042521f8c587a3bb4a5dbc3f7d0b9d70501f64fc7c2a4ec6e32ba1'
    --CONTRACT_ADDRESS = '0x38d9ecc404d8d21b1e251c0397f63d0429488345'
    with eth_price as (
    select date_trunc(day,hour)::date date_et ,
    median (PRICE) usd_price_et
    from crosschain.price.ez_prices_hourly
    where symbol ='ETH'
    and BLOCKCHAIN = 'ethereum'
    and date_trunc(day,hour)::date >= '2024-12-01'
    and TOKEN_ADDRESS is NULL
    group by 1
    ),
    sq_price as (select
    date_trunc(day,hour)::date date_sq ,
    median (PRICE) usd_price_sq
    from ink.price.ez_prices_hourly
    where
    TOKEN_ADDRESS = '0xbf0cafcbaaf0be8221ae8d630500984edc908861'
    and date_trunc(day,hour)::date >= '2024-12-01'
    group by 1
    )
    ,
    a as (
    SELECT
    tx_hash
    ,
    regexp_substr_all(SUBSTR(DATA, 3, len(DATA)), '.{64}') AS segmented_data
    ,CONCAT('0x', SUBSTR(topics [1] :: STRING, 10+17, 40)) AS sender
    ,CONCAT('0x', SUBSTR(topics [2] :: STRING, 10+17, 40)) AS to_address
    ,CONCAT('0x', SUBSTR(segmented_data [0] :: STRING, 10+15, 40)) AS fromToken
    ,CONCAT('0x', SUBSTR(segmented_data [1] :: STRING, 10+15, 40)) AS toToken
    ,(TRY_TO_NUMBER(ethereum.public.udf_hex_to_int(segmented_data [2] :: STRING)):: STRING /pow(10,18)) AS fromAmount
    ,(TRY_TO_NUMBER(ethereum.public.udf_hex_to_int(segmented_data [3] :: STRING)):: STRING/pow(10,18)) AS toAmount
    ,TRY_TO_NUMBER(ethereum.public.udf_hex_to_int(segmented_data [4] :: STRING)):: STRING AS toTokenFee
    -- ,case when amount_in_usd is null then amount_out_usd
    Last run: 11 days ago
    TX
    1
    1
    1
    5B
    3s