SQL cvičení: senátní statistiky

[kokes]

Bohužel za senát nemáme data pro celou historii, takže musíme používat scrapovaný data. Ale i tak...

Jak často se vyhrává z prvního místa

Vyhrálo zatim jen 16 lidí z celkovýho počtu asi 447 soubojů.

select count(distinct (rok, obvod, datum)) from volby.senat_kandidati_vse;

select rok, obvod, jmeno, navrhujici_strana, hlasy_k1, procenta_k1 from volby.senat_kandidati_vse
where procenta_k1 > 50
order by procenta_k1 asc;

S jakým náskokem jdou ještě prohrát volby v druhém kole?

Tohle neni náskok ale lidi s nejmenším ziskem, který nakonec vyhráli:

select rok, obvod, jmeno, navrhujici_strana, procenta_k1, procenta_k2 from volby.senat_kandidati_vse
where procenta_k2 >= 50
order by procenta_k1 asc
limit 10;

A tady je ten náskok teda (asi tam jsou samy preskoceni z prvniho roku)

with poradi as (
	select
	*, rank() over(partition by rok, obvod, datum order by hlasy_k1 desc) poradi_k1,
	procenta_k1 - lead(procenta_k1) over(partition by rok, obvod, datum order by procenta_k1 desc) naskok
	from volby.senat_kandidati_vse
	where procenta_k1 < 50
)

select rok, obvod, jmeno, navrhujici_strana, procenta_k1, procenta_k2, naskok from poradi
where poradi_k1 = 1 and procenta_k2 < 50 and hlasy_k2 > 0
order by naskok desc
limit 10

Nejtěsnější první kolo mezi druhým a třetím místem

with poradi as (
	select
	*, rank() over(partition by rok, obvod, datum order by hlasy_k1 desc) poradi_k1,
	procenta_k1 - lead(procenta_k1) over(partition by rok, obvod, datum order by procenta_k1 desc) naskok_procent,
	hlasy_k1 - lead(hlasy_k1) over(partition by rok, obvod, datum order by hlasy_k1 desc) naskok_hlasu
	from volby.senat_kandidati_vse
)

select rok, obvod, jmeno, navrhujici_strana, procenta_k1, procenta_k2, naskok_procent, naskok_hlasu from poradi
where poradi_k1 = 2
order by naskok_procent asc
limit 10

Nejmenší rozdíl v druhym kole

with poradi as (
	select
	*, rank() over(partition by rok, obvod, datum order by hlasy_k2 desc) poradi_k2,
	procenta_k2 - lead(procenta_k2) over(partition by rok, obvod, datum order by procenta_k2 desc) naskok_procent,
	hlasy_k2 - lead(hlasy_k2) over(partition by rok, obvod, datum order by hlasy_k2 desc) naskok_hlasu
	from volby.senat_kandidati_vse
)

select rok, obvod, jmeno, navrhujici_strana, procenta_k2, naskok_procent, naskok_hlasu from poradi
where procenta_k2 >= 50
order by naskok_procent asc
limit 10