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

), tab2 as (

SELECT

date_trunc('week', block_timestamp) as week,

tx_receiver as stake_pool,

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

FROM tab1

GROUP BY 1,2

SELECT

week,

count(DISTINCT stake_pool) as active_pools

FROM tab2

GROUP BY 1