Lag

Lag

Opposite from the LEAD() function, LAG() allows you to compare your current row to the rows before it, rather than those after it. It is still used for comparison, it just serves a bit of a different purpose. Similarly, with LEAD(), you choose the column you wish to output from the rows before your current row and partition based on how you wish to separate out your columns. Then, the most important part is ORDER BY. This will determine whether you’re getting the value you want or not. If you choose ASC or DESC values, that can change your whole query. Just be sure it fits the context of your problem and what you’re trying to solve.

SELECT
   customer_name,
   LAG(Name) OVER(PARTITION BY line_number ORDER BY arrived_at ASC) AS ahead_in_line
FROM grocery_customers

IIF

It’s essentially a simpler CASE statement. The IIF() function tests a condition and returns a specified value if the condition is TRUE and another specified value if the condition is FALSE.

in essence it acts like the excel IF function

SELECT
  iif(1=1,'TRUE','FALSE') AS 'T'
, iif(1=0,'TRUE','FALSE') AS 'F'