shreexSerum as
    Updated 2022-11-22
    with serum as (
    select
    distinct address as address,
    address_name as program
    from solana.core.dim_labels
    where label_type in ('defi','dex') and address_name ilike '%serum%'),
    new_Users as (
    select
    INSTRUCTION:accounts[1] as users,
    min(block_timestamp) as min_date
    from solana.core.fact_events inner join serum on program_id = address
    where block_timestamp >= '2022-11-01' and program_id=address
    GROUP BY users
    ),
    counts_users as (
    select
    date_trunc('day',min_date) as datee,
    count(distinct users) as new_users,
    sum(new_users) over (order by datee ) as cumulative_users
    from new_users
    GROUP BY datee
    ), dates as (
    select
    date_trunc('day',block_timestamp) as date,
    count(DISTINCT INSTRUCTION:accounts[1]) as unique_users,
    count(distinct tx_id) as txs,
    avg(txs) over (order by date) as avg_transactions
    from solana.core.fact_events inner join serum on program_id = address
    where date >= '2022-11-01' and program_id=address
    group by date
    order by date
    )
    select
    case
    when date >= '2022-11-08' then 'After FTX Collapse'
    when date < '2022-11-08' then 'Before FTX Collapse'