SocioAnalyticaover time swap on Dexes [Ethereum] copyv
    Updated 2024-03-04
    with swaps as (
    select
    tx_hash,
    block_timestamp
    from ethereum.defi.ez_dex_swaps
    where block_timestamp::date >= dateadd('day', -{{past_days}}, current_date)
    )
    ,
    fee_eth as (
    select
    date_trunc('day', block_timestamp) as date,
    count(DISTINCT tx_hash) as n_swaps,
    sum(tx_fee) as total_fee_eth,
    avg(tx_fee) as avg_fee_eth,
    median(tx_fee) as median_fee_eth
    from ethereum.core.fact_transactions a
    join swaps b using(tx_hash, block_timestamp)
    where a.block_timestamp::date >= dateadd('day', -{{past_days}}, current_date)
    group by 1
    )
    ,
    eth_price as (
    SELECT 'Ethereum' as network,
    TO_TIMESTAMP(value[0]::string) as date,
    value[1] as price
    FROM (
    SELECT livequery.live.udf_api(
    'https://api.coingecko.com/api/v3/coins/ethereum/market_chart?vs_currency=usd&days=30') as response
    ),LATERAL FLATTEN (input => response:data:prices)
    )
    , avg_price as (
    select
    date_trunc('day', date) as day,
    avg(price) as usd_price
    from eth_price
    group by 1
    QueryRunArchived: QueryRun has been archived