IBM SQL Interview Question | Using CTEs

[@nirmalaswain4598]

thank you mam for such a great kind kind of seriese please continue this section

[@harshitsalecha221]

SELECT employee_id, first_name, job_category
FROM (SELECT employee_id,
first_name,
job_category,
DENSE_RANK() OVER(PARTITION BY job_category ORDER BY salary DESC) as rn FROM employees) as a
WHERE rn=3;

[@badrilalnagar9232]

The family system of India is the basic source of Indian culture.

[@mohanbharadwaj7684]

which is more optimized query. Subquery or CTE's

[@DevaSivaNagaSai]

cte is more efficient than sub query

[@echodelta7680]

Hi Nishtha,
1. If there are more than 1 employee in each dept. having same salary as third highest salary, then what result would your query return?
2. Say, there is a table TRANSACTION having two columns ID and BILL. Table has 100 records, with IDs repeated many times, each ID having different or same bills. Here's a SELECT query to find the avg. bill for each ID over the table :
SELECT ID, AVG(BILL) FROM TRANSACTION GROUP BY ID;
My question is, does the AVG function run 100 x 100 times, top to bottom scanning one ID at a time? Note that I am not asking about the order of query execution (I know the order). I'm asking how these functions operate on the table rows in the background.

[@saikirant4677]

clearly explained

[@rajanchaurasiya1734]

with cte as(
select *,
dense_rank() over(partition by job_category order by salary desc) as rnk
from employees
)
select employee_id,first_name,job_category
from cte
where rnk = 3
order by employee_id;

[@HARSHRAJ-gp6ve]

simple dense rank question
with cte as(
select employees.*,DENSE_RANK()OVER(PARTITION BY job_category ORDER BY salary DESC) as x1 FROM employees
)
select employee_id,first_name,job_category FROM cte where x1=3;

[@theinsightminer08]

SELECT
employee_id,
first_name,
job_category
FROM
(SELECT
*,
DENSE_RANK() OVER(PARTITION BY job_category ORDER BY salary DESC) as rnk
FROM employees) as Ranking
WHERE rnk = 3
ORDER BY employee_id;