SELECT

block_timestamp,

tx_hash,

tx_receiver,

tx_signer,

tx:actions[0]:FunctionCall:deposit/pow(10,24) near_staked,

tx:receipt[0]:outcome:executor_id as executor_id,

tx:receipt[0]:outcome:logs as logs,

regexp_substr(logs, 'Contract total staked balance is\\W+\\w+') as contract_total,

trim(REGEXP_REPLACE(contract_total, '[a-z/-/A-z/./#/*"]', '')) as contract_regexp,

try_to_numeric(contract_regexp) as amount,

regexp_substr(logs, 'Total number of shares\\W+\\w+') as shares,

trim(REGEXP_REPLACE(shares, '[a-z/-/A-z/./#/*"]', '')) as shares_regexp,

try_to_numeric(shares_regexp) as shares_amount

FROM near.core.fact_transactions

WHERE tx_hash IN ( SELECT tx_hash

FROM near.core.fact_actions_events_function_call

WHERE method_name IN ('deposit_and_stake'))

AND shares_amount IS NOT NULL

), tab4 as (

SELECT

date_trunc('week', block_timestamp) as DAY,

tx_receiver as validator_address,

avg(amount)/pow(10,24) as TOKEN_FLOW_TOTAL

FROM tab1

GROUP BY 1,2

), tab5 as (

SELECT

*,

(sum(token_flow_total) OVER (PARTITION BY validator_address ORDER BY DAY)) / (sum(token_flow_total) OVER (ORDER BY DAY)) as voting_ratio

FROM tab4

ORDER BY 1,4 DESC

), tab6 as (

SELECT

*,