i_dancurrent-tomato copy copy
    Updated 2025-04-03
    -- SELECT date_trunc(day, block_timestamp) AS Day , COUNT(tx_hash) AS Transactions , COUNT(from_address) AS Wallets , SUM(tx_fee) AS Fees FROM ronin.core.fact_transactions WHERE block_timestamp >= '2025-01-01' GROUP BY 1 ORDER BY 1 desc

    WITH user_activity AS (
    SELECT
    from_address
    , DATE_TRUNC('day', block_timestamp) AS activity_day
    , DATE_TRUNC('week', block_timestamp) AS activity_week
    FROM ronin.core.fact_transactions
    WHERE block_timestamp >= CURRENT_DATE - INTERVAL '180 days'
    ),

    dau AS (
    SELECT
    activity_day
    , COUNT(DISTINCT from_address) AS daily_active_users
    FROM user_activity
    GROUP BY 1
    ),

    wau AS (
    SELECT
    activity_week
    , COUNT(DISTINCT from_address) AS weekly_active_users
    FROM user_activity
    GROUP BY 1
    )

    SELECT
    d.activity_day AS "Date"
    , d.daily_active_users AS "Daily Users"
    , w.weekly_active_users AS "Weekly Users"
    , (d.daily_active_users::float / w.weekly_active_users) * 100 AS DAU_WAU
    , ROUND(DAU_WAU, 2)||'%' AS "DAU - WAU Rate (%)"
    FROM dau d
    LEFT JOIN wau w
    ON DATE_TRUNC('week', d.activity_day) = w.activity_week
    Last run: about 1 month ago
    Date
    Daily Users
    Weekly Users
    DAU_WAU
    DAU - WAU Rate (%)
    1
    2024-10-05 00:00:00.0001185751137582986.1844749686.18%
    2
    2024-10-06 00:00:00.0001216466137582988.41694716488.42%
    3
    2024-10-07 00:00:00.0001148698187806161.16404099861.16%
    4
    2024-10-08 00:00:00.0001118489187806159.55552029559.56%
    5
    2024-10-09 00:00:00.0001093921187806158.24736257258.25%
    6
    2024-10-10 00:00:00.000913859187806148.65970807148.66%
    7
    2024-10-11 00:00:00.000851884187806145.3597620145.36%
    8
    2024-10-12 00:00:00.000869913187806146.31974147846.32%
    9
    2024-10-13 00:00:00.000869309187806146.28758064846.29%
    10
    2024-10-14 00:00:00.000836708154295654.22759949154.23%
    11
    2024-10-15 00:00:00.000880543154295657.06857486557.07%
    12
    2024-10-16 00:00:00.000906290154295658.73725498358.74%
    13
    2024-10-17 00:00:00.000885306154295657.37726804957.38%
    14
    2024-10-18 00:00:00.000915546154295659.33714247259.34%
    15
    2024-10-19 00:00:00.000807911154295652.36124685352.36%
    16
    2024-10-20 00:00:00.000895424154295658.03302232958.03%
    17
    2024-10-21 00:00:00.000967863161699359.85573221459.86%
    18
    2024-10-22 00:00:00.000902500161699355.81347600155.81%
    19
    2024-10-23 00:00:00.000485257161699330.00983925130.01%
    20
    2024-10-24 00:00:00.000947846161699358.61781714658.62%
    ...
    181
    11KB
    15s