with leader_info as( select block_id, date_trunc('hour', block_timestamp) as dt, case when (/* pubkey ... */ pubkey = 'EvnRmnMrd69kFdbLMxWkTn1icZ7DCceRhvmb2SJXqDo4' or pubkey = 'G1bLKfyNm7zsmmYEL9dyxBvMtxpFcwy2s84bHDj2ZFUY' or pubkey = '43Am3PKFeo9cACpqYL5Sk95rpVdxLw3Mc22PqRqZXEW2') then 'Palidators' when (pubkey = 'DWvDTSh3qfn88UoQTEKRV2JnLt5jtJAVoiCo3ivtMwXP' or pubkey = 'q9XWcZ7T1wP4bW9SB4XgNNwjnFEJ982nE8aVbbNuwot' or pubkey = 'HEL1USMZKAL2odpNBj2oCjffnFGaYwmbGmyewGv1e2TU' or pubkey = 'Fd7btgySsrjuo25CJCj7oE7VPMyezDhnx7pZkj2v69Nk' or pubkey = '5pPRHniefFjkiaArbGX3Y8NUysJmQ9tMZg3FrFGwHzSm' or pubkey = 'A4hyMd3FyvUJSRafDUSwtLLaQcxRP4r1BRC9w2AJ1to2' or pubkey = 'DtdSSG8ZJRZVv5Jx7K1MeWp7Zxcu19GD5wQRGRpQ9uMF' or pubkey = 'DRpbCBMxVnDK7maPM5tGv6MvB3v1sRMC86PZ8okm21hy' or pubkey = 'CW9C7HBwAMgqNdXkNgFg9Ujr3edR2Ab9ymEuQnVacd1A' or pubkey = 'EgxVyTgh2Msg781wt9EsqYx4fW8wSvfFAHGLaJQjghiL' or pubkey = 'FBKFWadXZJahGtFitAsBvbqh5968gLY7dMBBJUoUjeNi' or pubkey = 'EkvdKhULbMFqjKBKotAzGi3kwMvMpYNDKJXXQQmi6C1f' or pubkey = 'BtsmiEEvnSuUnKxqXj2PZRYpPJAc7C34mGz8gtJ1DAaH' or pubkey = 'Hz5aLvpKScNWoe9YZWxBLrQA3qzHJivBGtfciMekk8m5' or pubkey = '7Zm1pE4FubFYZDyAQ5Labh3A4cxDcvve1s3WCRgEAZ84' or pubkey = '9jxgosAfHgHzwnxsHw4RAZYaLVokMbnYtmiZBreynGFP' or pubkey = 'GoeW4aFK4dGoekJySgUynWDxBZiQJqm8GDAF4H53tDK9') then 'Superminority' when pubkey = 'ChorusmmK7i1AxXeiTtQgQZhQNiXYU84ULeaYF1EH15n' then 'Chorus One' end as validator, reward_amount_sol as fee_rewards from solana.gov.fact_rewards_fee where ( '{{n_days}}' = 0 and block_timestamp >= cast('{{start_date}}' as timestamp) and block_timestamp < cast('{{end_date}}' as timestamp) ) or ( '{{n_days}}' != 0 and block_timestamp > current_date() - interval '{{n_days}} days' and block_timestamp < current_date() ) ), jito_tip_dist_program as( select distinct ep.epoch, case when (signers[0] = 'G1bLKfyNm7zsmmYEL9dyxBvMtxpFcwy2s84bHDj2ZFUY' or signers[0] = 'qZMH9GWnnBkx7aM1h98iKSv2Lz5N78nwNSocAxDQrbP' or signers[0] = '9FXD1NXrK6xFU8i4gLAgjj2iMEWTqJhSuQN8tQuDfm2e' or signers[0] = 'B8m79Xf3kp19suGMJkfXZDDHCmMP5vWHuYAdirtswEzD' or signers[0] = '2ZS2DGFRDD7WWSzqkaPoqURVknoMdFVFWDdBRKNZuqct' or signers[0] = 'Awes4Tr6TX8JDzEhCZY2QVNimT6iD1zWHzf1vNyGvpLM' or signers[0] = '43Am3PKFeo9cACpqYL5Sk95rpVdxLw3Mc22PqRqZXEW2') then 'Palidators' when (signers[0] = 'DWvDTSh3qfn88UoQTEKRV2JnLt5jtJAVoiCo3ivtMwXP' or signers[0] = 'q9XWcZ7T1wP4bW9SB4XgNNwjnFEJ982nE8aVbbNuwot' or signers[0] = 'HEL1USMZKAL2odpNBj2oCjffnFGaYwmbGmyewGv1e2TU' or signers[0] = 'Fd7btgySsrjuo25CJCj7oE7VPMyezDhnx7pZkj2v69Nk' or signers[0] = 'EvnRmnMrd69kFdbLMxWkTn1icZ7DCceRhvmb2SJXqDo4' or signers[0] = '5pPRHniefFjkiaArbGX3Y8NUysJmQ9tMZg3FrFGwHzSm' or signers[0] = 'A4hyMd3FyvUJSRafDUSwtLLaQcxRP4r1BRC9w2AJ1to2' or signers[0] = 'DtdSSG8ZJRZVv5Jx7K1MeWp7Zxcu19GD5wQRGRpQ9uMF' or signers[0] = 'DRpbCBMxVnDK7maPM5tGv6MvB3v1sRMC86PZ8okm21hy' or signers[0] = 'CW9C7HBwAMgqNdXkNgFg9Ujr3edR2Ab9ymEuQnVacd1A' or signers[0] = 'EgxVyTgh2Msg781wt9EsqYx4fW8wSvfFAHGLaJQjghiL' or signers[0] = 'FBKFWadXZJahGtFitAsBvbqh5968gLY7dMBBJUoUjeNi' or signers[0] = 'EkvdKhULbMFqjKBKotAzGi3kwMvMpYNDKJXXQQmi6C1f' or signers[0] = 'BtsmiEEvnSuUnKxqXj2PZRYpPJAc7C34mGz8gtJ1DAaH' or signers[0] = 'Hz5aLvpKScNWoe9YZWxBLrQA3qzHJivBGtfciMekk8m5' or signers[0] = '7Zm1pE4FubFYZDyAQ5Labh3A4cxDcvve1s3WCRgEAZ84' or signers[0] = '9jxgosAfHgHzwnxsHw4RAZYaLVokMbnYtmiZBreynGFP' or signers[0] = 'GoeW4aFK4dGoekJySgUynWDxBZiQJqm8GDAF4H53tDK9') then 'Superminority' when signers[0] = 'ChorusmmK7i1AxXeiTtQgQZhQNiXYU84ULeaYF1EH15n' then 'Chorus One' end as validator, instruction['parsed']['info']['newAccount'] as TipDistributionAccount from solana.core.fact_events_inner ei inner join solana.gov.dim_epoch ep on (ep.start_block <= ei.block_id and ei.block_id <= ep.end_block) where signers[0] in ( 'G1bLKfyNm7zsmmYEL9dyxBvMtxpFcwy2s84bHDj2ZFUY', 'qZMH9GWnnBkx7aM1h98iKSv2Lz5N78nwNSocAxDQrbP', '9FXD1NXrK6xFU8i4gLAgjj2iMEWTqJhSuQN8tQuDfm2e', 'B8m79Xf3kp19suGMJkfXZDDHCmMP5vWHuYAdirtswEzD', '2ZS2DGFRDD7WWSzqkaPoqURVknoMdFVFWDdBRKNZuqct', 'Awes4Tr6TX8JDzEhCZY2QVNimT6iD1zWHzf1vNyGvpLM', '43Am3PKFeo9cACpqYL5Sk95rpVdxLw3Mc22PqRqZXEW2', -- Palidators 'DWvDTSh3qfn88UoQTEKRV2JnLt5jtJAVoiCo3ivtMwXP', 'q9XWcZ7T1wP4bW9SB4XgNNwjnFEJ982nE8aVbbNuwot', 'HEL1USMZKAL2odpNBj2oCjffnFGaYwmbGmyewGv1e2TU', 'Fd7btgySsrjuo25CJCj7oE7VPMyezDhnx7pZkj2v69Nk', 'EvnRmnMrd69kFdbLMxWkTn1icZ7DCceRhvmb2SJXqDo4', '5pPRHniefFjkiaArbGX3Y8NUysJmQ9tMZg3FrFGwHzSm', 'A4hyMd3FyvUJSRafDUSwtLLaQcxRP4r1BRC9w2AJ1to2', 'DtdSSG8ZJRZVv5Jx7K1MeWp7Zxcu19GD5wQRGRpQ9uMF', 'DRpbCBMxVnDK7maPM5tGv6MvB3v1sRMC86PZ8okm21hy', 'CW9C7HBwAMgqNdXkNgFg9Ujr3edR2Ab9ymEuQnVacd1A', 'EgxVyTgh2Msg781wt9EsqYx4fW8wSvfFAHGLaJQjghiL', 'FBKFWadXZJahGtFitAsBvbqh5968gLY7dMBBJUoUjeNi', 'EkvdKhULbMFqjKBKotAzGi3kwMvMpYNDKJXXQQmi6C1f', 'BtsmiEEvnSuUnKxqXj2PZRYpPJAc7C34mGz8gtJ1DAaH', 'Hz5aLvpKScNWoe9YZWxBLrQA3qzHJivBGtfciMekk8m5', '7Zm1pE4FubFYZDyAQ5Labh3A4cxDcvve1s3WCRgEAZ84', '9jxgosAfHgHzwnxsHw4RAZYaLVokMbnYtmiZBreynGFP', 'GoeW4aFK4dGoekJySgUynWDxBZiQJqm8GDAF4H53tDK9', -- Superminority 'ChorusmmK7i1AxXeiTtQgQZhQNiXYU84ULeaYF1EH15n' ) and instruction_program_id = '4R3gSG8BpU4t19KYj8CfnbtRpnT8gtk4dvTHxVRwc2r7' and event_type = 'createAccount' and ( ( '{{n_days}}' = 0 and block_timestamp >= cast('{{start_date}}' as timestamp) and block_timestamp <= cast('{{end_date}}' as timestamp) ) or ( '{{n_days}}' != 0 and block_timestamp >= current_date() - interval '{{n_days}} days' and block_timestamp < current_date() ) ) ), balance_change_info as ( select date_trunc('hour', sb.block_timestamp) as dt, sb.block_id, sb.tx_id, sb.account_address, sb.balance, sb.balance - sb.pre_balance as collectedTips from solana.core.fact_sol_balances sb where sb.account_address in ( select distinct tdp.TipDistributionAccount from jito_tip_dist_program tdp ) and sb.balance > sb.pre_balance and ( ( '{{n_days}}' = 0 and sb.block_timestamp >= cast('{{start_date}}' as timestamp) and sb.block_timestamp <= cast('{{end_date}}' as timestamp) ) or ( '{{n_days}}' != 0 and sb.block_timestamp >= current_date() - interval '{{n_days}} days' and sb.block_timestamp < current_date() ) ) ), collected_mev_jito as( select bci.dt, bci.block_id, jdp.validator, bci.collectedTips as collectedJitoTips from balance_change_info bci inner join jito_tip_dist_program jdp on jdp.TipDistributionAccount = bci.account_address ), total_rewards as( select li.dt, li.validator, li.block_id, coalesce(li.fee_rewards + cmj.collectedJitoTips, li.fee_rewards) as total_rewards from leader_info li left join collected_mev_jito cmj on cmj.block_id = li.block_id ), trader_API_tips_data AS ( select date_trunc('hour', ft.block_timestamp) as dt, ft.block_id, li.validator, sum(amount) as traderAPItips from solana.core.fact_transfers ft inner join leader_info li on li.block_id = ft.block_id where tx_to = 'HWEoBxYs7ssKuudEjzjmpfJVX7Dvi7wescFsVx2L5yoY' and mint = 'So11111111111111111111111111111111111111111' and ( ( '{{n_days}}' = 0 and ft.block_timestamp >= cast('{{start_date}}' as timestamp) and ft.block_timestamp < cast('{{end_date}}' as timestamp) ) or ( '{{n_days}}' != 0 and ft.block_timestamp > current_date() - interval '{{n_days}} days' )) group by 1,2,3 ), -- ================================================ -- KS test for: traderAPItips, collectedJitoTips e total_rewards -- ================================================ -- 1) KS - traderAPItips ecdf_traderAPI as ( select dt, validator, traderAPItips, cume_dist() over (partition by dt, validator order by traderAPItips) as ecdf_value from trader_API_tips_data ), slots_traderAPI as ( select distinct dt, traderAPItips from trader_API_tips_data ), cdf_full_traderAPI as ( select st.dt, st.traderAPItips, v.validator, et.ecdf_value from slots_traderAPI st cross join (select distinct validator from trader_API_tips_data) v left join ecdf_traderAPI et on et.dt = st.dt and et.validator = v.validator and et.traderAPItips = st.traderAPItips ), cdf_imputed_traderAPI as ( select dt, traderAPItips, validator, coalesce( ecdf_value, max(ecdf_value) over (partition by dt, validator order by traderAPItips rows between unbounded preceding and 1 preceding), 0 ) as ecdf_value_filled from cdf_full_traderAPI ), cdf_comparison_traderAPI as ( select c1.dt, c1.traderAPItips, c1.validator as validator_1, c2.validator as validator_2, c1.ecdf_value_filled as ecdf_value_1, c2.ecdf_value_filled as ecdf_value_2, c1.ecdf_value_filled - c2.ecdf_value_filled as cdf_diff, abs(c1.ecdf_value_filled - c2.ecdf_value_filled) as abs_ecdf_diff, c1.validator || ' vs ' || c2.validator as comparison_label from cdf_imputed_traderAPI c1 join cdf_imputed_traderAPI c2 on c1.dt = c2.dt and c1.traderAPItips = c2.traderAPItips where c1.validator = 'Palidators' and c2.validator != c1.validator ), ks_statistics_traderAPI as ( select dt, comparison_label, validator_1, validator_2, max(cdf_diff) as max_cdf_diff, min(cdf_diff) as min_cdf_diff, max(abs_ecdf_diff) as ks_statistic, exp(-2 * power(max(abs_ecdf_diff), 2) * (select count(*) from trader_API_tips_data t where t.dt = c.dt and t.validator = 'Palidators') ) as p_value from cdf_comparison_traderAPI c group by dt, comparison_label, validator_1, validator_2 ), ks_statistics_summary_traderAPI as ( select dt, comparison_label, ks_statistic, p_value, case when max_cdf_diff = ks_statistic and p_value < 0.05 then 'Palidators CDF > ' || validator_2 || ' CDF' when min_cdf_diff = -ks_statistic and p_value < 0.05 then 'Palidators CDF < ' || validator_2 || ' CDF' else 'Palidators CDF ~ ' || validator_2 || ' CDF' end as cdf_location, case when max_cdf_diff = ks_statistic and p_value < 0.05 then 1 when min_cdf_diff = -ks_statistic and p_value < 0.05 then -1 else 0 end as cdf_sign from ks_statistics_traderAPI ), -- 2) KS - collectedJitoTips ecdf_collectedJito as ( select dt, validator, collectedJitoTips, cume_dist() over (partition by dt, validator order by collectedJitoTips) as ecdf_value from collected_mev_jito ), slots_collectedJito as ( select distinct dt, collectedJitoTips from collected_mev_jito ), cdf_full_collectedJito as ( select st.dt, st.collectedJitoTips, v.validator, et.ecdf_value from slots_collectedJito st cross join (select distinct validator from collected_mev_jito) v left join ecdf_collectedJito et on et.dt = st.dt and et.validator = v.validator and et.collectedJitoTips = st.collectedJitoTips ), cdf_imputed_collectedJito as ( select dt, collectedJitoTips, validator, coalesce( ecdf_value, max(ecdf_value) over (partition by dt, validator order by collectedJitoTips rows between unbounded preceding and 1 preceding), 0 ) as ecdf_value_filled from cdf_full_collectedJito ), cdf_comparison_collectedJito as ( select c1.dt, c1.collectedJitoTips, c1.validator as validator_1, c2.validator as validator_2, c1.ecdf_value_filled as ecdf_value_1, c2.ecdf_value_filled as ecdf_value_2, c1.ecdf_value_filled - c2.ecdf_value_filled as cdf_diff, abs(c1.ecdf_value_filled - c2.ecdf_value_filled) as abs_ecdf_diff, c1.validator || ' vs ' || c2.validator as comparison_label from cdf_imputed_collectedJito c1 join cdf_imputed_collectedJito c2 on c1.dt = c2.dt and c1.collectedJitoTips = c2.collectedJitoTips where c1.validator = 'Palidators' and c2.validator != c1.validator ), ks_statistics_collectedJito as ( select dt, comparison_label, validator_1, validator_2, max(cdf_diff) as max_cdf_diff, min(cdf_diff) as min_cdf_diff, max(abs_ecdf_diff) as ks_statistic, exp(-2 * power(max(abs_ecdf_diff), 2) * (select count(*) from collected_mev_jito t where t.dt = c.dt and t.validator = 'Palidators') ) as p_value from cdf_comparison_collectedJito c group by dt, comparison_label, validator_1, validator_2 ), ks_statistics_summary_collectedJito as ( select dt, comparison_label, ks_statistic, p_value, case when max_cdf_diff = ks_statistic and p_value < 0.05 then 'Palidators CDF > ' || validator_2 || ' CDF' when min_cdf_diff = -ks_statistic and p_value < 0.05 then 'Palidators CDF < ' || validator_2 || ' CDF' else 'Palidators CDF ~ ' || validator_2 || ' CDF' end as cdf_location, case when max_cdf_diff = ks_statistic and p_value < 0.05 then 1 when min_cdf_diff = -ks_statistic and p_value < 0.05 then -1 else 0 end as cdf_sign from ks_statistics_collectedJito ), -- 3) KS - total_rewards ecdf_total_rewards as ( select dt, validator, total_rewards, cume_dist() over (partition by dt, validator order by total_rewards) as ecdf_value from total_rewards ), slots_total_rewards as ( select distinct dt, total_rewards from total_rewards ), cdf_full_total_rewards as ( select st.dt, st.total_rewards, v.validator, et.ecdf_value from slots_total_rewards st cross join (select distinct validator from total_rewards) v left join ecdf_total_rewards et on et.dt = st.dt and et.validator = v.validator and et.total_rewards = st.total_rewards ), cdf_imputed_total_rewards as ( select dt, total_rewards, validator, coalesce( ecdf_value, max(ecdf_value) over (partition by dt, validator order by total_rewards rows between unbounded preceding and 1 preceding), 0 ) as ecdf_value_filled from cdf_full_total_rewards ), cdf_comparison_total_rewards as ( select c1.dt, c1.total_rewards, c1.validator as validator_1, c2.validator as validator_2, c1.ecdf_value_filled as ecdf_value_1, c2.ecdf_value_filled as ecdf_value_2, c1.ecdf_value_filled - c2.ecdf_value_filled as cdf_diff, abs(c1.ecdf_value_filled - c2.ecdf_value_filled) as abs_ecdf_diff, c1.validator || ' vs ' || c2.validator as comparison_label from cdf_imputed_total_rewards c1 join cdf_imputed_total_rewards c2 on c1.dt = c2.dt and c1.total_rewards = c2.total_rewards where c1.validator = 'Palidators' and c2.validator != c1.validator ), ks_statistics_total_rewards as ( select dt, comparison_label, validator_1, validator_2, max(cdf_diff) as max_cdf_diff, min(cdf_diff) as min_cdf_diff, max(abs_ecdf_diff) as ks_statistic, exp(-2 * power(max(abs_ecdf_diff), 2) * (select count(*) from total_rewards t where t.dt = c.dt and t.validator = 'Palidators') ) as p_value from cdf_comparison_total_rewards c group by dt, comparison_label, validator_1, validator_2 ), ks_statistics_summary_total_rewards as ( select dt, comparison_label, ks_statistic, p_value, case when max_cdf_diff = ks_statistic and p_value < 0.05 then 'Palidators CDF > ' || validator_2 || ' CDF' when min_cdf_diff = -ks_statistic and p_value < 0.05 then 'Palidators CDF < ' || validator_2 || ' CDF' else 'Palidators CDF ~ ' || validator_2 || ' CDF' end as cdf_location, case when max_cdf_diff = ks_statistic and p_value < 0.05 then 1 when min_cdf_diff = -ks_statistic and p_value < 0.05 then -1 else 0 end as cdf_sign from ks_statistics_total_rewards ), -- Union of tests ks_union as ( select dt, metric, comparison_label, ks_statistic, p_value, cdf_location, cdf_sign from ( select dt, 'traderAPItips' as metric, comparison_label, ks_statistic, p_value, cdf_location, cdf_sign from ks_statistics_summary_traderAPI union all select dt, 'collectedJitoTips' as metric, comparison_label, ks_statistic, p_value, cdf_location, cdf_sign from ks_statistics_summary_collectedJito union all select dt, 'total_rewards' as metric, comparison_label, ks_statistic, p_value, cdf_location, cdf_sign from ks_statistics_summary_total_rewards ) t ) select dt, comparison_label, max(case when metric = 'traderAPItips' then ks_statistic end) as ks_statistic_traderAPItips, max(case when metric = 'traderAPItips' then p_value end) as p_value_traderAPItips, max(case when metric = 'traderAPItips' then cdf_location end) as cdf_location_traderAPItips, max(case when metric = 'traderAPItips' then cdf_sign end) as cdf_sign_traderAPItips, max(case when metric = 'collectedJitoTips' then ks_statistic end) as ks_statistic_collectedJitoTips, max(case when metric = 'collectedJitoTips' then p_value end) as p_value_collectedJitoTips, max(case when metric = 'collectedJitoTips' then cdf_location end) as cdf_location_collectedJitoTips, max(case when metric = 'collectedJitoTips' then cdf_sign end) as cdf_sign_collectedJitoTips, max(case when metric = 'total_rewards' then ks_statistic end) as ks_statistic_total_rewards, max(case when metric = 'total_rewards' then p_value end) as p_value_total_rewards, max(case when metric = 'total_rewards' then cdf_location end) as cdf_location_total_rewards, max(case when metric = 'total_rewards' then cdf_sign end) as cdf_sign_total_rewards from ks_union group by dt, comparison_label having max(case when metric = 'collectedJitoTips' then cdf_sign end) is not null order by dt, comparison_label

Solana vs Paladin