SELECT

block_timestamp,

tx_hash,

receiver_id,

replace(value, 'EVENT_JSON:') as logs_cleaned,

split(logs_cleaned, ' ') as split_logs,

try_parse_json(logs_cleaned):data as data_log,

nvl(try_parse_json(logs_cleaned):event, try_parse_json(logs_cleaned):type) as event,

case

-- when receiver_id IN ('aurora.pool.near', 'aurora.poolv1.near') and split_logs[1] = 'staking' then 'Stake NEAR (Aurora Pool)'

when split_logs[1] = 'staking' then 'Stake NEAR (Natively)'

when receiver_id = 'meta-pool.near' and event = 'STAKE' then 'Liquid Stake NEAR (Meta)'

when receiver_id = 'linear-protocol.near' and event = 'stake' then 'Liquid Stake NEAR (Linear)'

when receiver_id = 'nearx.stader-labs.near' and event = 'deposit_and_stake' then 'Liquid Stake NEAR (Stader)'

else null end as event_action,

-- case when event_action = 'Stake NEAR (Aurora Pool)' then try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) end as native_aurora_stake,

-- case when event_action = 'Stake NEAR (Other Pools)' then try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) end as native_non_aurora_stake,

-- try_to_numeric(replace(split_logs[2],'.'))/pow(10,24) as native_stake, -- native staking pool relies on log

-- try_parse_json(logs_cleaned):amount/pow(10,24) as meta_stake_amount, -- meta pool / relies on json

-- data_log[0]:amount/pow(10,24) as stader_stake_amount, -- stader pool

-- data_log[0]:staked_amount/pow(10,24) as linear_stake_amount, -- linear pool

regexp_substr(status_value, 'Success') as reg_success

-- coalesce(native_stake, meta_stake_amount, stader_stake_amount, linear_stake_amount) as near_staked

FROM near.core.fact_receipts,

table(flatten(input =>logs))

WHERE reg_success IS NOT NULL -- filter out successful transactions

),

USER_FIRST_STAKE AS (

SELECT

tx_signer,

event_action,

min(date_trunc('week',block_timestamp)) as first_stake

FROM near_event

LEFT JOIN (SELECT tx_hash as tx_hash1, tx_signer, tx_receiver FROM near.core.fact_transactions) ON tx_hash = tx_hash1