-- *UZPD - Zpracování výsledků voleb (kombinace RUIAN a CSU)

-- *skupina B -Maybrodskyy--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- CESTA

SET search_path TO c16, ruian, public;
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

-- TVORBA VRSTEV
----

--tabulka okresy--
--create table okresy_map as 
--select kod, nazev, definicnibod, originalnihranice, generalizovanehranice
--from ruian.okresy;

--tabulka statistika--
--create table sldb_b as 
--select *
--from csu_sldb.sldb;


--tabulka vysvetlivky pro statistiku--
--create table sldb_polozky as 
--select *
--from csu_sldb.sldb


--Uveďte počet volebních okrsků, kde počet neplatných hlasu v daném okrsků převyšuje statní průměr
--select count(okrsek)
--from plat_hlas
--where odevz_obal-pl_hl_celk>(select avg(
--odevz_obal-pl_hl_celk)
--from plat_hlas
--)



--Uvedte strany, ktere ziskaly mene nez 500 hlasu v parlamentnich volbach 2017 na celem uzemi CR.
--select  distinct nazevcelk
--from pol_strany ps
--join poc_hlas p
--on ps.kstrana=p.kstrana 
--where pocmandcr=0
--group by nazevcelk
--having sum(poc_hlasu)<500



--kolik procent volicu volilo ODS v parlamentnich volbach 2017?
--select sum(poc_hlasu)*10000/(
--select sum(poc_hlasu)
--from poc_hlas)
--from poc_hlas p
--join pol_strany ps 
--on p.kstrana=ps.kstrana
--where zkratkak8='ODS'




--ve kterych obcich, v okoli Prahy, byla zaznamenana volebni ucast, ktera prevysuje 80 procent?
--select nazev,(pl_hl_celk*100)/vol_seznam proc_ucast, generalizovanehranice
--from obce_map o
--join plat_hlas p
--on o.kod=p.obec
--where obec in 
-- (select ob.kod 
--from obce_map ob
--join okresy_map ok
--on ob.okreskod=ok.kod
--where ok.nazev like '%Praha%')
--and (pl_hl_celk*100)/vol_seznam>80


--TOP 3 strany, ktere byly zvoleny lidmi starsi 80 let, v obci, kde jejich pocet je nejvyssi v CR.
--select zkratkak8,poc_hlasu 
--from pol_strany ps
--join poc_hlas ph
--on ps.kstrana=ph.kstrana
--join sldb_b s
--on ph.okres=s.uzkod
--where ph.obec in
--(
--select uzkod
--from sldb_b
--order by vse31111/vse4111 desc limit 3)
--order by poc_hlasu 




--V jakých obcích v celé ČR  nezískaly ani jeden hlas strany, které neprošly do sněmovny.

--select distinct nazev,generalizovanehranice
--from obce_map o
--join poc_hlas p
--on o.kod=p.obec
--where kod not in (
--select obec
--from poc_hlas ph
--join pol_strany p
--on ph.kstrana=p.kstrana
--where pocmandcr=0
--)
  


--volebni ucast v okresech s nejvetsim procentem obyvatel s vysokoskolskym vzdelanim bez zapocitani hlavniho mesta Prahy.
--select ok.nazev,sum(pl_hl_celk)*10000/sum(vol_seznam)  as ucast_v_set_procent
--from plat_hlas p
--join obce_map ob
--on p.obec=ob.kod
--join okresy_map ok
--on ob.okreskod=ok.kod
--join sldb_b s
--on ok.nazev=s.nazev
--where s.nazev in
--(
--select s.nazev
--from sldb_b s
--where uzkod between 40000 and 49999 
--and s.nazev not like '%Praha'
--order by vse2181/vse4111 desc limit 6 )
--group by ok.nazev




--jaky je rozdil mezi procenty volici, kteri volili stranu SPD ve Melniku a celostatni vysledek?
--select sum(poc_hlasu)*10000/(
--select sum(poc_hlasu)
--from poc_hlas p
--join obce_map o
--on o.kod=p.obec
--where nazev ='Mělník'
--)  -(select sum(poc_hlasu)*10000/(
--select sum(poc_hlasu)
--from poc_hlas p
--join obce_map o
--on o.kod=p.obec
--)                             
--from poc_hlas p
--join obce_map o
--on o.kod=p.obec
--where kstrana=
--( select kstrana
--from pol_strany
--where zkratkak8='SPD')
--                      )  
--from poc_hlas p
--join obce_map o
--on o.kod=p.obec
--where kstrana=
--( select kstrana
--from pol_strany
--where zkratkak8='SPD')
--and nazev = 'Mělník'



--Ve kterých obcích vítěz voleb získal méně než 10 hlasů?


--select distinct nazev,generalizovanehranice
--from obce_map o
--join poc_hlas p
--on o.kod=p.obec
--where kod in (
--select obec
--from poc_hlas ph
--join pol_strany p
--on ph.kstrana=p.kstrana
--where pocmandcr=(
--select max(pocmandcr)
--from pol_strany)
--and poc_hlasu<10
--)



--Graficky znazornete ve kterych obcich ziskala nejvetsi strana ve snemovne prumerny pocet hlasu pro jednotlive strany z pohledu cele CR.
--select distinct nazev, poc_hlasu
--from obce_map o
--join poc_hlas p
--on o.kod=p.obec
--where o.kod in (
--select ph.obec
--from poc_hlas ph 
--join pol_strany p
--on ph.kstrana=p.kstrana
--join plat_hlas pl
--on pl.obec=ph.obec
--where pocmandcr=(
--select max(pocmandcr)
--from pol_strany)
--)
--and poc_hlasu between (select avg(poc_hlasu)-1
--from poc_hlas
--) and 
--(select avg(poc_hlasu)+1
--from poc_hlas
--)




--kolik voličů bylo přihlašeno do volebního seznamu parlamentních voleb 2017 v nejmenší obci v ČR


--select  nazev, vol_seznam
--from obce_map o
--join plat_hlas p
--on p.obec=o.kod
--order by st_area(generalizovanehranice) asc limit 1


--Jak dopadli volby ve 3 nejmene zalidnenych obcich?

--select o.nazev, zkratkak8,poc_hlasu
--from pol_strany ps
--join poc_hlas ph 
--on ps.kstrana=ph.kstrana
--join obce_map o
--on o.kod=ph.obec
--join sldb_b s
--on s.uzkod=o.kod
--order by vse3111/(st_area(generalizovanehranice)) asc limit 3




--Vypište politické strany, které neziskaly ani jeden voličský hlas v okresu Mladá Boleslav


--select zkratkak8 as politicke_strany
--from pol_strany
--where zkratkak8 not in (
--select distinct zkratkak8
--from pol_strany p
--join poc_hlas ph
--on p.kstrana=ph.kstrana
--join obce_map o
--on ph.obec=o.kod
--join okresy_map ok
--on ok.kod=o.okreskod
--where ok.nazev='Mladá Boleslav'
--)