SELECT

TO_TIMESTAMP_NTZ(CONCAT(CAST(e.block_timestamp AS DATE)::STRING, ' ', EXTRACT(HOUR FROM e.block_timestamp)::STRING, ':00:00')) AS tx_datetime,

COUNT(e.tx_id) AS tx_count,

COUNT(DISTINCT e.instruction:accounts[0]::STRING) AS unique_users,

SUM(b.burn_amount / 1e9) AS ore_bridged

FROM

solana.core.fact_events e

JOIN

solana.defi.fact_token_burn_actions b

ON

e.tx_id = b.tx_id

WHERE

e.succeeded

AND e.program_id = 'oreV2ZymfyeXgNgBdqMkumTqqAprVqgBWQfoYkrtKWQ'

AND e.instruction:accounts[2] = 'oreoU2P8bN6jkk3jbaiVxYnG1dCXcYxwhwyK9jSybcp'

AND e.instruction:accounts[3] = 'oreoN2tQbHXVaZsr3pf66A48miqcBXCDJozganhEJgz'

AND CAST(e.block_timestamp AS DATE) > CURRENT_DATE - INTERVAL '365 days'

GROUP BY

tx_datetime

tx_datetime,

SUM(tx_count) OVER (ORDER BY tx_datetime) AS cumulative_tx_count,

SUM(unique_users) OVER (ORDER BY tx_datetime) AS cumulative_unique_users,

SUM(ore_bridged) OVER (ORDER BY tx_datetime) AS cumulative_ore_bridged

FROM

hourly_data

ORDER BY

tx_datetime ASC;