SELECT

MIN(trunc(block_timestamp, 'week')) AS start_date,

MAX(trunc(block_timestamp, 'week')) AS end_date

FROM crosschain.olas.ez_olas_staking

),

weeks AS (

SELECT start_date AS week

FROM date_range

UNION ALL

SELECT DATEADD(week, 1, week)

FROM weeks

WHERE week < (SELECT end_date FROM date_range)

),

program_weeks AS (

SELECT

weeks.week,

programs.program_name

FROM weeks

CROSS JOIN (SELECT DISTINCT program_name FROM crosschain.olas.ez_olas_staking) AS programs

),

program_week_data AS (

SELECT

pw.week,

pw.program_name,

COALESCE(SUM(CASE WHEN event_name = 'Deposit' THEN amount ELSE 0 END), 0) AS staked,

COALESCE(SUM(CASE WHEN event_name = 'Withdrawn' THEN amount ELSE 0 END), 0) AS unstaked,

COALESCE(SUM(CASE WHEN event_name = 'Deposit' THEN amount ELSE 0 END) - SUM(CASE WHEN event_name = 'Withdrawn' THEN amount ELSE 0 END), 0) AS netflow,

COALESCE(COUNT(DISTINCT origin_from_address), 0) AS active_users,

COALESCE(COUNT(DISTINCT tx_hash), 0) AS actions

FROM program_weeks pw

LEFT JOIN crosschain.olas.ez_olas_staking t

ON pw.week = trunc(t.block_timestamp, 'week')

AND pw.program_name = t.program_name

GROUP BY pw.week, pw.program_name