data analyst sql interview questions and answers #41/100 | HSBC | SQL Tutorials | Product Based

[@souvik0077]

My approach: with cte1 as( select s.*,a.max_sal from salaries s inner join (select company_id,max(salary) as max_sal from salaries group by company_id)a on s.company_id=a.company_id) select company_id,employee_id,employee_name, case when max_sal 10000 then round(salary*0.51,0) end as actual_salary from cte1

[@SangramSwain-ml1vh]

I have improved a lot by watching this series ❤ Thanks vai 🎉

[@zero_analyst]

Thank you!

[@kiranchandukar1657]

select *, max(salary) over(partition by company_id) as max_sal, case when max(salary) over(partition by company_id) >10000 then salary*.51 when max(salary) over(partition by company_id) between 1000 and 10000 then salary*.76 else salary end as sal_tax from Salaries

[@Chathur732]

with cte as ( select *, rank() over(partition by company_id order by salary desc) as rn from ( select *,case when salary < 1000 then salary when salary between 1000 and 10000 then salary*0.24 else salary * 0.49 end as taxated_salary from salaries)) select company_id, employee_id,round((case when salary - taxated_salary = 0 then salary else salary - taxated_salary end),0) as taxed_salary from cte

[@fahimkasmani1474]

WITH CTE AS ( Select company_id, max(salary) as max_sal From salaries Group by company_id ), Finale as ( Select s.*, c.max_sal From CTE as c Join salaries as s On c.company_id = s.company_id ) Select company_id, employee_id, employee_name, salary, Max_sal, ROUND (Case when Max_sal < 1000 then salary When Max_sal between 1000 and 10000 then salary - (salary * 0.24) Else salary - (salary * 0.49) End) as Salaries_after_tax from Finale

[@shaikgouse7742]

Good approach sir

[@anirbanbiswas7624]

THIS CAN BE DONE WITH MORE EASIER PROCESS and without any JOIN---------------------------------------- with cte as(select *, max(salary) over(partition by company_id) as max_sal from salaries) select *,case when max_sal1000 and max_sal10000 then salary-((salary*49)/100) end as new_salary from cte

[@HARSHRAJ-gp6ve]

bhaiya,the dml command for the above dataset is not present currently in your github repo,plese provide dml commands for the same

[@zero_analyst]

done

[@Morningbcode]

[@PalashSharmahere]

WITH cte AS ( SELECT company_id, MAX(salary) AS maxsal FROM salaries GROUP BY company_id ), cte2 AS ( SELECT a.*, b.maxsal FROM cte b JOIN salaries a ON a.company_id = b.company_id ), cte3 as( SELECT *, CASE WHEN maxsal < 1000 THEN 0 WHEN maxsal BETWEEN 1000 AND 10000 THEN 0.24 ELSE 0.49 END AS tax_slab FROM cte2) select company_id,employee_id,employee_name , salary, maxsal ,tax_slab,(salary - (salary*tax_slab)) as finalsal from cte3

[@anirbanbiswas7624]

THIS CAN BE DONE WITH MORE EASIER PROCESS---------------------------------------- with cte as(select *, max(salary) over(partition by company_id) as max_sal from salaries) select *,case when max_sal1000 and max_sal10000 then salary-((salary*49)/100) end as new_salary from cte

[@HARSHRAJ-gp6ve]

with cte as( SELECT company_id,max(salary) as sal1 FROM salaries GROUP BY company_id ),cte1 as( select Salaries.*,sal1 FROM cte JOIN Salaries ON cte.company_id=Salaries.company_id ),cte2 as( select cte1.*, case when sal1