I am very much new to SQL and have been practicing straight forward queries since a few days. I cam across this question which I would request help for. So there is a table with employee name, salary and salary date as columns. Date is simply Year from 2015 to 2019. I am trying to write a query where I can get employee name with the maximum % hike in salary from 2018 to 2019. I wrote the below query but stuck for hours at the same.
CREATE TABLE data (
salary INTEGER NOT NULL,
emp_name TEXT NOT NULL,
Sal_Date YEAR NOT NULL
);
INSERT INTO data VALUES (10000, 'Ryan', 2015);
INSERT INTO data VALUES (12000, 'Bryan', 2016);
INSERT INTO data VALUES (11000, 'Manthan', 2016);
INSERT INTO data VALUES (15000, 'Susan', 2017);
INSERT INTO data VALUES (16000, 'Alien', 2017);
INSERT INTO data VALUES (10000, 'Ryan', 2018);
INSERT INTO data VALUES (12000, 'Bryan', 2018);
INSERT INTO data VALUES (11000, 'Manthan', 2018);
INSERT INTO data VALUES (15000, 'Susan', 2018);
INSERT INTO data VALUES (16000, 'Alien', 2018);
INSERT INTO data VALUES (11000, 'Ryan', 2019);
INSERT INTO data VALUES (13000, 'Bryan', 2019);
INSERT INTO data VALUES (15000, 'Manthan', 2019);
INSERT INTO data VALUES (18000, 'Susan', 2019);
INSERT INTO data VALUES (32000, 'Alien', 2019);
SELECT salary from data
group by ;
I am getting no logic to solve this. Can anybody please help with the query and logic explanation. I'll be grateful. Thanks
CodePudding user response:
SELECT * , (salary - LAG(salary,1,salary) over (partition by emp_name order by sal_date)) /salary * 100 as promotionPercentage
from data
order by emp_name , sal_date
db<>fiddle here
CodePudding user response:
You can use a Self-join to join a table to itself and return the salary for the previous year, then order by the % change in salary and return the first one
SELECT TOP 1 t1.emp_name
FROM data t1
JOIN data t2 ON t1.emp_name = t2.emp_name AND t1.Sal_date = t2.Sal_Date 1
WHERE t1.Sal_Date = 2019
ORDER BY (t1.Sal_Date - t2.Sal_Date) / t2.Sal_Date * 100
Alternatively, you can use the LAG analytic function but I am not sure it is present in all DBMS? I am using MSSQL for this one
CodePudding user response:
You can self-join the table with the condition that:
- same
emp_name
- the first table is for 2018, the other one 2019
Then, you can compare the salary in 2018 and 2019 in the same row. Here is a query example. You may need to modify a bit depending on your SQL engine.
SELECT
d18.emp_name,
cast(d19.salary AS FLOAT) / d18.salary - 1 AS hike_18_to_19
FROM
data d18
INNER JOIN
data d19
ON
d18.emp_name = d19.emp_name
AND d18.Sal_Date = 2018
AND d19.Sal_Date = 2019
ORDER BY
hike_18_to_19 DESC
You get something like this:
emp_name hike_18_to_19
0 Alien 1.000000
1 Manthan 0.363636
2 Susan 0.200000
3 Ryan 0.100000
4 Bryan 0.083333