Afonso_Diaz2023-11-04 06:57 PM
    Updated 2023-11-04
    with

    t as (
    select
    recorded_hour::date as date,
    avg(price) as price_usd
    from osmosis.price.ez_prices
    where symbol = 'ATOM'
    group by 1
    ),

    t2 as (
    select
    tx_id,
    block_timestamp,
    tx_from as user,
    nvl(try_parse_json(attribute_value):amount::float, 0)/1e6 as amount_atom,
    fee/1e6 as fee_atom,
    amount_atom * price_usd as amount_usd,
    fee_atom * price_usd as fee_usd,
    split(try_parse_json(attribute_value):denom::string, '/')[0] as validator
    from cosmos.core.fact_msg_attributes
    join cosmos.core.fact_transactions
    using(tx_id, block_timestamp)
    join t on date = block_timestamp::date
    where attribute_key ilike 'packet_data'
    and msg_type ilike 'send_packet'
    and validator in (select distinct address from cosmos.gov.fact_validators)
    and tx_id in (
    select distinct tx_id from cosmos.core.fact_msg_attributes
    where msg_type ilike 'send_packet'
    and attribute_value ilike 'channel-391'

    )
    )

    Run a query to Download Data