Updated 2025-01-26
    WITH price_data AS (
    SELECT
    DATE_TRUNC('DAY', HOUR) AS recorded_day,
    AVG(PRICE) AS avg_price
    FROM
    flow.price.ez_prices_hourly
    WHERE
    SYMBOL = 'FLOW'
    GROUP BY 1
    ),
    transaction_data AS (
    SELECT
    BLOCK_NUMBER,
    DATE_TRUNC('MINUTE', BLOCK_TIMESTAMP) AS block_minute,
    DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS block_day,
    BLOCK_TIMESTAMP,
    TX_HASH,
    NONCE,
    POSITION,
    ORIGIN_FUNCTION_SIGNATURE,
    FROM_ADDRESS,
    TO_ADDRESS,
    VALUE,
    VALUE_PRECISE_RAW,
    VALUE_PRECISE,
    TX_FEE,
    TX_FEE_PRECISE,
    STATUS
    FROM flow.core_evm.fact_transactions
    ),
    new_user AS (
    SELECT
    FROM_ADDRESS AS new_user,
    MIN(DATE_TRUNC('DAY', BLOCK_TIMESTAMP)) AS min_date
    FROM
    flow.core_evm.fact_transactions
    Last run: 20 days ago
    DATE
    DISTINCT_TX_COUNT
    DISTINCT_ADDRESS_COUNT
    TOTAL_TX_FEE
    TOTAL_TX_FEE_USD
    AVG_TX_FEE
    AVG_TX_FEE_USD
    DISTINCT_BLOCK_COUNT
    AVG_TX_PER_USER
    SUCCESS_RATE
    NEW_USER_COUNT
    TPM
    30D-MA TPM
    1
    2024-09-04 00:00:00.00013870.000229990.00012708253650.0000016665942039.208879458e-77119.71428571497.10144970.0958330.095833
    2
    2024-09-05 00:00:00.00017490.001902260.0010307112060.000010932528740.00000592362761912419.33333333310040.1208330.108333
    3
    2024-09-06 00:00:00.000457250.014920252860.0078295537570.000032648255720.0000171325027532218.28100190.3173610.178009
    4
    2024-09-07 00:00:00.00092300009130.66666666710010.0638890.149479
    5
    2024-09-08 00:00:00.0009270.00001905090.0000098624302572.07075e-71.072003289e-78213.14285714310040.0638890.132361
    6
    2024-09-09 00:00:00.000926200.040324796720.021360906760.000043547296670.0000230679338748546.3100110.6430560.217476833
    7
    2024-09-10 00:00:00.000260170.00023864001910.00012842036779.178462273e-74.939244911e-713215.29411764710070.1805560.212202428
    8
    2024-09-11 00:00:00.0007280.00023723620.00012734459640.0000032949472220.0000017686749563910010.050.191927125
    9
    2024-09-12 00:00:00.000257140.0002739630.00015161122690.0000010660038915.899269531e-712218.35714285710060.1784720.190432111
    10
    2024-09-13 00:00:00.000143110.00002018587710.000011302618451.411599797e-77.903928985e-8861310030.0993060.1813195
    11
    2024-09-14 00:00:00.000201210.08773672670.049319011150.00043650112790.00024536821471209.57142857110090.1395830.177525272
    12
    2024-09-15 00:00:00.0007280.00025991610.00014466646380.0000036099458330.00000200925644247910020.050.166898166
    13
    2024-09-16 00:00:00.000244230.035228791670.018771459070.00014438029370.0000769322093212310.60869565297.9508280.1694440.167094
    14
    2024-09-17 00:00:00.000183150.0003481556040.00018516974940.0000019024896390.0000010118565549212.210050.1270830.164236071
    15
    2024-09-18 00:00:00.000364240.0057834822220.0030702711830.000015888687420.00000843481094218715.16666666710080.2527780.170138866
    16
    2024-09-19 00:00:00.000384130.00369876070.0020723168320.0000096321893230.00000539665841820129.53846153810030.2666670.176171875
    17
    2024-09-20 00:00:00.000376250.011224512950.0064835134610.000029852428070.0000172433868614715.04100100.2611110.181168294
    18
    2024-09-21 00:00:00.000659210.01119668220.0065428297640.000016990413050.00000992842149321831.38095238199.54476580.4576390.196527777
    19
    2024-09-22 00:00:00.00011690.01052096980.0060931762610.000090698015520.000052527381567712.88888888999.13793110.0805560.190424
    20
    2024-09-23 00:00:00.000476240.02349098740.013739908880.000049350813870.0000288653547820319.83333333398.10924490.3305560.1974306
    ...
    145
    21KB
    4s