#4 IN SQL INTERVIEW QUESTION SERIES

Hey there !!

In my recent interview for product analyst position, I was asked lots of SQL question based on Window functions i.e., row_number(), rank(), dense_rank(), lead(), lag(), first_value(), last_value() etc. So, in today’s article I will be solving one question based on the use of lag() function and in next 3–4 articles I would be writing other questions as well. So, Lets jump directly in today’s problem statement.

Problem Statement: We have data of employees with their salaries for 3 consecutive years. Find YoY increase in salaries for each employees in percentage value.

Data: I am using a table basic_pays having total 120 records, with columns employee_id, fiscal_year, salary and running my queries on PostgreSQL. Below is snippet of Select * query from the the table.

When we say YoY increase it means, How much current year salary got increased with respect to previous year salary. Below is formula for calculating the same.

Now, for accessing the previous record in current row, we have to use lag() window function. Lag() function provides access to previous row data for specified physical offset to the current row. Syntax is ,

LAG( expression, offset, default) Over(PARTITION BY ……… ORDER BY......)

Expression: Value which we want to return
Offset: The number of rows back from current row, which we want to access. By default, it is 1.
dafault: When Preceding row is not defined, deafult will be returned. If not mentioned, NULL will be returned for the first row, since they don't have any preceeding rows.

Lets implement that in SQL,

In this Code, first partition by is diving the data into employees groups.

Order by clause is sorting this data in ascending order of fiscal year.

Lag() function is retrieving the previous year salary in current row in prev_salary column.

Note: Since default value is not mentioned, and the first row of each employee have no preceding row, NULL is returned in first row for each employee. Also by default, offset is 1.

Lets assume that for first year, previous salary was same as salary and there is no increase in salary i.e., 0% increase. So, populating salary in default value,

Notice that for first row, salary is same as prev_salary.

Now for finding YoY, Use a Common table expression (CTE) as salary_data and perform our calculation,

But per_increase with so many zero doesn’t seems good, so I would round it off & also concatenate that with %ge value so that it is visible that its a percentage increase.

That was all about using lag() window function for finding the YoY increase in salary. In same way, we can solve many questions if we are clear with concepts of usage of these functions.

Hope it will help you somewhere.

Keep learning, Keep practicing !!