IBM SQL Interview Question | Using CTEs

[@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;

[@nirmalaswain4598]

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

[@badrilalnagar9232]

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

[@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;

[@mohanbharadwaj7684]

which is more optimized query. Subquery or CTE's

[@DevaSivaNagaSai]

cte is more efficient than sub query

[@saikirant4677]

clearly explained

[@vijeayanvj4367]

select employee_id,first_name,job_category from (select employee_id,first_name,salary,job_category, dense_rank()over(partition by job_category order by salary desc) as rn from employees) abc where rn=3 order by employee_id

[@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.

[@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;