POOL_ADDRESS,

a.tx_hash, b.tx_signer as staker, b.tx_receiver as validator,

stake_amount/pow(10,24) as amount, GAS_USED/pow(10,12) as gas_used, --Ggas

TRANSACTION_FEE/pow(10,24) as tx_fee -- near

from near.core.dim_staking_actions a join near.core.fact_transactions b on a.tx_hash = b.tx_hash

where action = 'Stake' and date >= '2022-01-01' and tx_status = 'Success')

--date, POOL_ADDRESS,

count(DISTINCT tx_hash) as tx_count,

count(DISTINCT staker) as stakers,

count(DISTINCT validator) as validators,

tx_count/stakers as stperstaking,

tx_count/validators as stpervalidators,

min(amount) as min_amount,

median(amount) as median_amount,

avg(amount) as avg_amount,

max(amount) as max_amount,

sum(amount) as sum_amount,

min(gas_used) as min_gas_used,

median(gas_used) as median_gas_used,

avg(gas_used) as avg_gas_used,

max(gas_used) as max_gas_used,

min(tx_fee) as min_tx_fee,

median(tx_fee) as median_tx_fee,

avg(tx_fee) as avg_tx_fee,

max(tx_fee) as max_tx_fee,

sum(gas_used) as gas_used_total,

sum(tx_fee) as sum_tx_fee

from df