SQL aufbau

abfragen.sql

-- #1 durschnittsgehalt nach länder, wenn keine mitarbeiter dann 0

select
  country_name,
  coalesce(avg(salary)::int, 0)
from
  countries
  left join
  locations using (country_id)
  left join
  departments using (location_id)
  left join
  employees using (department_id)
group by
  1;

-- #2 für all die mitarbeiter, namen den abteilungsleiternamen auszeigen

select m.first_name, m.last_name, e.last_name from employees m join departments d
 on m.employee_id = d.manager_id join employees e on d.manager_id = e.manager_id select
  e.first_name||' '||e.last_name as emp,
  m.first_name||' '||m.last_name as dmgr
from
  employees e
  join
  departments d using (department_id)
  join
  employees m on m.employee_id = d.manager_id
order by 1

-- #3 wie viel verdienen dept. manager (wo nen mgr gibt's) prozentuell mehr als die mitarbeiter aus dieser abteilung

select
  (mgr - emp) / emp::numeric * 100 as pct_diff 
from
(
select
  avg(salary) as mgr,
  avg(emp) as emp
from (
select
  m.salary,
  (select avg(salary) from employees
   where department_id = d.department_id
   and employee_id != m.manager_id) as emp
from
  departments d
  join
  employees m ON d.manager_id = m.employee_id
) a

) b;

-- 4. durschnittsgehalt für jegliche manager
select
  avg(salary)
from
  employees e
where
  exists (select * from departments d
          where d.manager_id = e.employee_id)
  or
  exists (select * from employees e2
          where e2.manager_id = e.employee_id);
 
-- variante 2 (schneller)         
select
  avg(salary)
from
  employees e
where
  employee_id in (select manager_id from departments)
  or
  employee_id in (select manager_id from employees)
;          
          
          
-- 5. jahresbewertung von beigetretene mitarbeiter

select
  year,
  case
    when count < 10 then 'bad'
    when count >= 10 and count < 20 then 'ok'
    else 'good'
  end as bewertung
from (
  select
    extract(year from hire_date) as year,
    count(*)
  from
    employees
  group by 1
) a
order by 1;


-- 6. beitretungen pro quartal als spalten

select
  year,
  sum(case when q = 1 then 1 else 0 end) as q1,
  sum(case when q = 2 then 1 else 0 end) as q2,
  sum(case when q = 3 then 1 else 0 end) as q3,
  sum(case when q = 4 then 1 else 0 end) as q4
from (
  select
    extract(year from hire_date) as year,
    extract(quarter from hire_date) as q
  from
    employees
) a
group by 1
order by 1;

-- variante mit FILTER
select
  year,
  count(*) FILTER (WHERE q = 1) as q1,
  count(*) FILTER (WHERE q = 2) as q2,
  count(*) FILTER (WHERE q = 3) as q3,
  count(*) FILTER (WHERE q = 4) as q4
from (
  select
    extract(year from hire_date) as year,
    extract(quarter from hire_date) as q
  from
    employees
) a
group by 1
order by 1;

-- für all die jobs anzahl von mitarbeiter die mehr/weniger als 6k verdienen
select
  job_title,
  count(*) FILTER (where salary < 6000) as weniger,
  count(*) FILTER (where salary >= 6000) as mehr
from
  employees
  join
  jobs using (job_id)
group by
  1
order by
  1;
  
  
-- wieviel prozent bekommen mitarbeiter von managergehalt durschnittlich
WITH q_mgr AS (
  select avg(salary) as mgr from employees
  where employee_id in (
     select manager_id from employees)
), q_emp AS (
  select avg(salary) as emp from employees
  where employee_id not in (
     select manager_id from employees
     where manager_id is not null)
)
select emp/mgr::numeric from q_mgr, q_emp;

-- variante 2
WITH q_mgr AS (
  select avg(salary) as mgr from employees
  where employee_id in (
     select manager_id from employees)
), q_emp AS (
  select avg(salary) as emp from employees
  where employee_id not in (
     select manager_id from employees
     where manager_id is not null)
)
select 
  (select emp from q_emp) /
  (select mgr::numeric from q_mgr) as diff;
  
  
-- all die untergeordnete von top-level manager
WITH RECURSIVE q_cte(employee_id, first_name, last_name) AS (
  select 100::numeric, first_name, last_name    from employees where employee_id = 100
  union all
  select 
    e.employee_id, e.first_name, e.last_name
  from
    employees e
    join
    q_cte q on q.employee_id = e.manager_id
)
select * from q_cte;

-- vom unter bis oben
WITH RECURSIVE q_cte(employee_id, manager_id, first_name, last_name) AS (
  select 206::numeric, manager_id, first_name, last_name    from employees where employee_id = 206
  union all
  select 
    e.employee_id, e.manager_id, e.first_name, e.last_name
  from
    employees e
    join
    q_cte q on e.employee_id = q.manager_id
)
select * from q_cte;


-- max verdiener für all die jobs
select
  e.job_id,
  e.salary,
  e.first_name||' '||e.last_name as name
from
  employees e
  join (
    select job_id, max(salary) as max_salary
    from employees group by job_id
  ) x on x.job_id = e.job_id and x.max_salary = e.salary;
  
