Skip to content

Instantly share code, notes, and snippets.

@vijaysharmay

vijaysharmay/queries.sql

Last active September 3, 2022 05:39
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save vijaysharmay/9c30e5cf121e15afc15f42b42f8b0cb3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save vijaysharmay/9c30e5cf121e15afc15f42b42f8b0cb3 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
queries.sql
-- current employee snapshot
select
e.emp_no,
CONCAT(e.first_name,' ', e.last_name) as full_name,
e.gender,
ct .title,
d.dept_name,
CAST(datediff(CURRENT_DATE(), e.birth_date) / 365 as UNSIGNED) as age,
CAST(datediff(CURRENT_DATE(), e.hire_date) / 365 as UNSIGNED) as time_in_company,
cs.salary
from curr_dept cd
join employees e on cd.emp_no = e.emp_no
join departments d on d.dept_no = cd.dept_no
join curr_sal cs on cs.emp_no = cd.emp_no
join curr_title ct on ct.emp_no = cd.emp_no
-- Top 3 current employees per department in terms of salary
select
emp_no,
full_name,
gender,get top performers in each age group
and @rankval = salary then @rank
when @partval = dept_name
and (@rankval := salary) is not null then @rank + 1
when (@partval := dept_name) is not null
and (@rankval := salary) is not null is not null then 1
end as rnk
from
(
select
*
from
curr_emp_data
-- Add the below condition(s) for finding the top 3 current male employees in terms of salary
-- where gender = 'M'
-- where gender = 'F'
order by
dept_name, salary DESC) ced, (
select
@rankval := NULL, @partval := NULL, @rank := NULL ) X ) Y
where
rnk < 4
-- Binning Queries for Age, Year, Salary
get top performers in each age group
get top performers in each age group for every year
get salary ranges for each department
get salary hikes historically for each department (rolling diff)
-- get salary range across departments and titles
select dept_name, title, CONCAT(min(salary), ' - ',max(salary)) as salary_range from curr_emp_data ced group by 1, 2
-- get salary range across departments
select dept_name, CONCAT(min(salary), ' - ',max(salary)) as salary_range from curr_emp_data ced group by 1
-- get salary range across titles
select title, CONCAT(min(salary), ' - ',max(salary)) as salary_range from curr_emp_data ced group by 1
Raw
views.sql
-- Get current department for each employee
CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `curr_dept` AS
select
`de2`.`emp_no` AS `emp_no`,
`de2`.`dept_no` AS `dept_no`,
`cd`.`from_date` AS `from_date`,
`cd`.`to_date` AS `to_date`
from
(`employees`.`dept_emp` `de2`
join (
select
`de`.`emp_no` AS `emp_no`,
max(`de`.`from_date`) AS `from_date`,
max(`de`.`to_date`) AS `to_date`
from
`employees`.`dept_emp` `de`
group by
`de`.`emp_no`) `cd` on
(((`cd`.`emp_no` = `de2`.`emp_no`)
and (`cd`.`from_date` = `de2`.`from_date`)
and (`cd`.`to_date` = `de2`.`to_date`))))
-- Get current title for each employee
CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `curr_title_emp` AS
select
`X`.`emp_no` AS `emp_no`,
`employees`.`titles`.`title` AS `title`,
`employees`.`titles`.`from_date` AS `from_date`,
`employees`.`titles`.`to_date` AS `to_date`
from
(`employees`.`titles`
join (
select
`employees`.`titles`.`emp_no` AS `emp_no`,
max(`employees`.`titles`.`from_date`) AS `from_date`,
max(`employees`.`titles`.`to_date`) AS `to_date`
from
`employees`.`titles`
group by
`employees`.`titles`.`emp_no`) `X` on
(((`X`.`emp_no` = `employees`.`titles`.`emp_no`)
and (`X`.`from_date` = `employees`.`titles`.`from_date`)
and (`X`.`to_date` = `employees`.`titles`.`to_date`))))
-- Get current salary for each employee
CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `curr_sal` AS
select
`X`.`emp_no` AS `emp_no`,
`employees`.`salaries`.`salary` AS `salary`,
`employees`.`salaries`.`from_date` AS `from_date`,
`employees`.`salaries`.`to_date` AS `to_date`
from
(`employees`.`salaries`
join (
select
`employees`.`salaries`.`emp_no` AS `emp_no`,
max(`employees`.`salaries`.`from_date`) AS `from_date`,
max(`employees`.`salaries`.`to_date`) AS `to_date`
from
`employees`.`salaries`
group by
`employees`.`salaries`.`emp_no`) `X` on
(((`X`.`emp_no` = `employees`.`salaries`.`emp_no`)
and (`X`.`from_date` = `employees`.`salaries`.`from_date`)
and (`X`.`to_date` = `employees`.`salaries`.`to_date`))))
-- Get current manager for each department
CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `curr_dept_mgr` AS
select
`X`.`dept_no` AS `dept_no`,
`employees`.`dept_manager`.`emp_no` AS `emp_no`,
`employees`.`dept_manager`.`from_date` AS `from_date`,
`employees`.`dept_manager`.`to_date` AS `to_date`
from
(`employees`.`dept_manager`
join (
select
`employees`.`dept_manager`.`dept_no` AS `dept_no`,
max(`employees`.`dept_manager`.`from_date`) AS `from_date`,
max(`employees`.`dept_manager`.`to_date`) AS `to_date`
from
`employees`.`dept_manager`
group by
`employees`.`dept_manager`.`dept_no`) `X` on
(((`X`.`dept_no` = `employees`.`dept_manager`.`dept_no`)
and (`X`.`from_date` = `employees`.`dept_manager`.`from_date`)
and (`X`.`to_date` = `employees`.`dept_manager`.`to_date`))))
-- Get consolidated data about each employee
CREATE OR REPLACE
ALGORITHM = UNDEFINED VIEW `curr_emp_data` AS
select
`e`.`emp_no` AS `emp_no`,
concat(`e`.`first_name`, ' ', `e`.`last_name`) AS `full_name`,
`e`.`gender` AS `gender`,
`ct`.`title` AS `title`,
`d`.`dept_name` AS `dept_name`,
cast(((to_days(curdate()) - to_days(`e`.`birth_date`)) / 365) as unsigned) AS `age`,
cast(((to_days(curdate()) - to_days(`e`.`hire_date`)) / 365) as unsigned) AS `time_in_company`,
`cs`.`salary` AS `salary`
from
((((`employees`.`curr_dept` `cd`
join `employees`.`employees` `e` on
((`cd`.`emp_no` = `e`.`emp_no`)))
join `employees`.`departments` `d` on
((`d`.`dept_no` = `cd`.`dept_no`)))
join `employees`.`curr_sal` `cs` on
((`cs`.`emp_no` = `cd`.`emp_no`)))
join `employees`.`curr_title` `ct` on
((`ct`.`emp_no` = `cd`.`emp_no`)))
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment