flipside

    2024-01-29 07:49 PM
    mustafaotaru2024-01-29 07:49 PM
    Updated 2025-03-21
    with tab1 as (
    SELECT
    first_hour as hour,
    count(*) as new_users,
    sum(new_users) over (ORDER by hour) as total_users
    FROM (
    SELECT
    from_address,
    min(date_trunc('hour', block_timestamp)) as first_hour
    FROM blast.core.fact_transactions
    GROUP BY 1
    )
    GROUP BY 1
    ), tab2 as (
    SELECT
    date_trunc('hour', block_timestamp) as hour1,
    count(DISTINCT from_address) as users
    FROM blast.core.fact_transactions
    GROUP BY 1
    )

    SELECT
    *,
    users - new_users as returning_users
    FROM tab2
    LEFT outer JOIN tab1
    on hour = hour1
    WHERE hour > '2024-02-29'
    ORDER by hour DESC



    Last run: about 1 month ago
    HOUR1
    USERS
    HOUR
    NEW_USERS
    TOTAL_USERS
    RETURNING_USERS
    1
    		2025-03-21 10:00:00.0001252025-03-21 10:00:00.00011800953124
    2
    		2025-03-21 09:00:00.0005222025-03-21 09:00:00.000111800952511
    3
    		2025-03-21 08:00:00.0005922025-03-21 08:00:00.00071800941585
    4
    		2025-03-21 07:00:00.0004482025-03-21 07:00:00.000181800934430
    5
    		2025-03-21 06:00:00.0004822025-03-21 06:00:00.00041800916478
    6
    		2025-03-21 05:00:00.0004452025-03-21 05:00:00.00011800912444
    7
    		2025-03-21 04:00:00.00012122025-03-21 04:00:00.0007521800911460
    8
    		2025-03-21 03:00:00.0003412025-03-21 03:00:00.000111800159330
    9
    		2025-03-21 02:00:00.0004642025-03-21 02:00:00.000101800148454
    10
    		2025-03-21 01:00:00.0004452025-03-21 01:00:00.00021800138443
    11
    		2025-03-21 00:00:00.0005602025-03-21 00:00:00.00051800136555
    12
    		2025-03-20 23:00:00.0003452025-03-20 23:00:00.00081800131337
    13
    		2025-03-20 22:00:00.00018212025-03-20 22:00:00.000718001231814
    14
    		2025-03-20 21:00:00.00047062025-03-20 21:00:00.000518001164701
    15
    		2025-03-20 20:00:00.00024022025-03-20 20:00:00.000718001112395
    16
    		2025-03-20 19:00:00.00048892025-03-20 19:00:00.000318001044886
    17
    		2025-03-20 18:00:00.00020392025-03-20 18:00:00.000418001012035
    18
    		2025-03-20 17:00:00.00023462025-03-20 17:00:00.000618000972340
    19
    		2025-03-20 16:00:00.00066642025-03-20 16:00:00.000618000916658
    20
    		2025-03-20 15:00:00.00055782025-03-20 15:00:00.000518000855573
    ...
    9274
    677KB
    18s