Lead

Lead

The LEAD() function saves you from needing to do a confusing, messy join to another table. It allows you to access the values in the rows after the row you are currently looking at. It makes comparison super easy.

In a grocery store, there are multiple different checkout lanes 1–10. Then, within each checkout lane are different customers. Whoever arrived first is first in line and whoever arrived last is last in line.

SELECT
   customer_name,
   LEAD(customer_name) OVER(PARTITION BY line_number ORDER BY arrived_at ASC) AS next_in_line
FROM grocery_customers

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'