Salary Report Generator - SQL Interview Query 5 | SQL Problem Level "MEDIUM"
Вставка
- Опубліковано 1 сер 2024
- 30DaySQLQueryChallenge is a series of 30 videos covering 30 SQL Interview Queries. This is the FIFTH video in this series. This video series aims to provide 30 SQL Queries that can be asked during SQL Interviews. I will explain how to solve and address such SQL queries during interviews in these videos.
Let's follow the below routine to make the best use of it:
1. Watch the UA-cam video (first half) to understand the problem statement.
2. Go to my discord server (link below), download the dataset for each problem, and try solving it yourself.
3. Share your solution on Discord and discuss different solutions and issues on my Discord server.
4. Watch the second half of my UA-cam video to find my solution to the problem.
5. Share it with your contacts and spread the knowledge.
DOWNLOAD the Dataset from below:
Discord server: / discord
Blog website: techtfq.com/blog/30daysqlquer...
Timeline:
00:00 Intro
00:10 Understanding Problem Statement
02:30 Solution - Populate Employee Transaction table
11:40 Solution - Generate report in PostgreSQL
25:33 Solution - Generate report in Microsoft SQL Server
Thanks for participating in this challenge!
Good luck and Happy Learning!
I have solved it with case statement
with cte as(
select * from salary
cross join
(select income,percentage
from income
union
select deduction ,percentage from deduction )),
cte2 as(
select *,case
when income='Insurance' then round((base_salary * (cast(percentage as decimal)/100)),2)
when income='House' then round((base_salary * (cast(percentage as decimal)/100)),2)
when income='Basic' then round((base_salary * (cast(percentage as decimal)/100)),2)
when income='Health' then round((base_salary * (cast(percentage as decimal)/100)),2)
when income='Allowance' then round((base_salary * (cast(percentage as decimal)/100)),2)
when income='Others' then round((base_salary * (cast(percentage as decimal)/100)),2) end as amount
from cte)
select emp_name,
sum(case when income='Basic' then amount end) as basic,
sum(case when income='Allowance' then amount end) as Allowance,
sum(case when income='Others' then amount end) as Others,
sum(case when income in('Others','Allowance','Basic') then amount end) as gross,
sum(case when income ='Insurance' then amount end) as insurance,
sum(case when income ='Health' then amount end )as Health,
sum(case when income ='House' then amount end )as House,
sum(case when income in('House','Health','Insurance') then amount end )as total_deduction,
sum(case when income in('Others','Allowance','Basic') then amount end) -
sum(case when income in('House','Health','Insurance') then amount end )as net_pay
from cte2
group by 1
Thank you for your efforts 🙏
Thank You as small word for your work ,dedication and finally helping to others progress. Thank you very Much.
Nice question…it got cleared two concepts cross join and pivot…thank u…keep it up…waiting for more such question…❤
you are brilliant , awsome question :)
Thank you so much for the detailed explanation.😊
Hi I paid money for the sql class and having issues and you haven’t been responding to any messages about additional materials we are supposed to have received. Definitely not good service.
you the best brother thanks
with cte as
(
select id, income as trans_type, percentage
from
income
union all
select id, deduction as trans_type, percentage
from deduction
),
cte2 as
(
select emp_id, emp_name, trans_type, (percentage * base_salary)/100 as amount
from salary
cross join
cte
),
cte3 as
(
select emp_name as employee,
sum(case when trans_type = 'Basic' then amount else null end) as Basic,
sum(case when trans_type = 'Allowance' then amount else null end) as Allowance,
sum(case when trans_type = 'Others' then amount else null end) as Others,
sum(case when trans_type = 'Insurance' then amount else null end) as Insurance,
sum(case when trans_type = 'Health' then amount else null end) as Health,
sum(case when trans_type = 'House' then amount else null end) as House
from cte2
group by emp_name
)
select *,
Basic + Allowance + Others as Gross,
Insurance + Health + House as Total_deduction
from cte3
Thank You 🎉🎉❤
Thank you !!
good question :)
thank you
sir you could directly multiply salary column with percentage from the union and divide by 100 as the cross join would work for all rows and we have same formula for all rows
that's what I was thinking, I believe there was no need to write case statement but thanks for your efforts @techtfq
exactly
select s.emp_id, s.emp_name, a.income as trans_type,
(s.base_salary*a.percentage)/100 as amount
from salary as s
cross join ((select * from income)
union all
(select * from deduction)) a
this is what i did and it worked fine, lmk if i missed anything
Nice thoufiq, buts it's hard to remember the syntax on cross tab and pivot if we don't use frequently 😊
used PostgreSQL without cast , it worked fine for me
select s.emp_id,s.emp_name ,d.deduction as trans_type, (s.base_salary *d.percentage /100) as Amount
from salary s cross join deduction d
union
select s.emp_id,s.emp_name ,i.income as trans_type, (s.base_salary *i.percentage /100) as Amount
from salary s cross join income i;
Done day5 of #30daySQLQueryChallenge
My point is at 10:50 why do we use the case statement even though we are using the same formula base_salary* percentage/100 by using this directly is also fine right !!!!!
you are right.. It was unnecessary over here.
I had the same question. I in fact came to read the comments to know if anyone had this question. Thanks thoufiq for the confirmation here.
Hi Thoufiq,
Could you please explain the reason why you have used sum alone why not Max or min for aggregation in pivot query.
i will get some clarification on sum(amount) 😌. Even if we have used the sum of amount, the values in the result set are not added together to bring a new value.
-------------2nd approach for Create Emp_Transaction Table in SSMS-------------
select * into Emp_Transaction from(
select a.Emp_id,a.Name,b.income as tran_type,a.Sal/100*b.percentage as amount from Sal a cross join(
select income,percentage from income
union all
select deduction,percentage from deduction) b
) k
I have solved this using case -
select Employee, Basic, Allowance, Others, (Basic + Allowance + Others) as Total_Income,
Insurance, Health, House, (Basic + Allowance + Others) as total_Deduction,
(Basic + Allowance + Others -Insurance - Health - House) as net_pay
from(
select emp_name as Employee,
sum(case when trns_type = 'Basic' then amount else 0 end ) as Basic,
sum(case when trns_type = 'Allowance' then amount else 0 end ) as Allowance,
sum(case when trns_type = 'Others' then amount else 0 end ) as Others,
sum(case when trns_type = 'Insurance' then amount else 0 end ) as Insurance,
sum(case when trns_type = 'Health' then amount else 0 end ) as Health,
sum(case when trns_type = 'House' then amount else 0 end ) as House
from emp_transaction
group by employee ) as x
Hey, is the below query correct for the first expected output table: I have not applied case statement Select salary.Emp_id as Emp_id, salary.emp_name as Emp_name,
trns_type, base_salary*percentage/100
From Salary
Cross Join (Select id, income as Trns_type, percentage
from Income
Union
Select id, deduction as trns_type, percentage
From Deduction)
is this crosstab important from interview perspective?
select emp_id, emp_name, trns_type,round(base_salary*(cast(percentage as decimal)/100),2) as v
from salary
cross join (select income as trns_type, percentage from income
union
select deduction as trns_type, percentage from deduction) x by using this also iam getting same answers.is case compulsory
No case is not compulsory.
Why do you need a case statement, we can do directly base_salary*percentage/100??
exactly, why did I need the case statement. guess I am getting old..
It was unnecessary over here.
@@techTFQ sir😅
select emp_name,
max(case when trns_type='Basic' then amount end) as basic,
max(case when trns_type='Allowance' then amount end) as Allowance,
max(case when trns_type='Others' then amount end) as Others,
sum(case when trns_type in ('Basic','Allowance','Others') then amount end) as Gross,
max(case when trns_type='Insurance' then amount end) as Insurance,
max(case when trns_type='Health' then amount end) as Health,
max(case when trns_type='House' then amount end) as House,
sum(case when trns_type in ('Insurance','Health','House') then amount end) as Deductions,
(sum(case when trns_type in ('Basic','Allowance','Others') then amount end)-
sum(case when trns_type in ('Insurance','Health','House') then amount end)) as net
from emp_transaction
group by 1 order by 1
can u explain what does max do here ? and how u get rid of nulls
@@rohitsharma-mg7hd rows to column conversion
-- SOLUTION 3: DYNAMIC SQL
-- STEP 1: Generate column list dynamically
DECLARE @cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(trns_type), (','))
FROM (SELECT DISTINCT trns_type FROM emp_transaction) x;
PRINT @cols;
-- STEP 2: Build the dynamic SQL query
SET @sql = N' SELECT emp_name, ' + @cols +
N' FROM (
SELECT emp_id, emp_name, trns_type, amount
FROM emp_transaction
) AS SourceTable
PIVOT
(
MAX(amount)
FOR trns_type IN (' + @cols + N')
) AS pivot_table ';
-- Execute the dynamic SQL query
EXEC sp_executesql @sql;
with cte as
(select distinct s.emp_id,
s.emp_name,
d.deduction as trans_type,
s.base_salary*(d.percentage/100.0)as amount
from salary s,deduction d
union all
select distinct s.emp_id,
s.emp_name,
i.income as trans_type,
s.base_salary*(i.percentage/100.0)as amount
from salary s,income i
order by emp_id),
cte1 as
(select * from cte)
select emp_name,
max(case when trans_type='Basic' then amount end) as Basic,
max(case when trans_type='Allowance' then amount end) as Allowance,
max(case when trans_type='Health' then amount end) as Health,
max(case when trans_type='Others' then amount end) as Others,
max(case when trans_type='Insurance' then amount end) as Insurance
from cte1
group by emp_name
order by emp_name;
-- SOLUTION 2: Using MAX(), GROUP BY and CTE
WITH cte AS (
SELECT emp_name,
MAX(CASE WHEN trns_type = 'Basic' THEN amount END) AS Basic,
MAX(CASE WHEN trns_type = 'Allowance' THEN amount END) AS Allowance,
MAX(CASE WHEN trns_type = 'Others' THEN amount END) AS Others,
MAX(CASE WHEN trns_type = 'Insurance' THEN amount END) AS Insurance,
MAX(CASE WHEN trns_type = 'Health' THEN amount END) AS Health,
MAX(CASE WHEN trns_type = 'House' THEN amount END) AS House
FROM emp_transaction
GROUP BY emp_name
)
SELECT emp_name,
Basic, Allowance, Others,
Basic + Allowance + Others AS Gross,
Insurance, Health, House,
Insurance + Health + House AS Total_Deduction
FROM cte
I was trying to do the second objective on mysql....I am exhausted...Can you help me?
my oracle answer
--expected output1
SELECT emp_id, emp_name, trns_type,
base_salary*percentage*0.01 AS amount
FROM salary s,
(select income as trns_type, percentage from income
union
select deduction as trns_type, percentage from deduction) x
ORDER BY trns_type, emp_id, amount
;
--expected output2
SELECT emp_name,
sum(decode(trns_type, 'Basic', amount)) AS BASIC,
sum(decode(trns_type, 'Allowance', amount)) AS ALLOWANCE,
sum(decode(trns_type, 'Others', amount)) AS OTHERS,
SUM(CASE WHEN trns_type IN ('Basic', 'Allowance', 'Others')
THEN amount
END) AS GROSS,
sum(decode(trns_type, 'Insurance', amount)) AS INSURANCE,
sum(decode(trns_type, 'Health', amount)) AS HEALTH,
sum(decode(trns_type, 'House', amount)) AS HOUSE,
SUM(CASE WHEN trns_type IN ('Insurance', 'Health', 'House')
THEN amount
END) AS TOTAL_DEDUCTION
FROM emp_transaction
GROUP BY emp_name
ORDER BY emp_name
;
My query for populating employee_transaction:
Note:
uncomment "--WHERE.." and you will get to know how each row is interacting with others.
The outermost CTE 'my_final' is not mandatory. I've included it only for sake of ordering the rows as shown in the video.
WITH my_final AS (
WITH cte AS (
SELECT * FROM salary
-- WHERE emp_id = 1
)
SELECT emp_id,emp_name,income.income AS "TRANS_TYPE",((base_salary*percentage)/100)::int AS amount FROM cte
CROSS JOIN income
UNION
SELECT emp_id,emp_name,deduction.deduction AS "TRANS_TYPE",((base_salary*percentage)/100)::int AS amount
FROM cte
CROSS JOIN deduction)
SELECT *,ROW_NUMBER() OVER(PARTITION BY emp_id ORDER BY NULL) AS rn FROM my_final
Although I have delayed to participate in this challenge.
Solution in MySQL:
select emp_id, emp_name, TRANS_TYPE,
case
when TRANS_TYPE = "Basic" then base_salary
when TRANS_TYPE = "Allowance" then round((base_salary*4)/100 , 2)
when TRANS_TYPE = "Others" then round( (base_salary*6)/100, 2)
when TRANS_TYPE = "Insurance" then round( (base_salary*5)/100 , 2)
when TRANS_TYPE = "Health" then round( (base_salary*6)/100, 2)
when TRANS_TYPE = "House" then round( (base_salary*4)/100 , 2)
end as AMOUNT
from (
(select distinct s.emp_id, s.emp_name, s.base_salary, i.income as TRANS_TYPE, i.percentage
from Q5_salary s join Q5_income i )
union
(select s.emp_id, s.emp_name, s.base_salary, d.deduction as TRANS_TYPE, d.percentage
from Q5_deduction d cross join Q5_salary s )
) abc ;
with a as (
select emp_id, emp_name, income, base_salary/100*percentage amount
from salary
cross join
(select * from income
union
select * from deduction) t
)
select emp_name
,max(case when income = 'Basic' then amount end) Basic
,max(case when income = 'Allowance' then amount end) Allowance
,max(case when income = 'Others' then amount end) Others
,max(case when income = 'Basic' then amount end)
+max(case when income = 'Allowance' then amount end)
+max(case when income = 'Others' then amount end) Gross
,max(case when income = 'Insurance' then amount end) Insurance
,max(case when income = 'Health' then amount end) Health
,max(case when income = 'House' then amount end) House
,max(case when income = 'Insurance' then amount end)
+max(case when income = 'Health' then amount end)
+max(case when income = 'House' then amount end) total_deduction
from a
group by emp_name