carlesmontalaalgo dex 1
    Updated 2022-11-20
    with base as(
    select date_trunc('{{sequence}}', block_hour) as date,
    asset_id,
    avg(price_usd) as price_usd
    from algorand.defi.ez_price_pool_balances
    group by 1,2)

    select
    date_trunc('{{sequence}}', block_timestamp) as date,
    initcap(swap_program) as swap_programs,
    count(distinct(tx_group_id)) as swap_count,
    count(distinct(swapper)) as swapper_count,
    sum(swap_to_amount * price_usd) as swap_volume_usd,
    avg(swap_to_amount * price_usd) as avg_swap_volume_usd,
    median(swap_to_amount * price_usd) as median_swap_volume_usd,
    min(swap_to_amount * price_usd) as min_swap_volume_usd,
    max(swap_to_amount * price_usd) as max_swap_volume_usd
    from algorand.defi.fact_swap a
    join base b
    on date_trunc('{{sequence}}', block_timestamp) = b.date
    and swap_to_asset_id = asset_id
    and swap_to_amount > 0
    and swap_to_amount < 100000000
    and price_usd < 100000
    and price_usd > 0
    and block_timestamp >= CURRENT_DATE-{{n_days}}
    group by 1,2
    order by DEXes asc
    Run a query to Download Data