Query editor
/*WITH seasons AS (SELECT DATE '2023-01-19' AS start_date, DATE '2023-04-05' AS end_date, 'Season 3' AS seasonUNION ALL SELECT DATE '2023-04-06', DATE '2023-04-12', 'Break S3-S4'UNION ALL SELECT DATE '2023-04-13', DATE '2023-04-26', 'Reflection Period S3-S4'UNION ALL SELECT DATE '2023-04-27', DATE '2023-05-10', 'Special Voting Cycle #12a'UNION ALL SELECT DATE '2023-05-11', DATE '2023-05-17', 'Grants Council Nominations'UNION ALL SELECT DATE '2023-05-18', DATE '2023-05-31', 'Special Voting Cycle #12b'UNION ALL SELECT DATE '2023-06-01', DATE '2023-06-07', 'Grace Period'UNION ALL SELECT DATE '2023-06-08', DATE '2023-09-20', 'Season 4'UNION ALL SELECT DATE '2023-09-21', DATE '2023-09-27', 'Break S4-S5'UNION ALL SELECT DATE '2023-09-28', DATE '2023-11-22', 'Reflection Period S4-S5'UNION ALL SELECT DATE '2023-11-23', DATE '2024-01-03', 'Governance Break'UNION ALL SELECT DATE '2024-01-04', DATE '2024-05-08', 'Season 5'UNION ALL SELECT DATE '2024-05-09', DATE '2024-05-22', 'Reading Period S5-S6'UNION ALL SELECT DATE '2024-05-23', DATE '2024-06-05', 'Special Voting Cycle #23a'UNION ALL SELECT DATE '2024-06-06', DATE '2024-06-12', 'Candidate Week'UNION ALL SELECT DATE '2024-06-13', DATE '2024-06-19', 'Special Voting Cycle #23b'UNION ALL SELECT DATE '2024-06-20', DATE '2024-06-26', 'Week Off'UNION ALL SELECT DATE '2024-06-27', DATE '2024-11-20', 'Season 6'UNION ALL SELECT DATE '2024-11-21', DATE '2024-11-27', 'Retro Week S6-S7'UNION ALL SELECT DATE '2024-11-28', DATE '2024-12-18', 'Special Voting Cycle #31a'UNION ALL SELECT DATE '2024-12-19', DATE '2025-01-01', 'Nominations'UNION ALL SELECT DATE '2025-01-02', DATE '2025-01-15', 'Special Voting Cycle #31b'UNION ALL SELECT DATE '2025-01-16', DATE '2025-06-11', 'Season 7'),*/WITH seasons AS (SELECT DATE '2023-01-19' AS start_date, DATE '2023-04-05' AS end_date, DATE '2023-02-21' AS snapshot_date, 'Season 3' AS seasonUNION ALL SELECT DATE '2023-04-06', DATE '2023-06-07', DATE '2023-05-06', 'RP S3-S4'UNION ALL SELECT DATE '2023-06-08', DATE '2023-09-20', DATE '2023-08-01', 'Season 4'UNION ALL SELECT DATE '2023-09-21', DATE '2024-01-03', DATE '2023-11-15', 'RP S4-S5'UNION ALL SELECT DATE '2024-01-04', DATE '2024-05-08', DATE '2024-01-22', 'Season 5'UNION ALL SELECT DATE '2024-05-09', DATE '2024-06-26', DATE '2024-06-01', 'RP S5-S6'UNION ALL SELECT DATE '2024-06-27', DATE '2024-11-20', DATE '2024-08-30', 'Season 6'UNION ALL SELECT DATE '2024-11-21', DATE '2025-01-15', DATE '2024-12-20', 'RP S6-S7'UNION ALL SELECT DATE '2025-01-16', DATE '2025-06-11', DATE '2025-03-15', 'Season 7'),week_starts AS (SELECT date_trunc('week', min_date) + (INTERVAL '7' DAY * (seq - 1)) AS week_startFROM (SELECT MIN(date_trunc('week', evt_block_time)) AS min_date,MAX(date_trunc('week', evt_block_time)) AS max_dateFROM op_optimism.governancetoken_evt_delegatevoteschanged) datesCROSS JOIN UNNEST(SEQUENCE(0, CAST(DATE_DIFF('week', min_date, max_date) AS INT))) AS t(seq)),delegation_changes AS (SELECTevt_block_time,delegate,newBalance / POW(10, 18) AS running_balanceFROM op_optimism.governancetoken_evt_delegatevoteschanged),weekly_snapshots AS (SELECT DISTINCTw.week_start,dc.delegate,LAST_VALUE(dc.running_balance) OVER (PARTITION BY dc.delegate, w.week_startORDER BY dc.evt_block_timeROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING