SELECT tx_hash

FROM near.core.fact_actions_events_function_call

WHERE method_name IN ('deposit_and_stake')

),data_one as (

SELECT

block_timestamp,

tx_hash,

tx_receiver,

tx_signer,

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

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

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

regexp_substr(outcome_logs, 'Contract total staked balance is\\W+\\w+') as total_staked_contract,

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

try_to_numeric(total_regexp_contract) as to_numeric_amount,

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

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

try_to_numeric(regexp_shared) as total_amount_of_shares

FROM near.core.fact_transactions

WHERE tx_hash IN (select tx_hash from data_total)

AND total_amount_of_shares IS NOT NULL

), data_two as (

SELECT

date_trunc('week', block_timestamp) as weekly_date,

tx_receiver,

avg(total_amount_of_shares)/pow(10,24) as average_token_flow

FROM data_one

GROUP BY weekly_date, tx_receiver

), data_three as (

SELECT

*,

(sum(average_token_flow) OVER (PARTITION BY tx_receiver ORDER BY weekly_date)) / (sum(average_token_flow) OVER (ORDER BY weekly_date)) as calculation

FROM data_two

ORDER BY 1,4 DESC

), data_four as (