WITH ystd_txs AS (
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,