SQLPad
Sign In

151. Salary report

easy dropbox
  • ********************************************************
  • **********************************
    • <= 50000
    • > 50000, <= 80000
    • > 80000, <= 100000
    • > 100000

Table: employee

Employee information, department they are in and their annual salary.

  col_name     | col_type
---------------+-------------------
id             | bigint
full_name      | varchar (100)
department_id  | int
salary         | float

Sample results 

salary_group         | count
---------------------+----------
 <= 50000            |   123
 > 50000, <= 80000   |   456
 > 80000, <= 100000  |   789

Solution postgres

-- Q 151
SELECT CASE WHEN salary <= 50000 THEN '<= 50000'
            WHEN salary <= 80000 THEN '>50000, <= 80000'
            WHEN salary <= 100000 THEN '>80000, <= 100000'
            WHEN salary > 100000 THEN '>100000'
            ELSE NULL END AS salary_group,
        COUNT(id)
FROM employee
GROUP BY 1
ORDER BY 1

Explanation

This query is selecting data from the "employee" table and grouping it by salary ranges. It uses the CASE statement to categorize the salaries into four groups: less than or equal to 50,000, greater than 50,000 but less than or equal to 80,000, greater than 80,000 but less than or equal to 100,000, and greater than 100,000.

The query then counts the number of employees in each salary group and displays the results in ascending order based on the salary group.

This query can help a junior data analyst to better understand the distribution of salaries among employees and identify any salary patterns or outliers within the organization.

Expected results


More Dropbox questions

ID Title Level FTPR
152 Salary analysis dropbox easy
20%
153 Project ROI dropbox easy
18%