EXPLAIN ANALYSE
SELECT
       dt.Name,
       wg.Name,
       wg.Phone,
       jb.MaxPrice,
       jb.Minprice
FROM jobs as jb
JOIN Workgiver as wg on jb.BossID = wg.BossID
JOIN Duty as dt on jb.PositionID = dt.PositionID
JOIN VacationResume as vr on jb.VacationID = vr.VacationID
LEFT OUTER JOIN Resume as res on vr.ResumeID = res.ResumeID
LEFT OUTER JOIN users as us on us.UserID = res.UserID
WHERE ((dt.Name LIKE '') and (wg.Name NOT LIKE 'User54554') and (us.Name != 'User2534')
and (Minprice > 15000 and Maxprice < 110000));

SELECT
       wg.Name,
       wg.Phone,
       jb.Maxprice,
       jb.Minprice,
       ((jb.Maxprice + jb.Minprice) / 2) AS AVG
FROM Workgiver as wg
JOIN jobs as jb on wg.BossID = jb.BossID
JOIN VacationResume as vr on jb.VacationID = vr.VacationID
JOIN Candidats as can on vr.VacationResumeID = can.VacationResumeID
WHERE (date_trunc('month', can.Date) >= (date_trunc('month', current_date) - ('2 month'::interval)))
order by ((jb.MaxPrice + jb.MinPrice) / 2) DESC
LIMIT 5;

EXPLAIN ANALYZE
SELECT DISTINCT jb.VacationID, max(can.Date)
FROM jobs as jb
LEFT OUTER JOIN VacationResume as vr on jb.VacationID = vr.VacationID
LEfT OUTER JOIN Candidats as can on vr.VacationResumeID = can.VacationResumeID
GROUP BY (jb.VacationID, can.Date)
HAVING (can.Date <= (date_trunc('year', current_date) - ('1 year'::interval)))
order by (max(can.Date)) DESC;

-- create index on Candidats (EXTRACT(quarter from Date)) ;
-- drop index "Candidats_date_part_idx";

--HW 2
--1
explain analyse
SELECT dt.Name,
count(case EXTRACT(quarter from can.Date) when 1 then 1 else NULL end) as Fir,
count(case EXTRACT(quarter from can.Date) when 2 then 1 else NULL end) as Sec,
count(case EXTRACT(quarter from can.Date) when 3 then 1 else NULL end) as Thi,
count(case EXTRACT(quarter from can.Date) when 4 then 1 else NULL end) as Four,
EXTRACT(YEAR FROM can.Date) as Year
FROM Duty as dt
LEFT OUTER JOIN (jobs as jb LEFT JOIN (VacationResume as vr LEFT JOIN Candidats as can
on vr.VacationResumeID = can.VacationResumeID)
ON jb.VacationID = vr.VacationID)
ON dt.PositionID = jb.PositionID
GROUP BY (dt.PositionID, EXTRACT(YEAR FROM can.Date))
--having (EXTRACT(YEAR FROM C."Date") is not NULL)
ORDER BY (dt.PositionID);



SELECT dt.Name,
count(case (EXTRACT(quarter from can.Date) * 10000) + EXTRACT(Year from can.Date)
when 10000 + 2018 then 1
else NULL end) as QU1,
count(case (EXTRACT(quarter from can.Date) * 10000) + EXTRACT(Year from can.Date)
when 20000 + 2018 then 1
else NULL end) as QU2,
count(case (EXTRACT(quarter from can.Date) * 10000) + EXTRACT(Year from can.Date)
when 30000 + 2018 then 1
else NULL end) as QU3,
count(case (EXTRACT(quarter from can.Date) * 10000) + EXTRACT(Year from can.Date)
when 40000 + 2018 then 1
else NULL end) as QU4
FROM Duty as dt
LEFT OUTER JOIN (jobs as jb LEFT JOIN (VacationResume as vr LEFT JOIN Candidats as can
on vr.VacationResumeID = can.VacationResumeID)
ON jb.VacationID = vr.VacationID)
ON dt.PositionID = jb.PositionID
GROUP BY (dt.PositionID)
ORDER BY (dt.PositionID);
--2


SELECT DISTINCT wg.Name, max(can.Date)
FROM Workgiver as wg
LEFT OUTER JOIN jobs as jb on wg.BossID = jb.BossID
left outer JOIN VacationResume as vr on jb.VacationID = vr.VacationID
left outer JOIN Candidats as can on vr.VacationResumeID = can.VacationResumeID
GROUP BY (wg.Name, can.Date)
HAVING (can.Date <= (date_trunc('year', current_date) - ('1 year'::interval)))
order by (max(can.Date)) DESC