CODXIIIflow - users and txs / phases total
    Updated 2023-03-24

    with
    c as (
    select DISTINCT
    replace(event_data:address,'0x','A.') || '.' || event_data:contract as contract_address
    from flow.core.fact_events
    where date_trunc('day', BLOCK_TIMESTAMP) BETWEEN '2023-02-10 00:00:00.000' AND current_date
    and event_type in ('AccountContractAdded')
    and tx_succeeded=1

    ),
    eventss as (
    select
    STRTOK_TO_ARRAY(e.event_contract, '.') as event_contract_split,
    event_contract_split[0] || '.' || event_contract_split[1] as contract_address,
    e.*
    from flow.core.fact_events e
    where date_trunc('day', BLOCK_TIMESTAMP) BETWEEN '2023-02-10 00:00:00.000' AND current_date
    and e.event_contract in (select contract_address from c)
    )


    select
    CASE
    WHEN eventss.block_timestamp BETWEEN '2023-02-10 00:00:00.000' AND '2023-02-20 23:59:59.999' THEN '1 - education_phase'
    WHEN eventss.block_timestamp BETWEEN '2023-02-21 00:00:00.000' AND '2023-02-28 23:59:59.999' THEN '2 - submission_phase'
    ELSE '3 - launchpad_phase'
    END AS contracts_phase,
    count(distinct eventss.TX_ID) as txs,
    count(distinct PROPOSER) as users,
    round(txs/users,0) as avg_tx_per_user
    from eventss inner join flow.core.fact_transactions t on t.TX_ID=eventss.TX_ID
    group by 1
    order by 1