flipside

    scattered-gold
    permaryscattered-gold
    Updated 2025-03-31
    -- Query 1: Transaction volume by day with monthly cumulative metrics
    WITH daily_txn_volume AS (
    SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    DATE_TRUNC('month', block_timestamp) AS month_date,
    TO_CHAR(DATE_TRUNC('month', block_timestamp), 'MMMM') AS month_name,
    COUNT(*) AS daily_transactions,
    SUM(value) AS daily_volume -- Convert to RON from wei
    FROM ronin.core.fact_transactions
    WHERE block_timestamp >= '2025-01-01' AND block_timestamp < '2025-04-01'
    GROUP BY 1, 2, 3
    ),
    txn_cumulative_by_month AS (
    SELECT
    day,
    month_date,
    month_name,
    daily_transactions,
    daily_volume,
    SUM(daily_transactions) OVER (PARTITION BY month_date ORDER BY day) AS monthly_cumulative_transactions,
    SUM(daily_volume) OVER (PARTITION BY month_date ORDER BY day) AS monthly_cumulative_volume
    FROM daily_txn_volume
    )

    -- Query 2: USD volume by day with monthly cumulative metrics
    , daily_usd_volume AS (
    SELECT
    DATE_TRUNC('day', block_timestamp) AS day,
    DATE_TRUNC('month', block_timestamp) AS month_date,
    TO_CHAR(DATE_TRUNC('month', block_timestamp), 'MMMM') AS month_name,
    COUNT(*) AS daily_transfers,
    SUM(amount_usd) AS daily_volume_usd
    FROM (
    -- Native Transfers (RON)
    SELECT
    block_timestamp,
    Last run: 26 days ago
    DAY
    MONTH_DATE
    MONTH_NAME
    DAILY_TRANSACTIONS
    DAILY_VOLUME
    MONTHLY_CUMULATIVE_TRANSACTIONS
    MONTHLY_CUMULATIVE_VOLUME
    DAILY_TRANSFERS
    DAILY_VOLUME_USD
    MONTHLY_CUMULATIVE_TRANSFERS
    MONTHLY_CUMULATIVE_VOLUME_USD
    DATA_TYPE
    1
    		2025-01-01 00:00:00.0002025-01-01 00:00:00.000January104356026106257.6720841104356026106257.6720841TRANSACTIONS
    2
    		2025-01-01 00:00:00.0002025-01-01 00:00:00.000January48600282477386.6748600282477386.67USD_VOLUME
    3
    		2025-01-02 00:00:00.0002025-01-01 00:00:00.000January9399573908577.49621431198351730014835.1682984TRANSACTIONS
    4
    		2025-01-02 00:00:00.0002025-01-01 00:00:00.000January49850641163977.92984508123641364.59USD_VOLUME
    5
    		2025-01-03 00:00:00.0002025-01-01 00:00:00.000January9089263304159.89759506289244333318995.0658935TRANSACTIONS
    6
    		2025-01-03 00:00:00.0002025-01-01 00:00:00.000January47783835953366.631462346159594731.22USD_VOLUME
    7
    		2025-01-04 00:00:00.0002025-01-01 00:00:00.000January9203173006621.7841591381276036325616.8500526TRANSACTIONS
    8
    		2025-01-04 00:00:00.0002025-01-01 00:00:00.000January37718834495325.421839534194090056.64USD_VOLUME
    9
    		2025-01-05 00:00:00.0002025-01-01 00:00:00.000January8931032340477.54083835470586338666094.3908909TRANSACTIONS
    10
    		2025-01-05 00:00:00.0002025-01-01 00:00:00.000January37100425712970.222210538219803026.86USD_VOLUME
    11
    		2025-01-06 00:00:00.0002025-01-01 00:00:00.000January9515562950875.74193461565741941616970.1328255TRANSACTIONS
    12
    		2025-01-06 00:00:00.0002025-01-01 00:00:00.000January42483545525875.772635373265328902.63USD_VOLUME
    13
    		2025-01-07 00:00:00.0002025-01-01 00:00:00.000January9741573449646.42507111663157645066616.5578966TRANSACTIONS
    14
    		2025-01-07 00:00:00.0002025-01-01 00:00:00.000January51677944080246.913152152309409149.54USD_VOLUME
    15
    		2025-01-08 00:00:00.0002025-01-01 00:00:00.000January9730945947179.93451177760467051013796.4924084TRANSACTIONS
    16
    		2025-01-08 00:00:00.0002025-01-01 00:00:00.000January54728848609926.183699440358019075.72USD_VOLUME
    17
    		2025-01-09 00:00:00.0002025-01-01 00:00:00.000January10560495123336.97669108866071956137133.4690995TRANSACTIONS
    18
    		2025-01-09 00:00:00.0002025-01-01 00:00:00.000January58491248923319.094284352406942394.81USD_VOLUME
    19
    		2025-01-10 00:00:00.0002025-01-01 00:00:00.000January10458824093120.2910397970660160230253.7601392TRANSACTIONS
    20
    		2025-01-10 00:00:00.0002025-01-01 00:00:00.000January72914642858495.55013498449800890.31USD_VOLUME
    ...
    180
    26KB
    9s