SQL RANGE BETWEEN
Defines a frame by value, not row position — tied rows are treated as arriving together.
What & Why
A RANGE BETWEEN frame includes peers and rows whose ORDER BY value falls within the specified value interval. It creates a true calendar-time window even when some dates have no row or several rows share an ordered value.
See How It Works
BUSINESS QUESTION
Growth calculates DAU averages over the current date and prior six calendar days.
| date_day | dau | new_users | returning_users |
|---|---|---|---|
| 2024-04-01 | 1240 | 180 | 1060 |
| 2024-04-02 | 1315 | 205 | 1110 |
| 2024-04-03 | 1288 | 164 | 1124 |
| 2024-04-04 | 1392 | 221 | 1171 |
EXAMPLE QUERY
SELECT
date_day,
dau,
ROUND(AVG(dau) OVER (
ORDER BY date_day::timestamp
RANGE BETWEEN INTERVAL '6 days' PRECEDING AND CURRENT ROW
), 2) AS seven_day_avg
FROM growth.daily_active_users
ORDER BY date_day;RESULT — seven-day trailing average
| date_day | dau | seven_day_avg |
|---|---|---|
| 2024-04-01 | 1240 | 1240.00 |
| 2024-04-02 | 1315 | 1277.50 |
| 2024-04-03 | 1288 | 1281.00 |
| 2024-04-04 | 1392 | 1308.75 |
All four representative dates fall within six days of the current row.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario