Afonso_Diaz2023-08-19 04:53 PM
    Updated 2023-08-19
    with t as (
    select
    date_trunc('month', block_timestamp)::date as month,
    'Avalanche' as chain,
    count(distinct tx_hash) as transactions,
    (select count(distinct user)
    from (select distinct seller_address as user
    from avalanche.core.ez_nft_sales union select distinct buyer_address as user from avalanche.core.ez_nft_sales)) as users,
    sum(price_usd) as volume_usd,
    avg(price_usd) as average_volume_usd,
    sum(transactions) over (partition by chain order by month) as cumulative_transactions,
    sum(volume_usd) over (partition by chain order by month) as cumulative_volume_usd
    from avalanche.core.ez_nft_sales
    group by 1, 2

    union

    select
    date_trunc('month', block_timestamp)::date as month,
    'Optimism' as chain,
    count(distinct tx_hash) as transactions,
    (select count(distinct user)
    from (select distinct seller_address as user
    from optimism.core.ez_nft_sales union select distinct buyer_address as user from optimism.core.ez_nft_sales)) as users,
    sum(price_usd) as volume_usd,
    avg(price_usd) as average_volume_usd,
    sum(transactions) over (partition by chain order by month) as cumulative_transactions,
    sum(volume_usd) over (partition by chain order by month) as cumulative_volume_usd
    from optimism.core.ez_nft_sales
    group by 1, 2

    union

    select
    date_trunc('month', block_timestamp)::date as month,
    'Polygon' as chain,
    Run a query to Download Data