SELECT

DATE_TRUNC('month', BLOCK_TIMESTAMP) AS month,

COUNT(*) AS total_transactions,

SUM(VALUE) AS total_transaction_value,

SUM(TX_FEE) AS total_gas_fees,

COUNT(*) / (30 * 24 * 60 * 60) AS transactions_per_second, -- Approximate TPS for the month

SUM(CASE WHEN TX_SUCCEEDED THEN 1 ELSE 0 END) AS successful_transactions,

SUM(CASE WHEN NOT TX_SUCCEEDED THEN 1 ELSE 0 END) AS failed_transactions

FROM ink.core.fact_transactions

WHERE BLOCK_TIMESTAMP BETWEEN '2025-01-01' AND '2025-02-28'

GROUP BY 1

),

token_transfers AS (

SELECT

DATE_TRUNC('month', BLOCK_TIMESTAMP) AS month,

COUNT(*) AS total_token_transfers,

SUM(AMOUNT_USD) AS total_token_transfer_value

FROM ink.core.ez_token_transfers

WHERE BLOCK_TIMESTAMP BETWEEN '2025-01-01' AND '2025-02-28'

GROUP BY 1

SELECT

MONTHNAME(month) AS month, -- Extract month name (e.g., "January", "February")

-- Total Transactions

COALESCE(SUM(total_transactions), 0) AS total_transactions,

-- Total Transaction Value

COALESCE(SUM(total_transaction_value), 0) AS total_transaction_value,

-- Total Gas Fees

COALESCE(SUM(total_gas_fees), 0) AS total_gas_fees,

-- Transactions Per Second (TPS)

COALESCE(SUM(transactions_per_second), 0) AS transactions_per_second,

-- Transaction Success Rate

COALESCE(SUM(successful_transactions) * 100.0 / NULLIF(SUM(total_transactions), 0), 0) AS transaction_success_rate,