9 min read
SQL Window Frames: ROWS vs RANGE
Learn how ROWS and RANGE window frames change results, avoid hidden pitfalls, and build correct moving calculations with clear, runnable examples.
Read articleBest For
Reading Order
You do not have to read these in order, but this sequence is the fastest path from performance fundamentals into real query-tuning decisions.
Start with the idea of turning one long query into readable, named steps.
Then extend that mental model into recursion for hierarchies, sequences, and graph-like traversal.
Move into ranking, running totals, and partition-level analytics while preserving every row.
Deepen the window-function model by learning the frame rules that cause many subtle reporting bugs.
Use CASE inside aggregates to build denser analytical queries and dashboard-style outputs.
Finish with the CASE patterns that underpin classification, branching logic, and many reporting queries.
Practice Challenges
Topic hubs are better for structured reading. Challenge pages are better when you want to switch from reading into hands-on practice without leaving the concept area entirely.
Find products that never appear in the order table without being fooled by missing or duplicate order rows.
Open challengeFind the second distinct highest salary without accidentally returning the same maximum twice.
Open challengeFind values that appear in at least three consecutive rows instead of merely appearing three times anywhere in the table.
Open challengeCompare each weather row with the previous day and return only the IDs where temperature increased day over day.
Open challengeMatch patients whose condition list contains a Type I diabetes code without being fooled by partial code overlaps.
Open challengeAssign tied scores the same rank while keeping the next rank consecutive with no gaps.
Open challengeSwap every pair of adjacent seat IDs while leaving the final row untouched if the seat count is odd.
Open challengeCompute daily cancellation rate after excluding any trip where either the client or driver is banned.
Open challengeCalculate contest participation rate by dividing each contest’s registrations by the total user base, not by contest-local counts.
Open challengeRoll monthly transaction reporting into one query while splitting total volume from approved volume by country.
Open challengeMeasure what share of customers got an immediate delivery on their first-ever order instead of across all orders.
Open challengeCompute next-day retention by checking whether each player returned on the day after their first login.
Open challengeSum 2016 investment value only for policyholders who meet one duplicate condition and one uniqueness condition at the same time.
Open challengeUse a running total to find the last passenger whose cumulative weight still stays under the bus limit.
Open challengeReconstruct each product’s price as of a snapshot date, including products that had not changed yet and still use the default price.
Open challengeCalculate per-stock capital gain or loss by treating buys as cash outflows and sells as cash inflows before summing them.
Open challengeCalculate overall friend request acceptance rate while separating request volume from accepted pairs cleanly.
Open challengeClassify each tree node as root, inner, or leaf based on whether it has a parent and whether it is referenced by children.
Open challengeReturn only the customers whose distinct purchased product set covers every product listed in the catalog.
Open challengeLearn how ROWS and RANGE window frames change results, avoid hidden pitfalls, and build correct moving calculations with clear, runnable examples.
Read articleTurn multiple queries into one with conditional aggregation. Learn how CASE WHEN inside aggregate functions creates powerful single-query reports and pivot tables.
Read articleUnlock the power of WITH RECURSIVE. Learn how to traverse hierarchies, generate continuous dates, and solve graph problems directly in SQL.
Read articleLearn how to add conditional logic to your SQL queries using CASE statements. Transform data and categorize results with ease.
Read articleStop writing nested subquery nightmares. Learn how to use Common Table Expressions (CTEs) to make your SQL readable, modular, and powerful.
Read articleMaster ROW_NUMBER, RANK, and running totals with interactive visualizations. See exactly how window functions process your data row by row.
Read article