[volby] senatni souboje - ukazka tesnosti vysledku

[kokes]

with kandidati as (
	SELECT
		datum, obvod, jmeno, prijmeni, proc_k1, proc_k2,
		row_number() over(w) poradi_k1,
		coalesce(proc_k1 - lead(proc_k1) over(w), proc_k1 - lag(proc_k1) over(w)) naskok_k1
	FROM
		volby.senat_kandidati
		where proc_k2 > 0
		window w as (partition by datum, obvod order by proc_k1 desc)
), zaokrouhlene as (
	select
		*, (floor(naskok_k1/5)*5, floor(naskok_k1/5)*5 + 5) rozsah_naskoku
	from kandidati
)
-- select * from zaokrouhlene order by datum, obvod, poradi_k1; -- raw data

select
	rozsah_naskoku, sum(case when proc_k2 > 50 then 1 else 0 end) as vyher, count(*) as souboju
from zaokrouhlene
group by 1 order by 1;