SocioCryptonew users and active users
    Updated 2023-01-23
    SELECT
    CASE WHEN y.date between '2023-01-07' AND '2023-01-13' THEN 'before'
    WHEN y.date between '2023-01-15' AND '2023-01-21' THEN 'after'
    ELSE 'announcement' END as periods,
    avg(z.unique_wallet) as n_new_wallet,
    avg(y.wallet) as n_active_wallet
    FROM
    (
    SELECT date_trunc('day',first_tx) as first_tx_date,
    COUNT(DISTINCT wallet) as unique_wallet
    FROM
    (
    SELECT tx_sender AS wallet,
    min(block_timestamp) as first_tx,
    COUNT(DISTINCT tx_id) as n_txs
    from terra.core.fact_transactions p
    GROUP BY wallet
    )
    WHERE first_tx_date between '2023-01-07' AND '2023-01-21'
    group BY first_tx_date
    ) z
    left JOIN
    (
    SELECT date_trunc('day',block_timestamp) as date,
    count( DISTINCT tx_sender) as wallet
    from terra.core.fact_transactions
    WHERE date between '2023-01-07' AND '2023-01-21'
    group BY date
    )y
    on y.date = z.first_tx_date
    GROUP by periods
    ORDER by periods DESC