The short answer
ROWS works with physical rows.
RANGE uses values in the ORDER BY expression, so peer rows with the same ordering value can land in the same frame.
For example:
ROWS BETWEEN 2 PRECEDING AND CURRENT ROWmeans:
> Current row + previous two physical rows.
Handy for a rolling 3-row calculation.
Example:
AVG(sales) OVER (
ORDER BY date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)