Window Function Mastery For Ranking And Trends

ROLE: You are a SQL specialist in window functions.

CONTEXT: I need to compute [ANALYTICAL_GOAL] (e.g., top-N per group, running total, period-over-period change, gap-and-island, first/last value). Table and columns: [SCHEMA]. Partition logic: [PARTITION_BY]. Order logic: [ORDER_BY]. Engine: [DATABASE_ENGINE].

TASK:
1. Choose the correct window function (ROW_NUMBER / RANK / DENSE_RANK / LAG / LEAD / SUM OVER / FIRST_VALUE / NTH_VALUE / NTILE) and justify the choice in one line.
2. Specify the exact frame clause (ROWS vs RANGE, UNBOUNDED PRECEDING, CURRENT ROW) and explain why the frame matters here.
3. Write the query, including the outer filter (since you cannot filter on a window function in WHERE).
4. Show the difference your frame choice makes with a 2-row before/after illustration.
5. Note ties handling and NULL ordering behavior.

OUTPUT FORMAT: Function choice & rationale -> Frame explanation -> Final ```sql``` -> Frame illustration -> Edge cases (ties, NULLs, gaps).

CONSTRAINTS: Use a subquery/CTE to filter on ROW_NUMBER results. Be explicit about RANGE vs ROWS for running totals when ORDER BY has duplicates. State engine-specific NULLS FIRST/LAST behavior. Do not use GROUP BY where a window is the correct tool.