with temp as ( select emp_id, experience, salary, SUM( salary ) over( partition by experience order by emp_id ) as running_total from office ) select * from temp where experience='Junior' and running_total
Use of COALESCE is very important here as there is a chance than not even one senior would be in our budget. The following is my approach: WITH Salary_sum as( select *,sum(salary) over (partition by experience order by salary) as Cumulative_Salary from HR_Selection ), Seniors as ( select * from Salary_sum where experience='senior' and Cumulative_Salary
with cte as ( select o.*,sum(salary) over (partition by experience order by salary) as cumm from office o) --select max(cumm) from cte where experience='Senior' and cumm
Using Nested cte's I know this is a bit crumpy and lot of lines but just another approach With senior as ( select *, sum(salary) over (order by salary asc) AS senior_cum from office where experience = 'Senior'), selected_seniors as ( select emp_id, experience, salary from senior where senior_cum
Really Informative video.. I got to learn a lot.. Thanks.👌
Thanks, I'm glad you found it helpful!
with temp as (
select emp_id, experience, salary,
SUM( salary ) over( partition by experience order by emp_id ) as running_total
from office )
select * from temp where experience='Junior' and running_total
Nice 👍
Use of COALESCE is very important here as there is a chance than not even one senior would be in our budget.
The following is my approach:
WITH Salary_sum as(
select *,sum(salary) over (partition by experience order by salary) as Cumulative_Salary from HR_Selection
),
Seniors as (
select * from Salary_sum where experience='senior' and Cumulative_Salary
nice one.
with cte as (
select o.*,sum(salary) over (partition by experience order by salary) as cumm from office o)
--select max(cumm) from cte where experience='Senior' and cumm
What is the level of this question?
Easy
Medium
Hard
medium
Using Nested cte's
I know this is a bit crumpy and lot of lines but just another approach
With senior as ( select *, sum(salary) over (order by salary asc) AS senior_cum from office where experience = 'Senior'),
selected_seniors as ( select emp_id, experience, salary from senior where senior_cum
nice one.