LEAD & LAG functions
Lesson 6 of 13 in Coddy's SQL for advanced course.
The Lead and Lag functions allow us to the value of the current row by n steps back of n steps ahead
For example, if we want to calculate the ratio of a company for the current row and one months ago we extract the value two months ago:
| id | revenue | month |
| 1 | 532 | 5 |
| 2 | 492 | 6 |
| 3 | 393 | 7 |
| 4 | 723 | 8 |
SELECT id, revenue, LAG(month, 1) OVER (ORDER BY MONTH) as prev_month_revenue
FROM table1 ORDER BY idThis will create the following table:
| id | revenue | prev_month_revenue |
| 1 | 532 | |
| 2 | 492 | 532 |
| 3 | 393 | 492 |
| 4 | 723 | 723 |
This way we can calculate the prev_month_revenue/revenue ratio.
If we instead used the LEAD function would take the next month of each row:
| id | revenue | next_month_revenue |
| 1 | 532 | 492 |
| 2 | 492 | 393 |
| 3 | 393 | 723 |
| 4 | 723 |
Challenge
EasyAir conditioners can get very expensive. Over time, they have become more efficient and stronger, but the significance of these improvements is not seen day by day. We need to zoom out to see the improvements over time.
To do this, Calculate the average efficiency and strength of air conditioners for each id and month. Then fetch the ratio between efficiency and strength for the current month and two months before it. Finally, Calculate the ratio between the current month's ratio and the ratio of the two months before it. Return the results where the ratio is greater than 1.5. Name this column ratio_two_months.
Try it yourself
All lessons in SQL for advanced
4Summary
Final challenge #12Window Functions part 1
ROW_NUMBER functionORDER BY criterionPARTITION BY criterionLEAD & LAG functionsRecap challenge #1Recap challenge #23Window Functions part 2
RANK & DENSE_RANK functionsNTILE functionAggregation functionsROWS & RANGE criterion