WITH flow_sol_base as ( SELECT trunc(block_timestamp,'day') as day, --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,'day') as day, --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.day, b.day) as days, 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 days order by net_flow_delegated desc) as validator_ranks from flow_sol_base a full outer join flow_sol_base2 b on a.day=b.day 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 days, 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 days) 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 days, net_flow_delegated, sum(net_flow_delegated) over (order by days) as cumulative_flow_delegated, count(*) from flow_sol_base3 group by 1,2 ), flow_sol_base6 as (select days, avg(cumulative_flow_delegated) as cosmos_osmosis_dayly_cumulative_flow_delegated from flow_sol_base5 group by 1 ), flow_cosmos_osmosis_sol_stats as ( SELECT days, --validator_ranks, 33 as bizantine_fault_tolerance, sum(cosmos_osmosis_dayly_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 days 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 days, vote_accounts, sum(total_flow_delegated) as total_flow_delegatedz, sum(total_flow_delegatedz) over (partition by vote_accounts order by days) 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 days order by cumulative_flow_delegatedz desc) as rank from flow_cosmos_osmosis_sol_stats2), flow_cosmos_osmosis_sol_stats4 as ( SELECT days, vote_accounts, CUMULATIVE_FLOW_DELEGATEDZ, rank, sum(CUMULATIVE_FLOW_DELEGATEDZ) 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 flow_cosmos_osmosis_sol_stats3 order by rank asc), flow_final_nal as ( SELECT a.days, 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.days = b.days --where a.days >=CURRENT_DATE-INTERVAL '6 dayS' group by 1,2 order by 1 asc ), flow as ( SELECT days, 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_dayly as ( SELECT trunc(block_timestamp,'day') as days, 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 days, sum(near_staked) as day_near_staked, sum(day_near_staked) over (order by days)as total_near_staked from near_cosmos_osmosis_dayly group by 1 order by 1 ), near_terra_cosmos_osmosis_ranking as ( SELECT days, validator, count(distinct tx) as txs, sum(near_staked) as total_near_delegated, sum(total_near_delegated) over (partition by validator order by days) as cumulative_near_delegated FROM near_cosmos_osmosis_dayly group by 1,2 ), near_terra_flow_cosmos_osmosis_sol_stats as ( SELECT days, 50 as bizantine_fault_tolerance, total_near_staked, (total_near_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 near_cosmos_osmosis_totals ), near_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 near_terra_cosmos_osmosis_ranking ), near_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 near_terra_flow_cosmos_osmosis_sol_stats2 order by rank asc ), near_final as ( SELECT a.days, 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.days = b.days group by 1,2 order by 1 asc ), near as ( select days, 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_dayly as ( SELECT trunc(block_timestamp,'day') 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_dayly group by 1,2,3 ), terra_cosmos_osmosis_totals as ( SELECT days, sum(total_near_delegated) as day_osmo_staked, sum(day_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 dayS' group by 1,2 order by 1 asc ), terra as ( SELECT days as days, 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_dayly as ( SELECT trunc(block_timestamp,'day') 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_dayly group by 1,2,3 ), cosmos_osmosis_totals as ( SELECT days, sum(total_near_delegated) as day_osmo_staked, sum(day_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 dayS' group by 1,2 order by 1 asc ), cosmos as ( SELECT days as days, 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_dayly as ( SELECT trunc(block_timestamp,'day') 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 day_osmo_staked, sum(day_osmo_staked) over (order by days)as total_osmo_staked from osmosis_dayly 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_dayly 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 dayS' 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 days, 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,'day') as days, 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,'day') as days, 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.days, b.days) as days, 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.days=b.days 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 days, 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 days) 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 days, net_sol_delegated, sum(net_sol_delegated) over (order by days) as cumulative_sol_delegated, count(*) from sol_base3 group by 1,2 ), sol_base6 as (select days, avg(cumulative_sol_delegated) as dayly_cumulative_sol_delegated from sol_base5 group by 1 ), sol_stats as ( SELECT days, --validator_ranks, 33 as bizantine_fault_tolerance, sum(dayly_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 days 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 days, vote_accounts, sum(total_sol_delegated) as total_sol_delegatedz, sum(total_sol_delegatedz) over (partition by vote_accounts order by days) as cumulative_sol_delegatedz from sol_base4 group by 1,2 ), sol_stats3 as (select *, 1 as numbering, sum(numbering) over (partition by days order by cumulative_sol_delegatedz desc) as rank from sol_stats2), sol_stats4 as ( SELECT days, vote_accounts, CUMULATIVE_SOL_DELEGATEDZ, rank, sum(CUMULATIVE_SOL_DELEGATEDZ) 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 sol_stats3 order by rank asc), sol_final as ( SELECT a.days, 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.days = b.days group by 1,2 order by 1 asc ), sol as ( select days as day, sum(nakamoto_coeff) as naka_coeff from sol_final group by 1 order by 1 asc ) select case when day <'2023-06-05' then 'Before SEC' when day between '2023-06-05' and '2023-06-08' then 'During SEC Rumours' else 'After SEC' end as period,* from sol where day>=current_date-INTERVAL '1 MONTH' and day <=current_date-1

hyoeiseman