BlockTrackertest Users Distribution by number of transaction copy
    Updated 2025-03-05
    WITH users AS (
    SELECT
    block_timestamp,
    origin_from_address,
    tx_hash
    -- MIN(block_timestamp) OVER (PARTITION BY origin_from_address) AS initial_timestamp,
    -- MAX(block_timestamp) OVER (PARTITION BY origin_from_address) AS last_timestamp
    FROM {{chain}}.core.ez_decoded_event_logs
    WHERE block_timestamp::date >= '2022-10-01'
    AND event_name IN ('LiFiGenericSwapCompleted','LiFiTransferStarted')
    ),
    transactions AS (
    SELECT
    origin_from_address,
    COUNT(DISTINCT DATE_TRUNC('day', block_timestamp)) AS n_days,
    COUNT(DISTINCT DATE_TRUNC('week', block_timestamp)) AS n_weeks,
    COUNT(DISTINCT DATE_TRUNC('month', block_timestamp)) AS n_months,
    COUNT(DISTINCT tx_hash) AS n_txns
    FROM users
    GROUP BY origin_from_address
    ),
    total_user_count AS (
    SELECT
    COUNT(DISTINCT origin_from_address) AS total_users
    FROM transactions
    ),
    user_counts AS (
    SELECT
    t.origin_from_address,
    t.n_days,
    t.n_weeks,
    t.n_months,
    t.n_txns,
    tuc.total_users
    FROM transactions t
    CROSS JOIN total_user_count tuc
    QueryRunArchived: QueryRun has been archived