SQL Window Functions
I just started learning about window functions, and I found this great example of an interview question where a possible solution is a window function.
The problem is that you have the profits from several companies, and you want to return the company whose profit increased every year across the table. So even if the profit decreased from one year to the next, you don’t return that company.
In the video (not my idea, it is by techTFQ!), he achieves this with the “lead” window function, which takes the value of the next year and checks whether it is larger than the current year, and if so, returns 1, otherwise 0. The only issue is the last row in the table for each company, since there is nothing to compare the final year to. He defaults that case to be 1 by entering more parameters in the lead function, as in lead(amount, 1, amount+1). What this does is check the next amount, offset by 1, and then if there is no next amount, it compares it to amount + 1, which will always be larger than the current amount. I thought this was pretty clever.
However, since the desired output is not a table of 1’s and 0’s, but rather the list of companies whose profit increased each year, he returns the brands that are in a select statement which chooses the brands where the values are all 1.
Somehow, writing out my own interpretation of the window function helps me to understand it better. I am excited to try to use a window function to solve a leetcode problem soon!
