Explain the subtraction of row numbers in SQL.

In SQL, the ROW_NUMBER() window function assigns a unique sequential integer to rows within a partition, ordered by a specified column. Subtracting two ROW_NUMBER() values that are partitioned differently creates a constant value for consecutive rows that share a common attribute. For example, if you partition by user_id and order by event_date, ROW_NUMBER() gives 1,2,3,... for that user. If you also partition by user_id and event_type, ROW_NUMBER() resets for each event_type. The difference between these two row numbers remains the same for consecutive rows with the same event_type, effectively grouping them. This technique is often used to identify streaks or contiguous blocks of rows where a condition holds. It works because the difference captures the offset between the overall sequence and the sequence within the subgroup, which stays constant while the subgroup remains unchanged.