kurksonides-u6YS1L2023-05-14 05:41 PM
    with min_dates as(
    select
    origin_from_address as addresses,
    min(block_timestamp)::date as min_date,
    date_trunc('month', min_date)::date as date
    from ethereum.core.ez_dex_swaps
    where platform like '%uniswap%'
    group by addresses
    ),
    new_userz as(
    select
    date,
    count(DISTINCT addresses) as new_users,
    count(DISTINCT tx_hash) as new_transactions,
    sum(amount_in_usd) as new_volume,
    avg(amount_in_usd) as new_avg_price,
    median(amount_in_usd) as new_median_price,
    min(amount_in_usd) as new_min_price,
    max(amount_in_usd) as new_max_price,
    sum(new_volume)over(order by date) as new_total_volume,
    sum(new_transactions)over(order by date) as new_total_swaps,
    new_transactions / new_users as transactions_per_new_user,
    new_volume / new_users as volume_per_new_user
    from ethereum.core.ez_dex_swaps x, min_dates
    where x.block_timestamp::date > current_date - interval '12 months'
    and x.origin_from_address in(SELECT DISTINCT addresses)
    group by date
    ),
    users as(
    select
    date,
    count(DISTINCT origin_from_address) as users,
    count(DISTINCT tx_hash) as transactions,
    sum(amount_in_usd) as volume,
    avg(amount_in_usd) as avg_price,
    median(amount_in_usd) as median_price,