WITH

dim_convert AS (

SELECT block_timestamp, block_id

FROM thorchain.core.dim_block

),

active_node_adƒdresses AS

SELECT block_timestamp, current_status, node_address

FROM thorchain.core.fact_update_node_account_status_events

QUALIFY (block_timestamp = MAX(block_timestamp) OVER(PARTITION BY node_address))

AND (current_status = 'Active')

),

churn_blocks AS (

SELECT DISTINCT block_timestamp

FROM thorchain.core.fact_update_node_account_status_events

ORDER BY block_timestamp DESC --Necessary for the 'LIMIT 2' to keep the right ones!

LIMIT 2

),

x_axis_start AS (

SELECT MIN(block_timestamp) AS block_timestamp

FROM churn_blocks

),

before_reference AS

SELECT DISTINCT block_timestamp,

SUM(slash_points) OVER(PARTITION BY node_address ORDER BY block_timestamp ASC) AS cumulative_slash_points,

CONCAT('.', RIGHT(node_address, 4)) AS node_abbreviation

FROM thorchain.core.fact_slash_points

WHERE (block_timestamp >= (SELECT block_timestamp FROM x_axis_start))

AND (reason = 'fail keysign')

AND (node_address IN (SELECT DISTINCT node_address FROM active_node_addresses))