LittlerData2024-04-11 02:50 AM
    Updated 2024-04-19
    with bridge_transactions as (
    select BLOCK_TIMESTAMP,
    SENDER,
    SOURCE_CHAIN,
    DESTINATION_CHAIN,
    TOKEN_ADDRESS,
    TOKEN_SYMBOL,
    AMOUNT,
    TX_HASH
    from axelar.defi.ez_bridge_satellite
    where BLOCK_TIMESTAMP::date between '2023-08-21' and '2023-08-23'
    ),
    token_prices as (
    select date_trunc(day,HOUR)::date date ,
    TOKEN_ADDRESS,
    SYMBOL,
    avg(PRICE) usd_price
    from crosschain.price.ez_hourly_token_prices
    where date_trunc(day,HOUR)::date between '2023-08-21' and '2023-08-23'
    group by 1,2,3
    ),
    transactions_prices as (
    select
    BLOCK_TIMESTAMP as date,
    SENDER,
    SOURCE_CHAIN,
    DESTINATION_CHAIN,
    b.TOKEN_ADDRESS,
    b.TOKEN_SYMBOL,
    AMOUNT,
    AMOUNT * zeroifnull(usd_price) amount_usd ,
    TX_HASH
    from bridge_transactions b left join token_prices p
    on (BLOCK_TIMESTAMP::date =date and b.TOKEN_ADDRESS = p.TOKEN_ADDRESS )
    where block_timestamp between '2023-08-21' and '2023-08-23'
    ),