SQL cvičení: zakázky v kontextu

[kokes]

with organy as (
	SELECT
		jmeno, prijmeni, narozeni, string_agg(distinct nazev_organu_cz, ', ') as org
	FROM
		psp.poslanci_osoby os
		INNER JOIN psp.poslanci_zarazeni zar on zar.id_osoba = os.id_osoba
		INNER JOIN psp.poslanci_organy org on org.id_organ = zar.id_of
	where cl_funkce = 0
	group by 1, 2, 3
), osoby as (
	SELECT
		pos.jmeno, pos.prijmeni, pos.narozeni, pos.org, ang.funkce, ang.ico, ang.datum_zapis, ang.datum_vymaz
	FROM
		organy pos
		inner join justice.angazovane_osoby ang on lower(ang.jmeno) = lower(pos.jmeno) and lower(ang.prijmeni) = lower(pos.prijmeni) and pos.narozeni = ang.datum_narozeni
)

SELECT
	osoby.jmeno, osoby.prijmeni, osoby.org, osoby.ico, osoby.datum_zapis, osoby.datum_vymaz, zzvz_dodavatele.dodavatel_nazev, osoby.funkce,
	zzvz_vz.nazev_vz,
	zzvz_vz.zadavatel_uredni_nazev,
	zzvz_vz.celkova_konecna_hodnota_vz,
	zzvz_vz.datum_odeslani_formulare_na_vvz,
	zzvz_vz.*
FROM
	zakazky.zzvz_vz
	INNER JOIN zakazky.zzvz_dodavatele USING (id_zakazky)
	INNER JOIN osoby on osoby.ico = zzvz_dodavatele.dodavatel_ico
WHERE
	datum_uverejneni > now() - interval '6 month'
	and osoby.datum_zapis < zzvz_vz.datum_odeslani_formulare_na_vvz
	and (osoby.datum_vymaz is null OR osoby.datum_vymaz > zzvz_vz.datum_odeslani_formulare_na_vvz)
	AND celkova_konecna_hodnota_vz > 5e8
LIMIT 100

[kokes]

A smlouvy v kontextu. Bohužel teda vzhledem ke způsobu nahrávání dat to tam máme trochu přeházený a bez zadavatele (protože ani nevíme, kdo to zadal).

with organy as (
	SELECT
		jmeno, prijmeni, narozeni, string_agg(distinct nazev_organu_cz, ', ') as org
	FROM
		psp.poslanci_osoby os
		INNER JOIN psp.poslanci_zarazeni zar on zar.id_osoba = os.id_osoba
		INNER JOIN psp.poslanci_organy org on org.id_organ = zar.id_of
	where cl_funkce = 0
	group by 1, 2, 3
), osoby as (
	SELECT
		pos.jmeno, pos.prijmeni, pos.narozeni, pos.org, ang.funkce, ang.ico, ang.datum_zapis, ang.datum_vymaz
	FROM
		organy pos
		inner join justice.angazovane_osoby ang on lower(ang.jmeno) = lower(pos.jmeno) and lower(ang.prijmeni) = lower(pos.prijmeni) and pos.narozeni = ang.datum_narozeni
)

SELECT
	osoby.jmeno, osoby.prijmeni, osoby.org, osoby.ico, osoby.datum_zapis, osoby.datum_vymaz, uc.nazev as dodavatel_nazev, osoby.funkce,
	sml.*,
	uc.*
FROM smlouvy.smlouvy sml
INNER JOIN smlouvy.ucastnici uc on sml.id_verze = uc.smlouva
INNER JOIN osoby on osoby.ico = uc.ico

WHERE sml.cas_zverejneni > now() - interval '6 month'
and (osoby.datum_vymaz is null OR osoby.datum_vymaz > sml.datum_uzavreni)
AND COALESCE(sml.hodnota_s_dph, hodnota_bez_dph) > 1e8
limit 100

[kokes]

A ReD (rozšířil jsem to časové období, protože data maj zpoždění)

with organy as (
	SELECT
		jmeno, prijmeni, narozeni, string_agg(distinct nazev_organu_cz, ', ') as org
	FROM
		psp.poslanci_osoby os
		INNER JOIN psp.poslanci_zarazeni zar on zar.id_osoba = os.id_osoba
		INNER JOIN psp.poslanci_organy org on org.id_organ = zar.id_of
	where not cl_funkce
	group by 1, 2, 3
), osoby as (
	SELECT
		pos.jmeno, pos.prijmeni, pos.narozeni, pos.org, ang.funkce, ang.ico, ang.datum_zapis, ang.datum_vymaz
	FROM
		organy pos
		inner join justice.angazovane_osoby ang on lower(ang.jmeno) = lower(pos.jmeno) and lower(ang.prijmeni) = lower(pos.prijmeni) and pos.narozeni = ang.datum_narozeni
)

SELECT
	osoby.jmeno, osoby.prijmeni, osoby.org, osoby.ico, osoby.datum_zapis, osoby.datum_vymaz, prijemce.obchodni_nazev, osoby.funkce,
	dotace.podpis_datum, castka_rozhodnuta, dotace.nazev, rozhodnuti.dotace_poskytovatel,
	dotace.*,
	prijemce.*,
	rozhodnuti.*
FROM
	red.dotace
	INNER JOIN red.prijemce using(id_prijemce)
	INNER JOIN red.rozhodnuti using(id_dotace)
	INNER JOIN osoby on osoby.ico = prijemce.ico
WHERE
	-- dotace.podpis_datum > now() - interval '12 months'
	rok_rozhodnuti = 2022
	and osoby.datum_zapis < dotace.podpis_datum
	and (osoby.datum_vymaz is null OR osoby.datum_vymaz > dotace.podpis_datum)
	AND rozhodnuti.castka_rozhodnuta > 1e6
ORDER by podpis_datum desc
LIMIT 100