-- variante 2, keine duplikate

WITH q_max AS (
select
  job_id,
  max(salary) as max_salary
from
  employees
group by
  job_id
)
select
  j.job_id,
  q.max_salary,
  max((select first_name||' '||last_name from employees
   where job_id = j.job_id and salary = q.max_salary limit 1)) as name
from
  jobs j
  join
  q_max q using (job_id)
group by
  1, 2
order by
  1, 2
;

-- mit DISTINCT ON
select
  distinct on (job_id)
  e.job_id,
  e.salary,
  e.first_name||' '||e.last_name as name
from
  employees e
order by
  1, 2 desc;

-- aggregate über mehrere gruppen
select
  region, null as country, avg(production)::int
from t_oil group by 1
union all
select
  null, country, avg(production)::int
from t_oil group by 2
union all
select
  null, null, avg(production)::int
from t_oil
;


-- popülärster job für all die abteilungen
select * from (
select
  department_name,
  (select job_id from employees
   where department_id = d.department_id
   group by job_id
   order by count(*) desc limit 1) as pop
from
  departments d
) a
where pop is not null;


-- mit mode()
select
  department_id,
  mode() WITHIN GROUP (
     ORDER BY job_id)
from
  employees
group by
  1;

-- 1.er quantal
select
  percentile_cont(0.25) WITHIN GROUP (
     ORDER BY salary desc)
from
  employees
;

select
  percentile_cont(0.25) WITHIN GROUP (
     ORDER BY production desc)
from
  t_oil
where
  country = 'USA'
;


-- all die quartilen separat per abteilung
select
  department_id,
  p[1],
  p[2],
  p[3],
  p[4]
from (
select
  department_id,
  percentile_cont(array[0.25,0.5,0.75,1])
    within group (order by salary desc) as p
from
  employees
group by
  1
) a;

-- hypotetisches rank 
select
  rank(11000) within group (
        order by production desc)
from 
  t_oil
where
  country = 'USA';


-- window functions
-- wieviel prozent vom abeteilungsdurschnitt
select
  department_id,
  first_name||' '||last_name,
  salary,
  round(100*salary / avg(salary::numeric) over(
             partition by department_id))
from
  employees;

-- top 3 produktsionsjahren für USA
select * from (select
  year,
  rank() over(order by production desc),
  production
from
  t_oil
where
  country = 'USA'
order by
  1) x where rank < 4;


-- 3.jahres durchschnitt für USA
select
  year,
  production,
  avg(production) over(order by year rows between 2 preceding and current row)
from
  t_oil
where
  country = 'USA'
order by
  year;

-- mitarbeiter die mehr verdienen als ihre manager
select
  e.*
from
  employees e
where
  e.salary > (
    select salary
    from employees
    where e.manager_id = employee_id
    )
;

-- mit joins
select
  e.*
from
  employees e
  join
  employees m
    on e.manager_id = m.employee_id
where
  e.salary > m.salary;

-- wieviel muss man verdien um in top 10% gehören (percentile_disc)
select percentile_cont(0.1) within group (order by salary desc) from employees;

-- eine liste von manager (keine abteilungsmanager) die frühere jobs bei der firma hatten
select
  *
from
  employees e
where
  employee_id in (select manager_id 
                  from employees)
  and employee_id in (select employee_id
                      from job_history);

select
  *
from
  employees e
where
  exists (select * from employees where
          e.employee_id = manager_id)
  and exists (select * from job_history where
              employee_id = e.employee_id);
              
select
  e.first_name, e.last_name
from
  employees e
  join
  employees u
    on u.manager_id = e.employee_id
  join
  job_history h
    on e.employee_id = h.employee_id
group by
  1, 2;

-- wieviel verdienen manager die schon frühere jobs hatten mehr als manager die keine hatten
with q_had as (
  select avg(salary)
  from employees e
  where employee_id in (select manager_id from employees)
    and employee_id in (select employee_id from job_history)
), q_no as (
  select avg(salary)
  from employees e
  where employee_id in (select manager_id from employees)
  and employee_id not in (select employee_id from job_history)
)
SELECT
  (q_had.avg - q_no.avg) / q_no.avg::numeric * 100
FROM
  q_had, q_no;


-- durch. dienstalter per abteilung,  ab letzten abt. beitretungsdatum
select department_name, avg(age) from (
select e.department_id, m.max - e.hire_date as age
from (
select department_id, max(hire_date)
from employees group by 1
) m 
join employees e 
  on e.department_id = m.department_id
) m2
join departments d on d.department_id = m2.department_id
group by 1
order by 2 desc;

-- mit fenster funktionen
select
  department_name, avg(max - hire_date) as age
from (
select
  department_name,
  hire_date,
  max(hire_date) over(partition by department_id)
from
  employees
  join
  departments using (department_id)
) a
group by 1
order by 2 desc;


-- global ölproduktion y-o-y change
select
  *,
  round((production - lag) /
        lag::numeric * 100, 1) as yoy_diff
from (
select
  year, 
  production, 
  lag(production) over (order by year)
from (
  select year, sum(production) as production
  from t_oil
  group by 1
  ) a
) b;