Analytical Functions Own Practice on HR Schema

select distinct first_name, hire_date,salary ,
rank() over (order by salary) as rank from employees
where manager_id between 105 and 150
order by salary desc

select first_name, hire_date,salary ,
rank() over (order by salary) as rank,
dense_rank() over (order by salary) as dense_rank
 from employees
where manager_id between 105 and 150
order by salary desc

select e.*,--rank() over (order by salary) as rank,
avg(salary)  keep (dense_rank last order by salary) over (partition by e.manager_id) as dense_rank
 from employees e
where manager_id between 105 and 150
--group by first_name , hire_date,salary
order by salary desc


select e.EMPLOYEE_ID,E.MANAGER_ID , SUM(SALARY) over (partition by e.manager_id ORDER BY E.SALARY) EMP_UNDER_MANAGER from employees e
--group by manager_id

select ROWNUM,e.EMPLOYEE_ID,E.MANAGER_ID , E.SALARY,
SUM(SALARY) over (partition by e.manager_id) EMP_UNDER_MANAGER,
COUNT(*) OVER( partition by e.manager_id) TOTAL,
COUNT(DISTINCT E.MANAGER_ID) OVER()  NO_OF_MANAGER,
ROUND(100*RATIO_TO_REPORT(SALARY) OVER (PARTITION BY DEPARTMENT_ID),2) PER_SAL,
ROUND(100*RATIO_TO_REPORT(SALARY) OVER (),2) PER_SAL_TOT
--MAX(SALARY) OVER( partition by e.MANAGER_ID) MAX_SAL
from employees e

select ROWNUM,EMPLOYEE_ID,DEPARTMENT_ID,
COUNT(EMPLOYEE_ID) OVER(PARTITION BY DEPARTMENT_ID) COUNT,
MAX(SALARY) OVER (PARTITION BY DEPARTMENT_ID) SAL_MAX,
--COUNT(DISTINCT SALARY) OVER (PARTITION BY DEPARTMENT_ID) D_SAL
ROUND(100*RATIO_TO_REPORT(SALARY) OVER (PARTITION BY DEPARTMENT_ID),2) PER_SAL,
ROUND(100*RATIO_TO_REPORT(SALARY) OVER (),2) PER_SAL_TOT
FROM EMPLOYEES
WHERE DEPARTMENT_ID=20
ORDER BY SAL_MAX
--GROUP BY MANAGER_ID,EMPLOYEE_ID

select EMPLOYEE_ID,JOB_ID,SALARY,
COUNT(EMPLOYEE_ID) OVER(PARTITION BY JOB_ID) COUNT,
MAX(SALARY) OVER (PARTITION BY JOB_ID) SAL_MAX,
--COUNT(DISTINCT SALARY) OVER (PARTITION BY DEPARTMENT_ID) D_SAL
ROUND(100*RATIO_TO_REPORT(SALARY) OVER (PARTITION BY JOB_ID),2) PER_SAL_DEPT,
TO_CHAR(ROUND(100*RATIO_TO_REPORT(SALARY) OVER (),2),'9.99')||'%' PER_SAL_TOT
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
AND SALARY>5000
--ORDER BY \

select EMPLOYEE_ID,DEPARTMENT_ID,
COUNT(EMPLOYEE_ID) OVER(PARTITION  BY DEPARTMENT_ID ) COUNT,
MAX(SALARY) OVER (ORDER BY DEPARTMENT_ID ) SAL_MAX
FROM EMPLOYEES
WHERE DEPARTMENT_ID IS NOT NULL
--ORDER BY SAL_MAX

SELECT FIRST_NAME,EMPLOYEE_ID,SALARY,DEPARTMENT_ID,
COUNT(*) OVER (PARTITION BY DEPARTMENT_ID) COUNT,
ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) NUM,
DENSE_RANK() OVER (ORDER BY SALARY ) DR,
RANK() OVER (ORDER BY SALARY ) RANK
FROM EMPLOYEES

SELECT * FROM (SELECT FIRST_NAME,EMPLOYEE_ID,SALARY,DEPARTMENT_ID,
COUNT(*) OVER (PARTITION BY DEPARTMENT_ID) COUNT,
ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) ROW_NUM
FROM EMPLOYEES
) WHERE ROW_NUM=1


SELECT * FROM
(SELECT FIRST_NAME,EMPLOYEE_ID,SALARY,DEPARTMENT_ID,
--COUNT(*) OVER (PARTITION BY DEPARTMENT_ID) COUNT,
ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) ROW_NUM,
RANK() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) RANK
--LEAD (SALARY,1,-1) OVER (PARTITION BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID) LEAD
FROM EMPLOYEES)
WHERE RANK=2

 
SELECT FIRST_NAME,EMPLOYEE_ID,SALARY,DEPARTMENT_ID,
COUNT(*) OVER (PARTITION BY DEPARTMENT_ID) COUNT,
ROW_NUMBER() OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY) ROW_NUM,
LAST_VALUE(SALARY) OVER (PARTITION BY DEPARTMENT_ID ORDER BY SALARY ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX,
SUM(SALARY) OVER(PARTITION BY DEPARTMENT_ID ORDER BY SALARY ROWS  BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) CUMM_SAL
--LEAD (SALARY,1,-1) OVER (PARTITION BY DEPARTMENT_ID ORDER BY DEPARTMENT_ID) LEAD
FROM EMPLOYEES


SELECT DISTINCT DEPARTMENT_ID,MAX(SALARY),COUNT(EMPLOYEE_ID) ,ROWNUM FROM EMPLOYEES
GROUP BY DEPARTMENT_ID,ROWNUM
ORDER BY ROWNUM

SELECT ROWNUM,DEPARTMENT_ID, SALARY FROM EMPLOYEES


SELECT * FROM EMPLOYEES

create table max_temp2 as
select e.*,t.max_sal from employees e, max_temp t
where e.manager_id = t.manager_id

select first_name, hire_date,salary,first_value(first_name)
--rank() over (order by salary) as rank
from employees
where manager_id between 105 and 150
order by salary desc