Window frames control which rows contribute to a window calculation relative to the current row. Ranking tasks also require a precise definition of 'top N': a fixed number of rows, all ties at a cutoff, or the top N distinct values. These distinctions matter for correctness in interview reports and production analytics.
Key Takeaways at a Glance:
- •Window frames (ROWS vs RANGE) define the exact subset of rows within the partition that contribute to the calculation.
- •Frame bounds: UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING.
- •Top-N per group is the single most tested window pattern in placement interviews (e.g. top 2 highest paid per department).
- •Deterministic Top-N requires a unique tie-breaker column in the ORDER BY clause.
In Simple Words (The Mental Model)
A window partition defines the big neighborhood (e.g. your department). The window frame defines your immediate vision within that neighborhood—like looking at only the 2 people directly in front of you and 2 people behind you (a sliding frame).
Real-World Analogy
Think of looking out of a train window with moving shutters. As the train travels forward, the shutter moves along with you. At any moment, you can only see the 3 mile markers closest to your current position (a sliding window frame).