date_range AS (

SELECT

DATEADD(day, seq4(), '2023-01-01'::DATE) AS day

FROM

TABLE(GENERATOR(ROWCOUNT => 366)) -- Generate 366 rows to cover the entire year

WHERE

day <= '2024-01-01' -- Ensure the date range covers the necessary period

),

combined_transactions AS (

SELECT

'ethereum' AS dataset,

BLOCK_TIMESTAMP,

FACT_TRANSACTIONS_ID

FROM ethereum.core.fact_transactions

WHERE DATE_TRUNC('day', BLOCK_TIMESTAMP) BETWEEN '2023-01-01' AND '2024-01-01'

UNION ALL

SELECT

'polygon' AS dataset,

BLOCK_TIMESTAMP,

FACT_TRANSACTIONS_ID

FROM polygon.core.fact_transactions

WHERE DATE_TRUNC('day', BLOCK_TIMESTAMP) BETWEEN '2023-01-01' AND '2024-01-01'

UNION ALL

SELECT

'bsc' AS dataset,

BLOCK_TIMESTAMP,

FACT_TRANSACTIONS_ID

FROM bsc.core.fact_transactions

WHERE DATE_TRUNC('day', BLOCK_TIMESTAMP) BETWEEN '2023-01-01' AND '2024-01-01'

UNION ALL