Average Salary: Departments VS Company


  • 0

    Click here to see the full article post


  • 0
    L

    The numbering of the 3 steps is wrong, not "1. 1. 1.", but "1. 2. 3.".


  • 0

    @lleiou Thanks for the feedback. It has been corrected.


  • 0
    N

    If you are using PostgreSQL, you could write the solution mentioned above in a more readable way using CTEs (common table expressions):

    with company_avg as (
      select
          to_char(pay_date, 'YYYY-MM') "pay_month",
          avg(amount) "avg_company"
        from l615_salary
          join l615_employee using(employee_id)
        group by pay_month
    ), dept_avg as (
      select
          to_char(pay_date, 'YYYY-MM') "pay_month",
          department_id,
          avg(amount) "avg_dept"
        from l615_salary
          join l615_employee using(employee_id)
        group by pay_month, department_id
    )
    select
        pay_month,
        department_id,
        case
        when avg_dept > avg_company then 'higher'
        when avg_dept < avg_company then 'lower'
        when avg_dept = avg_company then 'same'
        end "comparison"
      from company_avg
        join dept_avg using(pay_month)
      order by pay_month desc, department_id
    ;
    

  • 0
    M

    You could write CTEs If you are using PostgreSQL:
    WITH company_salary AS (
    SELECT
    pay_date,
    avg(amount) comp_avg
    FROM salary
    GROUP BY pay_date
    ),
    dept_salary AS (
    SELECT
    pay_date,
    department_id,
    avg(amount) dept_avg
    FROM salary s JOIN employee e ON s.employee_id = e.employee_id
    GROUP BY pay_date, department_id
    )
    SELECT
    s1.pay_date,
    s2.department_id,
    CASE WHEN s2.dept_avg < s1.comp_avg
    THEN 'lower'
    WHEN s2.dept_avg > s1.comp_avg
    THEN 'higher'
    ELSE 'same' END
    FROM company_salary s1 JOIN dept_salary s2 ON s1.pay_date = s2.pay_date;


  • 0

    with dataset as (
    select
    avg(amount) over (partition by trunc(pay_date,'mm') ) - avg(amount) over (partition by trunc(pay_date,'mm'),department_id ) as salary_ind,
    department_id, trunc(pay_date,'mm') as pay_month
    from salary a
    join employee e on a.employee_id =e.employee_id
    )
    select distinct pay_month,department_id,
    case when salary_ind > 0 then 'lower'
    else (
    case when salary_ind < 0 then 'higher '
    else ('same') end
    ) end as comparison
    from dataset


Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.