SELECT

DATEADD(day, 1, date(block_timestamp)) AS current_date, -- for grouping yesterday's transactions with current transactions

COUNT(DISTINCT tx_hash) AS ystd_tx_vol

FROM polygon.core.fact_transactions

WHERE status = 'SUCCESS'

GROUP BY 1

),

current_txs AS (

SELECT

date(block_timestamp) AS date,

COUNT(DISTINCT tx_hash) AS tx_vol

FROM polygon.core.fact_transactions

WHERE status = 'SUCCESS'

GROUP BY 1

),

txs AS (

SELECT

c.date AS date,

c.tx_vol AS tx_vol,

y.ystd_tx_vol AS prev_tx_vol

-- CASE WHEN c.date >= '2022-07-01' THEN 'Orange' ELSE 'Blue' END AS colour

FROM current_txs c

JOIN ystd_txs y ON c.date = y.current_date

),

interact AS (

SELECT

block_timestamp,

from_address

FROM polygon.core.fact_transactions

UNION ALL

SELECT

block_timestamp,