Question 100
A Snowflake platform team receives the following requirement: An engineer writes a WHERE condition against a ROW_NUMBER window alias and receives a SQL compilation error. What is the principal technical cause in this scenario?
Explanation
Correct Answer
The described symptoms indicate that WHERE cannot directly filter results of a window function at the same query level. The appropriate resolution is: Use QUALIFY or an outer query to filter the windowed row number. Confirm with: The query returns the intended top row per group without a window-in-WHERE error.
Incorrect Answers
The SQL window frame does not match the intended bounded moving calculation. “Specify the intended ROWS frame and verify chronological order for the window aggregate” would address that alternative cause, not the condition in this question: wHERE cannot directly filter results of a window function at the same query level.
“Partition the running-total window by account and order by the correct posting sequence” responds to this separate finding: the window partitioning key does not represent the required account-level group. This question instead needs “Use QUALIFY or an outer query to filter the windowed row number”, so the alternative does not meet the stated condition.
The report lacks a partitioned ranking rule for choosing one row per entity. “Use ROW_NUMBER over subscriber partitions ordered by update time, then filter to rank one” applies to that separate problem. The observed case is an engineer writes a WHERE condition against a ROW_NUMBER window alias and receives a SQL compilation error; choosing this alternative would address the wrong failure mode.