SELECT

date_trunc('week', block_timestamp) AS week

, event_inputs:validatorId as validatorId

, sum(event_inputs:amount / pow(10,18)) as stakingAmount

, sum(stakingAmount) OVER (PARTITION BY validatorId ORDER BY week) AS cumulativeStakingAmount

FROM ethereum.core.fact_event_logs

WHERE block_timestamp >= '2022-07-01'

AND contract_address = lower('0xa59c847bd5ac0172ff4fe912c5d29e5a71a7512b')

AND origin_function_signature = '0x6ab15071' -- buyVoucher

AND event_name = 'ShareMinted'

GROUP BY week, validatorId

),

topVal AS (

SELECT validatorId

FROM cte

WHERE week = (SELECT max(week) FROM cte)

ORDER BY cumulativeStakingAmount DESC

LIMIT 9

SELECT *

FROM cte

WHERE validatorId IN (SELECT * FROM topVal)

ORDER BY week, validatorId