rajsMonthly NFL All Day Growth
    Updated 2022-09-14
    SELECT
    date,
    no_of_txs,
    lag(no_of_txs,1) over (order by date) as lag_no_of_txs,
    no_of_txs - lag(no_of_txs,1) over (order by date) as growth_no_of_txs,
    (no_of_txs - lag(no_of_txs,1) over (order by date)) / lag(no_of_txs,1) over (order by date) * 100 as growth_no_of_txs_pct,
    volume,
    lag(volume,1) over (order by date) as lag_volume,
    volume - lag(volume,1) over (order by date) as growth_volume,
    (volume - lag(volume,1) over (order by date)) / lag(volume,1) over (order by date) * 100 as growth_volume_pct
    FROM
    (
    SELECT
    date_trunc('month', block_timestamp) as date,
    count(distinct tx_id) as no_of_txs,
    -- avg(count(distinct tx_id)) over (order by date_trunc('day', block_timestamp) rows between 7 preceding and 1 preceding) as "prior_7_days_avg_no_of_txs",
    -- sum(count(distinct tx_id)) over (order by date_trunc('day', block_timestamp)) as cum_no_of_txs,
    sum(price) as volume
    -- avg(sum(price)) over (order by date_trunc('day', block_timestamp) rows between 7 preceding and 1 preceding) as "prior_7_days_avg_volume",
    -- sum(sum(price)) over (order by date_trunc('day', block_timestamp)) as cum_volume,
    -- count(distinct buyer) as no_of_buyers,
    -- avg(count(distinct buyer)) over (order by date_trunc('day', block_timestamp) rows between 7 preceding and 1 preceding) as "prior_7_days_avg_no_of_buyers",
    -- sum(count(distinct buyer)) over (order by date_trunc('day', block_timestamp)) as cum_no_of_buyers,
    -- count(distinct seller) as no_of_sellers,
    -- avg(count(distinct seller)) over (order by date_trunc('day', block_timestamp) rows between 7 preceding and 1 preceding) as "prior_7_days_avg_no_of_sellers",
    -- sum(count(distinct seller)) over (order by date_trunc('day', block_timestamp)) as cum_no_of_sellers
    -- distinct currency
    -- count(*)
    from flow.core.ez_nft_sales
    where nft_collection = 'A.e4cf4bdc1751c65d.AllDay'
    group by 1
    order by 1 desc
    -- limit 1
    )
    Run a query to Download Data