flipside

    Overtime
    Afonso_DiazOvertime
    Updated 5 days ago
    WITH main AS (
    SELECT
    tx_hash,
    block_timestamp,
    from_address AS user,
    tx_fee
    FROM kaia.core.fact_transactions
    ),
    monthly_txns AS (
    SELECT
    DATE_TRUNC('MONTH', block_timestamp) AS month,
    user,
    COUNT(tx_hash) AS tx_count,
    SUM(tx_fee) AS total_fee
    FROM main
    GROUP BY 1, 2
    ),
    first_seen AS (
    SELECT
    user,
    MIN(DATE_TRUNC('MONTH', block_timestamp)) AS first_month
    FROM main
    GROUP BY 1
    ),
    monthly_metrics AS (
    SELECT
    mt.month,
    SUM(mt.tx_count) AS total_txns,
    SUM(mt.total_fee) AS total_fee,
    COUNT(DISTINCT CASE WHEN fs.first_month = mt.month THEN mt.user END) AS new_users,
    COUNT(DISTINCT CASE WHEN fs.first_month < mt.month THEN mt.user END) AS old_users
    FROM monthly_txns mt
    LEFT JOIN first_seen fs ON mt.user = fs.user
    GROUP BY 1
    ),
    cumulative_metrics AS (
    Last run: 5 days ago
    MONTH
    TOTAL_TXNS
    TOTAL_FEE
    NEW_USERS
    OLD_USERS
    CUMULATIVE_FEE
    CUMULATIVE_TXNS
    CUMULATIVE_NEW_USERS
    1
    		2019-06-01 00:00:00.00013138.712007449508.71200741313495
    2
    		2019-07-01 00:00:00.0002571511053.91670595206572631062.6287133525846421152
    3
    		2019-08-01 00:00:00.000627841369772.33339412843541075170834.962107456536877305506
    4
    		2019-09-01 00:00:00.000264467156616.28235562532599115626127451.2444630759181548631497
    5
    		2019-10-01 00:00:00.000462776249596.729822648583117386177047.974285675138093101117328
    6
    		2019-11-01 00:00:00.00015344385107278.401942675294673937980284326.37622835291536954064067
    7
    		2019-12-01 00:00:00.0001009139149745.34916277575726474558334071.725391125392450864821331
    8
    		2020-01-01 00:00:00.0001290317353748.35139531177709103859387820.076786425521482595999040
    9
    		2020-02-01 00:00:00.0001019955143664.2167406831957112000431484.293527025623478106830997
    10
    		2020-03-01 00:00:00.000764786032392.4322382579243796326463876.725765275699956707623434
    11
    		2020-04-01 00:00:00.000814370735597.963065755929176517499474.688830975781393778182725
    12
    		2020-05-01 00:00:00.000492322820252.87367362534182652378519727.5625046830626058524551
    13
    		2020-06-01 00:00:00.000578791539725.49679792579929057874559453.059302525888505209323841
    14
    		2020-07-01 00:00:00.000632554041588.59742212531677553833601041.65672465951760609640616
    15
    		2020-08-01 00:00:00.000641580627643.623407826042740748628685.280132451015918669901043
    16
    		2020-09-01 00:00:00.000557652519929.58465242513819415086648614.86478487510716839110039237
    17
    		2020-10-01 00:00:00.000675434819222.1165810520755149402667836.98136592511392273910246788
    18
    		2020-11-01 00:00:00.000605580417693.35486637519010346309685530.336232311997854310436891
    19
    		2020-12-01 00:00:00.000930964429744.168222917717487798715274.504455212928818710614065
    20
    		2021-01-01 00:00:00.0001192010641726.031650913875970861757000.536106114120829310752824
    72
    7KB
    826s