PDE Preparing and Using Data for Analysis Practice Question

Answer analysis

Option-by-option breakdown

For each option: why learners choose it and why it is or isn't the right answer here.

• ✓

  LAG()

  Why this is correct

  LAG() is a navigation window function that returns a column's value from a preceding row within the same partition, evaluated by the ORDER BY timestamp. It satisfies the stem's requirement to retrieve the previous row's value, unlike LEAD(), which looks forward.

• ✗

  FIRST_VALUE()

  Why it's wrong here

  FIRST_VALUE() returns the value from the first row of the window frame, not the immediately preceding row. It is tempting because it is an analytic function sharing the OVER clause. FIRST_VALUE() is correct when the earliest value in each partition, such as a cohort's initial timestamp, is required.

• ✗

  ROW_NUMBER()

  Why it's wrong here

  ROW_NUMBER() assigns a sequential integer to each row within the partition; it returns a position, not a column value from another row. It is tempting because it also uses OVER(PARTITION BY... ORDER BY...). ROW_NUMBER() is correct for deduplication or ranking, not for accessing prior-row data.

• ✗

  LEAD()

  Why it's wrong here

  LEAD() returns the value from the following row within the window frame, so it looks forward rather than backward. It is tempting because it shares the same OVER(PARTITION BY... ORDER BY...) syntax as the required function. LEAD() would be correct when the next row's timestamp or value is needed.