WITH unstake AS (

SELECT

tx_hash,

(ethereum.public.udf_hex_to_int(data :: STRING)/pow(10,18)) AS value

FROM blast.core.fact_event_logs

WHERE origin_to_address = '0xe1784da2b8f42c31fb729e870a4a8064703555c2'

AND contract_address = '0xe1784da2b8f42c31fb729e870a4a8064703555c2'

AND origin_function_signature = '0xc148cf85'

),

datas AS (

SELECT

block_timestamp,

tx.tx_hash,

from_address,

unstake.value AS value,

tx_fee,

'Unstake' AS label

FROM blast.core.fact_transactions tx

INNER JOIN unstake ON unstake.tx_hash = tx.tx_hash

SELECT

date_trunc('hour',block_timestamp) AS hour,

count(tx_hash) AS transactions,

count(DISTINCT from_address) AS users,

SUM(value) AS unstake_value,

SUM(tx_fee) AS fee,

SUM(unstake_value) OVER(ORDER BY hour ASC) AS cum_unstakke_value,