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

Welcome to Day 41 of my 100 Days Challenge!

Join me as I walk through each problem step-by-step, providing detailed explanations and practical tips. Don't forget to like, subscribe, and hit the bell icon to stay updated with daily challenges!

Links:
GitHub Repository - Get the question & datasets

Best Data Analytics Course:

My Other YouTube Videos -
SQL 30 Days Road Map - [Free Learning Resources]

#SQL #DataAnalysis #PortfolioProject #DataCleaning #BusinessAnalysis #DataAnalyst #SQLProject #EDA #SQLTutorial #GitHub #DataScience #CareerInData #LearnSQL #SQLforDataAnalyst #AspiringDataAnalyst #ResumeProject #EndToEndProject

[zero_analyst]

🎯 Get Latest SQL Guided Project (1M sales records)

🎯 Get Latest
End to End Advanced SQL Project (Amazon)+ 70 Business Problems

🐍 All Python Projects:
Python ETL - Data Cleaning Project

[SangramSwain-mlvh]

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

[fahimkasmani]

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

[SnapMathShorts]

My approach :

with cte as (
select *, max(salary) over(partition by company_id) as max_salary
from salaries)

select
company_id,
employee_id,
employee_name,
salary,
case when max_salary <1000 then salary
when max_salary >=1000 and max_salary<=10000 then salary - 0.24*salary
else salary - 0.49*salary end as new_salary
from cte;

[souvik]

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 <1000 then round(salary, 0)
when max_sal between 1000 and 10000 then round(salary*0.76, 0)
when max_sal > 10000 then round(salary*0.51, 0) end as actual_salary from cte1

[kiranchandukar]

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

[Chathur]

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

[anirbanbiswas]

THIS CAN BE DONE WITH MORE EASIER PROCESS and without any


with cte as(select *, max(salary) over(partition by company_id) as max_sal from salaries)
select *, case when max_sal<1000 then salary
when max_sal>1000 and max_sal<10000 then salary-((salary*24)/100)
when max_sal>10000 then salary-((salary*49)/100) end as new_salary from cte

[HARSHRAJ-gpve]

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

[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

[zero_analyst]

Start Data Career in 2025 (Best Course)

[HARSHRAJ-gpve]

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
), cte2 as(
select cte1.*,
case
when sal1<1000 THEN 0
when sal1 BETWEEN 1000 and 10000 THEN 0.24*(salary)
ELSE 0.49*(salary)
END AS tax_val
FROM cte1
)
select company_id, employee_id, employee_name, (salary-tax_val) as sal_found FROM cte2;