sinahosseinzadeh2023-04-12 11:36 PM
    WITH raw_tx AS
    (
    SELECT
    a.TX_ID, a.BLOCK_TIMESTAMP, a.PROPOSER, b.EVENT_TYPE, b.EVENT_CONTRACT
    FROM
    flow.core.fact_transactions a JOIN flow.core.fact_events b
    ON a.TX_ID = b.TX_ID
    WHERE
    a.TX_SUCCEEDED = 'true'
    ),

    adj_tx AS
    (
    SELECT
    distinct TX_ID, BLOCK_TIMESTAMP, PROPOSER, EVENT_TYPE, EVENT_CONTRACT
    FROM
    raw_tx
    ),

    tab1 AS
    (
    SELECT
    'Daily' as timeline,
    date_trunc('day', BLOCK_TIMESTAMP) as date,
    COUNT(distinct PROPOSER) as count_active_user
    FROM
    adj_tx
    GROUP BY 1, 2

    UNION ALL
    SELECT
    'Weekly' as timeline,
    date_trunc('week', BLOCK_TIMESTAMP) as date,
    COUNT(distinct PROPOSER) as count_active_user
    FROM
    adj_tx
    Run a query to Download Data