Percentage change in salary using BigQuery-CodePudding

Using bigquery, I have to calculate the percentage change of salary from the year 2020 to 2021. I have dummy employment data. I am sharing only a few rows of it for simplicity.

Highlighted column is the required column and I want the result to be in this form only.

Formula = (salary_2020 - salary_2021) / salary_2020 )* 100

    My query :

    with a_2020(
    SELECT * FROM `xx.salary.Sal_2020_2021` 
    where Year = 2020),
    
    b_2021 as (SELECT * FROM `xx.salary.Sal_2020_2021` 
    where  Year = 2021)
    
    select * , (a_2020.Salary - b_2021 .Salary) / a_2020.Salary )* 100 from b_2021
     join a_2020  
using (Emp_ID ,Address,Name, State)

Problem : 1)I am getting all the fields twice. 2) In the result, I get only 2021 year. Not 2020

CodePudding user response：

Join on Emp_ID and do not include duplicates values for year. Assuming that year column has only 2020 and 2021 values, this code might work.

SELECT 
    E1.Emp_ID AS Emp_ID,
    E1.Name AS Name,
    E1.Experience AS Experience,
    E1.Year AS Year,
    E1.Salary AS Salary,
    E1.State AS State, 
    E1.Address AS Address,
    CASE WHEN E1.Year=2021 AND E2.Year=2020 THEN ((E1.Salary-E2.Salary)/E2.Salary)*100 ELSE NULL END AS "Percent Increase in Salary"
FROM `xx.salary.Sal_2020_2021` E1 JOIN `xx.salary.Sal_2020_2021` E2 ON E1.Emp_ID=E2.Emp_ID AND E1.Year <> E2.Year

CodePudding user response：

You might consider below query using a window function.

SELECT Emp_ID, Year, Salary,
       IF(Year = 2021,
          ROUND((Salary / LAG(Salary) OVER w - 1) * 100, 2), NULL
       ) AS increase
  FROM `xx.salary.Sal_2020_2021`
WINDOW w AS (PARTITION BY Emp_ID ORDER BY Year);

 -------- ------ -------- ---------- 
| Emp_ID | Year | Salary | increase |
 -------- ------ -------- ---------- 
|  10003 | 2020 |  15000 |          |
|  10003 | 2021 |  16700 |    11.33 |
|  10007 | 2021 |   9000 |          |
|  10012 | 2020 |  10000 |          |
|  10012 | 2021 |  13500 |     35.0 |
|  10018 | 2021 |  19000 |          |
|  10041 | 2021 |  20000 |          |
|  10041 | 2021 |  21000 |      5.0 |
 -------- ------ -------- ----------