SELECT

DATE_TRUNC('day', BLOCK_TIMESTAMP) AS date,

COUNT(TX_HASH) AS total_transactions,

SUM(CASE WHEN tx_succeeded = 'TRUE' THEN 1 ELSE 0 END) AS successful_transactions,

successful_transactions/total_transactions as success_rate,

SUM(tx_fee) AS fees,

AVG(tx_fee) AS avg_tx_fee,

COUNT(DISTINCT FROM_ADDRESS) AS unique_users,

avg(DATEDIFF(MINUTE, INSERTED_TIMESTAMP, BLOCK_TIMESTAMP)) AS latency_MINUTEs

FROM

flow.core_evm.fact_transactions

--WHERE

-- BLOCK_TIMESTAMP >= DATEADD(day, -30, CURRENT_DATE) and block_timestamp<current_date

GROUP BY

1

),

non_evm_data AS (

SELECT

DATE_TRUNC('day', x.BLOCK_TIMESTAMP) AS date,

COUNT(distinct x.TX_ID) AS total_transactions,

SUM(CASE WHEN x.TX_SUCCEEDED = 'TRUE' THEN 1 ELSE 0 END) AS successful_transactions,

successful_transactions/total_transactions as success_rate,

SUM(y.event_data:amount) AS fees,

AVG(y.event_data:amount) AS avg_tx_fee,

COUNT(DISTINCT x.PAYER) AS unique_users,

avg(DATEDIFF(MINUTE, x.INSERTED_TIMESTAMP, x.BLOCK_TIMESTAMP)) AS latency_MINUTEs

FROM

flow.core.fact_transactions x

join flow.core.fact_events y on x.tx_id=y.tx_id

WHERE

-- x.BLOCK_TIMESTAMP >= DATEADD(day, -30, CURRENT_DATE) and

and event_Type='FeesDeducted' and x.block_timestamp<current_date

GROUP BY

1