query = spark.sql("""with cte as (select dept_id,emp_name,salary, row_number() over(partition by dept_id order by salary desc, emp_name) as rn, count(dept_id) over (partition by dept_id order by dept_id) as dept_count from emp ) select dept_id,max(case when rn = 1 then emp_name else Null end ) as max_salary, min(case when rn = dept_count then emp_name else Null end) as min_salary from cte group by dept_id""")
![Twenty One Pilots - “The Line” (from Arcane Season 2) [Official Music Video]](http://i.ytimg.com/vi/E2Rj2gQAyPA/mqdefault.jpg)
For dataset please join our telegram channel.
t.me/CognitiveCoders
good logic and well explained, thanks
Thanks. Please do like, share and subscribe for supporting us
Nice explanation 👌 👍 👏
Thanks. Please do like, share and subscribe for supporting us.
query = spark.sql("""with cte as (select dept_id,emp_name,salary, row_number() over(partition by dept_id order by salary desc, emp_name) as rn,
count(dept_id) over (partition by dept_id order by dept_id) as dept_count from emp
)
select dept_id,max(case when rn = 1 then emp_name else Null end ) as max_salary,
min(case when rn = dept_count then emp_name else Null end) as min_salary
from cte group by dept_id""")