SQL, data modeling, data quality, EDA, visualization · insight-0044
Densify dates before a ROWS-based rolling window, because "7 rows" is not "7 days" when days are missing
- Evidence type
- documented fact
- Difficulty
- medium
- Roles
- Data analyst, Data scientist, ML engineer
- Industries
- aviation, retail, energy
Summary: situation, action, reason, boundary
Situation
A 7-day rolling average of passengers uses AVG(...) OVER (PARTITION BY route ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) on a table with gaps.
Action
Cross join the entity list with a calendar table, left join the facts, and decide explicitly how missing days enter the window (NULL, zero or excluded with a count of days present). Report the number of observed days per window.
Reason
A ROWS frame counts physical rows, not calendar time, so its time span depends on data completeness.
Boundary
Rolling averages, lag features and moving sums in SQL or pandas on irregular series.
Symptom or risk
Around a gap, the window silently stretches over weeks, mixing seasons and hiding the outage in a smooth line.
Exceptions
Data guaranteed complete by a contract and a completeness check.
Trade-offs
Densified tables are larger; the alternative is time-based frames where the engine supports them.
Pitfalls
Filling gaps with zero for an outage, which drags the average down and looks like lost demand.
Example
Synthetic airline data: flight_day_raw has a 9-day IST-TZX gap (2023-03-04 to 2023-03-12) and about 0.5% random gaps.
Lessons and concepts
Claims and their sources
ROWS frames are defined by numbers of rows preceding or following the current row (for example n PRECEDING).