-- forked from adriaparcerisas / solana decent 8 @ https://flipsidecrypto.xyz/adriaparcerisas/q/naka-comparisons-IoiAL4 WITH flow_sol_base as ( SELECT trunc(block_timestamp,'week') as week, --case when action in ('DelegatorTokensCommitted','TokensCommitted') then 'Staking', --when action in ('UnstakedTokensWithdrawn','DelegatorUnstakedTokensWithdrawn') then 'Unstaking' -- end as actions, node_id as validator, sum(amount) as flow_delegated from flow.core.ez_staking_actions where action in ('DelegatorTokensCommitted','TokensCommitted') group by 1,2 order by 1 asc ), flow_sol_base2 as ( SELECT trunc(block_timestamp,'week') as week, --case when action in ('DelegatorTokensCommitted','TokensCommitted') then 'Staking', --when action in ('UnstakedTokensWithdrawn','DelegatorUnstakedTokensWithdrawn') then 'Unstaking' -- end as actions, node_id as validator, sum(amount) as flow_withdrawn from flow.core.ez_staking_actions where action in ('UnstakedTokensWithdrawn','DelegatorUnstakedTokensWithdrawn') group by 1,2 order by 1 asc ), flow_sol_base3 as(select ifnull(a.week, b.week) as weeks, ifnull(a.validator, b.validator) as vote_accounts, contract_name as validator_names, ifnull(flow_delegated, 0) as flow_delegatedz, ifnull(flow_withdrawn, 0) as flow_withdrawnz, ifnull(flow_delegatedz, 0) - ifnull(flow_withdrawnz,0) as net_flow_delegated, rank() over (partition by weeks order by net_flow_delegated desc) as validator_ranks from flow_sol_base a full outer join flow_sol_base2 b on a.week=b.week and a.validator=b.validator --and a.vote_account = b.vote_account and a.validator_rank = b.validator_rank and stake_authority = withdraw_destination left join flow.core.dim_contract_labels c on ifnull(a.validator, b.validator) = c.account_address --where net_flow_delegated > 0 --group by 1,2,3,4,5 order by 1 desc ), flow_sol_base4 as( select weeks, vote_accounts, validator_names, validator_ranks, avg(net_flow_delegated) as average_flow_delegated, median(net_flow_delegated) as median_flow_delegated, sum(net_flow_delegated) as total_flow_delegated, sum(total_flow_delegated) over (partition by vote_accounts order by weeks) as cumulative_flow_delegated from flow_sol_base3 --where validator_ranks <= 50 group by 1,2,3,4 order by 1 asc, 5 asc ), flow_sol_base5 as (select weeks, net_flow_delegated, sum(net_flow_delegated) over (order by weeks) as cumulative_flow_delegated, count(*) from flow_sol_base3 group by 1,2 ), flow_sol_base6 as (select weeks, avg(cumulative_flow_delegated) as cosmos_osmosis_weekly_cumulative_flow_delegated from flow_sol_base5 group by 1 ), flow_cosmos_osmosis_sol_stats as ( SELECT weeks, --validator_ranks, 33 as bizantine_fault_tolerance, sum(cosmos_osmosis_weekly_cumulative_flow_delegated) as total_flow_delegated_so_far, (total_flow_delegated_so_far*bizantine_fault_tolerance)/100 as threshold--, --sum(total_flow_delegated) over (partition by weeks order by validator_ranks asc) as total_flow_delegated_by_ranks, --count(distinct vote_accounts) as validators from flow_sol_base6 group by 1,2 order by 1 asc ), flow_cosmos_osmosis_sol_stats2 as (select weeks, vote_accounts, sum(total_flow_delegated) as total_flow_delegatedz, sum(total_flow_delegatedz) over (partition by vote_accounts order by weeks) as cumulative_flow_delegatedz from flow_sol_base4 group by 1,2 ), flow_cosmos_osmosis_sol_stats3 as (select *, 1 as numbering, sum(numbering) over (partition by weeks order by cumulative_flow_delegatedz desc) as rank from flow_cosmos_osmosis_sol_stats2), flow_cosmos_osmosis_sol_stats4 as ( SELECT weeks, vote_accounts, CUMULATIVE_FLOW_DELEGATEDZ, rank, sum(CUMULATIVE_FLOW_DELEGATEDZ) over (partition by weeks order by rank asc) as total_staked --count(case when total_staked) --sum(1) over (partition by weeks order by stake_rank) as nakamoto_coeff from flow_cosmos_osmosis_sol_stats3 order by rank asc), flow_final_nal as ( SELECT a.weeks, vote_accounts, 'nakamoto', 'coefficient', count(case when total_staked <= threshold then 1 end) as nakamoto_coeff from flow_cosmos_osmosis_sol_stats4 a join flow_cosmos_osmosis_sol_stats b on a.weeks = b.weeks --where a.weeks >=CURRENT_DATE-INTERVAL '6 weekS' group by 1,2 order by 1 asc ), flow as ( SELECT weeks, sum(nakamoto_coeff) as naka_coeff from flow_final_nal group by 1 order by 1 asc ), near_transactions as ( SELECT tx_hash FROM near.core.fact_actions_events_function_call WHERE method_name IN ('deposit_and_stake') ), near_stakes as ( SELECT block_timestamp, tx_hash as tx, tx_receiver as validator, tx_signer as delegator, tx:actions[0]:FunctionCall:deposit/pow(10,24) near_staked FROM near.core.fact_transactions WHERE tx_hash in (select * from near_transactions) ), near_cosmos_osmosis_weekly as ( SELECT trunc(block_timestamp,'week') as weeks, tx, validator, near_staked --amount_staked/pow(10,24) as near_staked FROM near_stakes WHERE near_staked is not null ), near_cosmos_osmosis_totals as ( SELECT weeks, sum(near_staked) as week_near_staked, sum(week_near_staked) over (order by weeks)as total_near_staked from near_cosmos_osmosis_weekly group by 1 order by 1 ), near_terra_cosmos_osmosis_ranking as ( SELECT weeks, validator, count(distinct tx) as txs, sum(near_staked) as total_near_delegated, sum(total_near_delegated) over (partition by validator order by weeks) as cumulative_near_delegated FROM near_cosmos_osmosis_weekly group by 1,2 ), near_terra_flow_cosmos_osmosis_sol_stats as ( SELECT weeks, 50 as bizantine_fault_tolerance, total_near_staked, (total_near_staked*bizantine_fault_tolerance)/100 as threshold--, --sum(total_sol_delegated) over (partition by weeks order by validator_ranks asc) as total_sol_delegated_by_ranks, --count(distinct vote_accounts) as validators from near_cosmos_osmosis_totals ), near_terra_flow_cosmos_osmosis_sol_stats2 as ( select *, 1 as numbering, sum(numbering) over (partition by weeks order by cumulative_near_delegated desc) as rank from near_terra_cosmos_osmosis_ranking ), near_terra_flow_cosmos_osmosis_sol_stats3 as ( SELECT weeks, validator, cumulative_near_delegated, rank, sum(cumulative_near_delegated) over (partition by weeks order by rank asc) as total_staked --count(case when total_staked) --sum(1) over (partition by weeks order by stake_rank) as nakamoto_coeff from near_terra_flow_cosmos_osmosis_sol_stats2 order by rank asc ), near_final as ( SELECT a.weeks, validator, count(case when total_staked <= threshold then 1 end) as nakamoto_coeff from near_terra_flow_cosmos_osmosis_sol_stats3 a join near_terra_flow_cosmos_osmosis_sol_stats b on a.weeks = b.weeks group by 1,2 order by 1 asc ), near as ( select weeks, sum(nakamoto_coeff) as naka_coeff from near_final group by 1 order by 1 asc ), terra_cosmos_osmosis_stakes1 as ( SELECT x.block_timestamp, x.tx_id, y.attribute_value as validator, case when REGEXP_SUBSTR(x.attribute_value,'i.*') is not null then REGEXP_SUBSTR(x.attribute_value,'i.*') else REGEXP_SUBSTR(x.attribute_value,'u.*') end as attribute_name, REPLACE(x.attribute_value, attribute_name, '')::decimal/pow(10,6)*(-1) as staked FROM terra.core.fact_msg_attributes x join terra.core.fact_msg_attributes y on x.tx_id=y.tx_id where x.msg_type='unbond' and x.attribute_key='amount' and y.msg_type='unbond' and y.attribute_key='validator' having staked is not null and attribute_name is not null and staked>-1e9 ), terra_cosmos_osmosis_stakes2 as ( SELECT x.block_timestamp, x.tx_id, y.attribute_value as validator, case when REGEXP_SUBSTR(x.attribute_value,'i.*') is not null then REGEXP_SUBSTR(x.attribute_value,'i.*') else REGEXP_SUBSTR(x.attribute_value,'u.*') end as attribute_name, REPLACE(x.attribute_value, attribute_name, '')::decimal/pow(10,6) as staked FROM terra.core.fact_msg_attributes x join terra.core.fact_msg_attributes y on x.tx_id=y.tx_id where x.msg_type='delegate' and x.attribute_key in ('new_shares','amount') and y.msg_type='delegate' and y.attribute_key='validator' having staked is not null and attribute_name is not null and staked<1e9 ), terra_cosmos_osmosis_stakes3 as ( SELECT x.block_timestamp, x.tx_id, y.attribute_value as validator, case when REGEXP_SUBSTR(x.attribute_value,'i.*') is not null then REGEXP_SUBSTR(x.attribute_value,'i.*') else REGEXP_SUBSTR(x.attribute_value,'u.*') end as attribute_name, REPLACE(x.attribute_value, attribute_name, '')::decimal/pow(10,6)*(-1) as staked FROM terra.core.fact_msg_attributes x join terra.core.fact_msg_attributes y on x.tx_id=y.tx_id where x.msg_type='redelegate' and x.attribute_key='amount' and y.msg_type='redelegate' and y.attribute_key='source_validator' having staked is not null and attribute_name is not null and staked>-1e9 ), terra_cosmos_osmosis_stakes4 as ( SELECT x.block_timestamp, x.tx_id, y.attribute_value as validator, case when REGEXP_SUBSTR(x.attribute_value,'i.*') is not null then REGEXP_SUBSTR(x.attribute_value,'i.*') else REGEXP_SUBSTR(x.attribute_value,'u.*') end as attribute_name, REPLACE(x.attribute_value, attribute_name, '')::decimal/pow(10,6) as staked FROM terra.core.fact_msg_attributes x join terra.core.fact_msg_attributes y on x.tx_id=y.tx_id where x.msg_type='redelegate' and x.attribute_key='amount' and y.msg_type='redelegate' and y.attribute_key='destination_validator' having staked is not null and attribute_name is not null and staked<1e9 ), terra_cosmos_osmosis_stakes5 as ( SELECT * FROM terra_cosmos_osmosis_stakes1 UNION SELECT * FROM terra_cosmos_osmosis_stakes2 UNION SELECT * FROM terra_cosmos_osmosis_stakes3 UNION SELECT * FROM terra_cosmos_osmosis_stakes4 ) , terra_cosmos_osmosis_weekly as ( SELECT trunc(block_timestamp,'week') as days, validator, sum(staked) as osmo_staked FROM terra_cosmos_osmosis_stakes5 WHERE staked is not null group by 1,2 ), terra_cosmos_osmosis_ranking as ( SELECT days, validator, osmo_staked as total_near_delegated, sum(total_near_delegated) over (partition by validator order by days) as cumulative_near_delegated FROM terra_cosmos_osmosis_weekly group by 1,2,3 ), terra_cosmos_osmosis_totals as ( SELECT days, sum(total_near_delegated) as week_osmo_staked, sum(week_osmo_staked) over (order by days) as total_osmo_staked from terra_cosmos_osmosis_ranking where cumulative_near_delegated >0 group by 1 order by 1 ), terra_flow_cosmos_osmosis_sol_stats as ( SELECT days, 50 as bizantine_fault_tolerance, total_osmo_staked, (total_osmo_staked*bizantine_fault_tolerance)/100 as threshold--, --sum(total_sol_delegated) over (partition by days order by validator_ranks asc) as total_sol_delegated_by_ranks, --count(distinct vote_accounts) as validators from terra_cosmos_osmosis_totals ), terra_flow_cosmos_osmosis_sol_stats2 as ( select *, 1 as numbering, sum(numbering) over (partition by days order by cumulative_near_delegated desc) as rank from terra_cosmos_osmosis_ranking where cumulative_near_delegated >0 ), terra_flow_cosmos_osmosis_sol_stats3 as ( SELECT days, validator, cumulative_near_delegated, rank, sum(cumulative_near_delegated) over (partition by days order by rank asc) as total_staked --count(case when total_staked) --sum(1) over (partition by days order by stake_rank) as nakamoto_coeff from terra_flow_cosmos_osmosis_sol_stats2 order by rank asc), terra_final_nal as ( SELECT a.days, validator, count(case when total_staked <= threshold then 1 end) as nakamoto_coeff from terra_flow_cosmos_osmosis_sol_stats3 a join terra_flow_cosmos_osmosis_sol_stats b on a.days = b.days --where a.days >=CURRENT_DATE-INTERVAL '3 weekS' group by 1,2 order by 1 asc ), terra as ( SELECT days as weeks, sum(nakamoto_coeff) as naka_coeff from terra_final_nal group by 1 order by 1 asc ), cosmos_osmosis_stakes1 as ( SELECT x.block_timestamp, x.tx_id, y.attribute_value as validator, case when REGEXP_SUBSTR(x.attribute_value,'i.*') is not null then REGEXP_SUBSTR(x.attribute_value,'i.*') else REGEXP_SUBSTR(x.attribute_value,'u.*') end as attribute_name, REPLACE(x.attribute_value, attribute_name, '')::decimal/pow(10,6)*(-1) as staked FROM cosmos.core.fact_msg_attributes x join cosmos.core.fact_msg_attributes y on x.tx_id=y.tx_id where x.msg_type='unbond' and x.attribute_key='amount' and y.msg_type='unbond' and y.attribute_key='validator' having staked is not null and attribute_name is not null and staked>-1e9 ), cosmos_osmosis_stakes2 as ( SELECT x.block_timestamp, x.tx_id, y.attribute_value as validator, case when REGEXP_SUBSTR(x.attribute_value,'i.*') is not null then REGEXP_SUBSTR(x.attribute_value,'i.*') else REGEXP_SUBSTR(x.attribute_value,'u.*') end as attribute_name, REPLACE(x.attribute_value, attribute_name, '')::decimal/pow(10,6) as staked FROM cosmos.core.fact_msg_attributes x join cosmos.core.fact_msg_attributes y on x.tx_id=y.tx_id where x.msg_type='delegate' and x.attribute_key='amount' and y.msg_type='delegate' and y.attribute_key='validator' having staked is not null and attribute_name is not null and staked<1e9 ), cosmos_osmosis_stakes3 as ( SELECT x.block_timestamp, x.tx_id, y.attribute_value as validator, case when REGEXP_SUBSTR(x.attribute_value,'i.*') is not null then REGEXP_SUBSTR(x.attribute_value,'i.*') else REGEXP_SUBSTR(x.attribute_value,'u.*') end as attribute_name, REPLACE(x.attribute_value, attribute_name, '')::decimal/pow(10,6)*(-1) as staked FROM cosmos.core.fact_msg_attributes x join cosmos.core.fact_msg_attributes y on x.tx_id=y.tx_id where x.msg_type='redelegate' and x.attribute_key='amount' and y.msg_type='redelegate' and y.attribute_key='source_validator' having staked is not null and attribute_name is not null and staked>-1e9 ), cosmos_osmosis_stakes4 as ( SELECT x.block_timestamp, x.tx_id, y.attribute_value as validator, case when REGEXP_SUBSTR(x.attribute_value,'i.*') is not null then REGEXP_SUBSTR(x.attribute_value,'i.*') else REGEXP_SUBSTR(x.attribute_value,'u.*') end as attribute_name, REPLACE(x.attribute_value, attribute_name, '')::decimal/pow(10,6) as staked FROM cosmos.core.fact_msg_attributes x join cosmos.core.fact_msg_attributes y on x.tx_id=y.tx_id where x.msg_type='redelegate' and x.attribute_key='amount' and y.msg_type='redelegate' and y.attribute_key='destination_validator' having staked is not null and attribute_name is not null and staked<1e9 ), cosmos_osmosis_stakes5 as ( SELECT * FROM cosmos_osmosis_stakes1 UNION SELECT * FROM cosmos_osmosis_stakes2 UNION SELECT * FROM cosmos_osmosis_stakes3 UNION SELECT * FROM cosmos_osmosis_stakes4 ) , cosmos_osmosis_weekly as ( SELECT trunc(block_timestamp,'week') as days, validator, sum(staked) as osmo_staked FROM cosmos_osmosis_stakes5 WHERE staked is not null group by 1,2 ), cosmos_osmosis_ranking as ( SELECT days, validator, osmo_staked as total_near_delegated, sum(total_near_delegated) over (partition by validator order by days) as cumulative_near_delegated FROM cosmos_osmosis_weekly group by 1,2,3 ), cosmos_osmosis_totals as ( SELECT days, sum(total_near_delegated) as week_osmo_staked, sum(week_osmo_staked) over (order by days) as total_osmo_staked from cosmos_osmosis_ranking where cumulative_near_delegated >0 group by 1 order by 1 ), cosmos_osmosis_sol_stats as ( SELECT days, 33 as bizantine_fault_tolerance, total_osmo_staked, (total_osmo_staked*bizantine_fault_tolerance)/100 as threshold--, --sum(total_sol_delegated) over (partition by days order by validator_ranks asc) as total_sol_delegated_by_ranks, --count(distinct vote_accounts) as validators from cosmos_osmosis_totals ), cosmos_osmosis_sol_stats2 as ( select *, 1 as numbering, sum(numbering) over (partition by days order by cumulative_near_delegated desc) as rank from cosmos_osmosis_ranking where cumulative_near_delegated >0 ), cosmos_osmosis_sol_stats3 as ( SELECT days, validator, cumulative_near_delegated, rank, sum(cumulative_near_delegated) over (partition by days order by rank asc) as total_staked --count(case when total_staked) --sum(1) over (partition by days order by stake_rank) as nakamoto_coeff from cosmos_osmosis_sol_stats2 order by rank asc), cosmos_osmosis_final_nak as ( SELECT a.days, validator, sum(case when total_staked <= threshold then 1 end) as voting_power from cosmos_osmosis_sol_stats3 a join cosmos_osmosis_sol_stats b on a.days = b.days --where a.days >=CURRENT_DATE-INTERVAL '3 weekS' group by 1,2 order by 1 asc ), cosmos as ( SELECT days as weeks, sum(voting_power) as naka_coeff from cosmos_osmosis_final_nak group by 1 order by 1 asc ), osmosis_stakes1 as ( SELECT block_timestamp, tx_id, REDELEGATE_SOURCE_VALIDATOR_ADDRESS as validator, (amount / power(10,6))*(-1) as staked FROM osmosis.core.fact_staking where currency LIKE 'uosmo' AND REDELEGATE_SOURCE_VALIDATOR_ADDRESS is not null ), osmosis_stakes2 as ( SELECT block_timestamp, tx_id, validator_address as validator, CASE WHEN action = 'undelegate' then (amount / power(10,6))*(-1) else amount/power(10,6) end as staked FROM osmosis.core.fact_staking where currency LIKE 'uosmo' AND REDELEGATE_SOURCE_VALIDATOR_ADDRESS is NULL ), osmosis_stakes12 as ( SELECT block_timestamp, tx_id, validator_address as validator, (amount / power(10,6)) as staked FROM osmosis.core.fact_staking where currency LIKE 'uosmo' AND REDELEGATE_SOURCE_VALIDATOR_ADDRESS is not null ), osmosis_stakes3 as ( SELECT * FROM osmosis_stakes1 UNION SELECT * FROM osmosis_stakes2 UNION SELECT * FROM osmosis_stakes12 ), osmosis_weekly as ( SELECT trunc(block_timestamp,'week') as days, tx_id, validator, sum(staked) as osmo_staked FROM osmosis_stakes3 WHERE staked is not null group by 1,2,3 ), osmosis_totals as ( SELECT days, sum(osmo_staked) as week_osmo_staked, sum(week_osmo_staked) over (order by days)as total_osmo_staked from osmosis_weekly group by 1 order by 1 ), osmosis_ranking as ( SELECT days, validator, count(distinct tx_id) as txs, sum(osmo_staked) as total_near_delegated, sum(total_near_delegated) over (partition by validator order by days) as cumulative_near_delegated FROM osmosis_weekly group by 1,2 ), osmosis_sol_stats as ( SELECT days, 35 as bizantine_fault_tolerance, total_osmo_staked, (total_osmo_staked*bizantine_fault_tolerance)/100 as threshold--, --sum(total_sol_delegated) over (partition by days order by validator_ranks asc) as total_sol_delegated_by_ranks, --count(distinct vote_accounts) as validators from osmosis_totals ), osmosis_sol_stats2 as ( select *, 1 as numbering, sum(numbering) over (partition by days order by cumulative_near_delegated desc) as rank from osmosis_ranking ), osmosis_sol_stats3 as ( SELECT days, validator, cumulative_near_delegated, rank, sum(cumulative_near_delegated) over (partition by days order by rank asc) as total_staked --count(case when total_staked) --sum(1) over (partition by days order by stake_rank) as nakamoto_coeff from osmosis_sol_stats2 order by rank asc), osmosis_final_nak as ( SELECT a.days, validator, count(case when total_staked <= threshold then 1 end) as nakamoto_coeff from osmosis_sol_stats3 a join osmosis_sol_stats b on a.days = b.days --where a.days >=CURRENT_DATE-INTERVAL '3 weekS' group by 1,2 order by 1 asc ), proposals_info_1 as ( SELECT proposal_id, max(block_timestamp) as last_vote FROM osmosis.core.fact_governance_votes LEFT JOIN osmosis.core.dim_vote_options ON VOTE_ID = vote_option WHERE proposal_id in (114) GROUP by 1 ), proposals_info_2 as ( SELECT proposal_id, max(block_timestamp) as last_vote FROM osmosis.core.fact_governance_votes LEFT JOIN osmosis.core.dim_vote_options ON VOTE_ID = vote_option WHERE proposal_id in (196) GROUP by 1 ), proposals_info_3 as ( SELECT proposal_id, max(block_timestamp) as last_vote FROM osmosis.core.fact_governance_votes LEFT JOIN osmosis.core.dim_vote_options ON VOTE_ID = vote_option WHERE proposal_id in (337) GROUP by 1 ), osmo as ( SELECT days as weeks, sum(nakamoto_coeff) as naka_coeff from osmosis_final_nak,proposals_info_1 x,proposals_info_2 y,proposals_info_3 z group by 1 order by 1 asc ), sol_base as (select trunc(block_timestamp,'week') as weeks, stake_authority, vote_account, validator_rank, sum(post_tx_staked_balance/1e9 - pre_tx_staked_balance/1e9) as sol_delegated from solana.core.ez_staking_lp_actions where post_tx_staked_balance > pre_tx_staked_balance and event_type = 'delegate' and succeeded = 'TRUE' --and block_timestamp >= '2022-01-01' and node_pubkey is not null group by 1,2,3,4), sol_base2 as (select trunc(block_timestamp,'week') as weeks, withdraw_destination, vote_account, validator_rank, sum(withdraw_amount/1e9) as sol_withdrawn from solana.core.ez_staking_lp_actions where event_type = 'withdraw' --and block_timestamp >= '2022-01-01' and succeeded = 'TRUE' and node_pubkey is not null group by 1,2,3,4), sol_base3 as(select ifnull(a.weeks, b.weeks) as weeks, ifnull(a.stake_authority, b.withdraw_destination) as stake_authoritys, ifnull(a.vote_account, b.vote_account) as vote_accounts, address_name, initcap(split(address_name, ': ')[1]) as validator_names, ifnull(a.validator_rank, b.validator_rank) as validator_ranks, ifnull(sol_delegated, 0) as sol_delegatedz, ifnull(sol_withdrawn, 0) as sol_withdrawnz, ifnull(sol_delegatedz, 0) - ifnull(sol_withdrawnz,0) as net_sol_delegated from sol_base a full outer join sol_base2 b on a.weeks=b.weeks and a.vote_account = b.vote_account and a.validator_rank = b.validator_rank and stake_authority = withdraw_destination left join solana.core.dim_labels c on ifnull(a.vote_account, b.vote_account) = c.address --where net_sol_delegated > 0 group by 1,2,3,4,6,7,8 order by 1 desc ), sol_base4 as( select weeks, vote_accounts, validator_names, validator_ranks, avg(net_sol_delegated) as average_sol_delegated, median(net_sol_delegated) as median_sol_delegated, sum(net_sol_delegated) as total_sol_delegated, sum(total_sol_delegated) over (partition by vote_accounts order by weeks) as cumulative_sol_delegated from sol_base3 --where validator_ranks <= 50 group by 1,2,3,4 order by 1 asc, 5 asc ), sol_base5 as (select weeks, net_sol_delegated, sum(net_sol_delegated) over (order by weeks) as cumulative_sol_delegated, count(*) from sol_base3 group by 1,2 ), sol_base6 as (select weeks, avg(cumulative_sol_delegated) as weekly_cumulative_sol_delegated from sol_base5 group by 1 ), sol_stats as ( SELECT weeks, --validator_ranks, 33 as bizantine_fault_tolerance, sum(weekly_cumulative_sol_delegated) as total_sol_delegated_so_far, (total_sol_delegated_so_far*bizantine_fault_tolerance)/100 as threshold--, --sum(total_sol_delegated) over (partition by weeks order by validator_ranks asc) as total_sol_delegated_by_ranks, --count(distinct vote_accounts) as validators from sol_base6 group by 1,2 order by 1 asc ), sol_stats2 as (select weeks, vote_accounts, sum(total_sol_delegated) as total_sol_delegatedz, sum(total_sol_delegatedz) over (partition by vote_accounts order by weeks) as cumulative_sol_delegatedz from sol_base4 group by 1,2 ), sol_stats3 as (select *, 1 as numbering, sum(numbering) over (partition by weeks order by cumulative_sol_delegatedz desc) as rank from sol_stats2), sol_stats4 as ( SELECT weeks, vote_accounts, CUMULATIVE_SOL_DELEGATEDZ, rank, sum(CUMULATIVE_SOL_DELEGATEDZ) over (partition by weeks order by rank asc) as total_staked --count(case when total_staked) --sum(1) over (partition by weeks order by stake_rank) as nakamoto_coeff from sol_stats3 order by rank asc), sol_final as ( SELECT a.weeks, vote_accounts, 'nakamoto', 'coefficient', count(case when total_staked <= threshold then 1 end) as nakamoto_coeff from sol_stats4 a join sol_stats b on a.weeks = b.weeks group by 1,2 order by 1 asc ), sol as ( select weeks as weeks, sum(nakamoto_coeff) as naka_coeff from sol_final group by 1 order by 1 asc ) select 'Near' as chain,* from near where weeks>='2023-01-01' and weeks <=current_date-1 union select 'Osmosis' as chain,* from osmo where weeks>='2023-01-01' and weeks <=current_date-1 union select 'Solana' as chain,* from sol where weeks>='2023-01-01' and weeks <=current_date-1

Comparative Analysis of Governance Voting and Staking Metrics on the NEAR Blockchain