ROW_NUMBER
Assigns a unique, gapless sequence number to each row within a partition — the foundation of "top-N per group" queries.
Medium · Interview Practice
SQL Window Functions Medium Challenge
A real SQL interview round, built around ROW_NUMBER, RANK, DENSE_RANK, PARTITION BY, Running Totals, LAG and LEAD.
Challenges 1–55 pts each
Challenges 6–107 pts each
Challenges 11–158 pts each
Total100 points · Pass at 70
Before you begin
- You have exactly 30 minutes. The timer starts when you click "Start Challenge".
- The sandbox uses a real e-commerce schema:
categories,users,products,orders,order_items,payments. - Every challenge requires a specific window function — result matching alone is not enough, your query must actually use it.
- You can run queries to preview results. Answers are only graded on final submission.
- Passing unlocks a shareable certificate and a spot on the Window Functions Medium Challenge leaderboard.
You need to be logged in to take this challenge.
Learn
What Are SQL Window Functions?
Window functions compute a value across a set of rows related to the current row — a "window" — without collapsing those rows into a single output row the way GROUP BY does. Once you're comfortable with GROUP BY, HAVING, JOINs, and basic aggregates, window functions are the natural next step toward interview-ready SQL.
This challenge is built around a realistic e-commerce dataset — customers, orders, order items, products, categories, and payments — because that is the shape of data window-function questions are asked against at Amazon, Flipkart, Swiggy, Zomato, Razorpay, Deloitte, Accenture, TCS Digital, Infosys, and analytics consulting firms.
Why Data Analysts Need Window Functions
Nearly every analytics, BI, and data-engineering role eventually needs a "top customer per category," a "month-over-month growth," or a "running total" query — and window functions are the idiomatic, performant way to write them. Interviewers use these questions to separate candidates who memorized SELECT/JOIN/GROUP BY from candidates who can reason about partitions, frames, and ordering the way a production analytics pipeline requires.
ROW_NUMBER vs RANK vs DENSE_RANK
All three assign a position to each row within an ordered partition, but they disagree on ties. ROW_NUMBER() always hands out a unique, gapless number even when values tie. RANK() gives tied rows the same number but skips the next position (1, 2, 2, 4). DENSE_RANK() also gives tied rows the same number but never skips a position (1, 2, 2, 3). Picking the wrong one is one of the most common SQL interview mistakes.
Understanding PARTITION BY
PARTITION BY resets a window function at the start of each group — "rank products within each category," or "compare each order to the customer's previous order" — instead of ranking or comparing across the entire result set. It is to window functions what GROUP BY is to aggregates, except the underlying rows are never collapsed.
Common Window Functions Covered in This Challenge
RANK
Ranks rows within a partition, leaving gaps after ties (1, 2, 2, 4).
DENSE_RANK
Ranks rows within a partition without gaps after ties (1, 2, 2, 3).
PARTITION BY
Splits a result set into independent groups so a window function resets and re-runs within each group.
SUM() OVER()
Computes a running or cumulative total across ordered rows — the backbone of revenue and growth trend lines.
LAG
Looks back to a previous row in the same partition — the backbone of period-over-period comparisons.
LEAD
Looks ahead to a following row in the same partition — used for "what happens next" analysis.
NTILE
Splits ordered rows into N roughly equal buckets — used for percentile/quartile segmentation.
SQL Window Functions Interview Questions
Data analyst, BI analyst, analytics engineer, and product analyst interviews consistently include at least one window-function question — most often ranking within a group (ROW_NUMBER/RANK/DENSE_RANK) or a period-over-period comparison (LAG/LEAD). This challenge mirrors that format: 15 scenario-driven questions, each requiring a specific window function, evaluated against real query output — not multiple choice.
Passing at 70/100 unlocks a shareable certificate with your score and completion date, and every attempt is eligible for the Window Functions Medium Challenge leaderboard. Once you're consistently passing here, the Window Functions Hard Challenge is the natural next step.