SELECT

DATE_TRUNC('DAY', BLOCK_TIMESTAMP) AS transaction_day,

STATUS,

COUNT(*) AS transaction_count

FROM

base.core.fact_transactions;

WHERE

BLOCK_TIMESTAMP >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())

AND BLOCK_TIMESTAMP < CURRENT_TIMESTAMP()

GROUP BY

transaction_day,

STATUS

),

Daily_Successful_Transactions AS (

SELECT

transaction_day,

transaction_count AS successful_transactions

FROM

Daily_Transactions

WHERE

STATUS = 'SUCCESS'

transaction_day,

COALESCE(successful_transactions, 0) AS successful_transactions

FROM

Daily_Successful_Transactions

ORDER BY

transaction_day;