SELECT

CAST(BLOCK_TIMESTAMP AS DATE) AS TransferDate,

COUNT(*) AS TotalTokenTransfers

FROM ethereum.core.fact_token_transfers

WHERE BLOCK_TIMESTAMP >= DATEADD(day, -30, CURRENT_DATE)

GROUP BY TransferDate

),

TransactionsDaily AS (

SELECT

CAST(BLOCK_TIMESTAMP AS DATE) AS TransactionDate,

COUNT(*) AS TotalTransactions

FROM ethereum.core.fact_transactions

WHERE BLOCK_TIMESTAMP >= DATEADD(day, -30, CURRENT_DATE) and STATUS = 'SUCCESS'

GROUP BY TransactionDate

SELECT

COALESCE(tt.TransferDate, td.TransactionDate) AS Date,

COALESCE(TotalTokenTransfers, 0) / (24 * 60 * 60) AS TokenTransfersPerSecond,

COALESCE(TotalTransactions, 0) / (24 * 60 * 60) AS TransactionsPerSecond

FROM TokenTransfersDaily tt

FULL OUTER JOIN TransactionsDaily td

ON tt.TransferDate = td.TransactionDate

ORDER BY Date;