Window Functions - Business Analytics

1. What moving-average value is returned for the fourth week?
   Answer: The value is 36.67 because the current week and two preceding weeks are included. When you see a window function like ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, your job is to count exactly which rows fall inside that window for each position in the result set — not to average all available data. For the fourth week, the frame includes the current row (week 4: 40 units) plus the two preceding rows (week 3: 50 units, week 2: 20 units). That's three rows total, giving you: $\frac{20 + 50 + 40}{3} = \frac{110}{3} \approx 36.67$ This confirms B is correct. A is wrong because it assumes all four weeks are averaged: $\frac{10+20+50+40}{4} = 30$. But ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the window to at most three rows — week 1 is simply out of frame by week 4. C is wrong because it misreads the clause as 1 PRECEDING, not 2 PRECEDING. That would yield $\frac{50+40}{2} = 45$, but the actual frame stretches back two rows, not one. D is a distractor built on a misconception: a standard AVG() window function applies equal weights to every row in the frame. Weighted averages require explicit calculation logic (like SUM(weight * value) / SUM(weight)) — they aren't a default behavior of AVG() OVER (...). Study tip: Always sketch the window frame on paper. Write out the week numbers, identify the current row, count back the specified number of preceding rows, and average only those values. This prevents both the "include everything" trap (A) and the off-by-one trap (C).
2. Which expression should replace the ellipsis?
   Answer: SUM(sales) / SUM(SUM(sales)) OVER (PARTITION BY region) AS region_share When you need a store's share of its region's total, you're dealing with two levels of aggregation simultaneously — a classic use case for window functions applied to grouped results. The key insight is that SUM(sales) inside a GROUP BY query already represents per-store totals. To compute a ratio, you need to divide that store-level sum by the regional total, which requires a second pass over those already-aggregated values using SUM(SUM(sales)) — an outer window SUM wrapping the inner grouped SUM. Choice A correctly writes SUM(sales) / SUM(SUM(sales)) OVER (PARTITION BY region). The PARTITION BY region tells the window function to sum up all store-level totals within the same region, giving you the correct denominator. Dividing each store's sales by its region's total yields the regional share you need. Choice B uses OVER () with no partition, so the window function sums across all regions — you'd be computing each store's share of company-wide sales, not regional sales. Choice C partitions by store_id, which is meaningless here: each store appears only once in the grouped results, so the "window" collapses to just that store itself — effectively dividing by itself and returning 1 for every row. Choice D omits the window function entirely, dividing SUM(sales) by itself, which always returns 1 and provides no useful information. As a study tip, whenever you see share-of-group calculations in SQL, reach for SUM(...) OVER (PARTITION BY group_column) — and remember that inside a GROUP BY query, you must nest the aggregate: SUM(SUM(sales)).
3. Which result best describes the output of the query?
   Answer: Three rows: N1 and N2 each show region revenue of 100, while S1 shows region revenue of 25. When you see a window function with PARTITION BY in SQL, think of it as creating invisible subgroups within your result set — the aggregation happens within each partition, but crucially, all original rows are preserved in the output. Here, PARTITION BY region tells SQL to calculate SUM(revenue) separately for each region group. The North partition contains N1 (40) and N2 (60), so their regional sum is $40 + 60 = 100$. The South partition contains only S1 (25), so its regional sum is $25$. Because the query also selects transaction_id and region, every original transaction row appears in the output — each stamped with its partition's aggregate value. That gives you three rows: N1 → 100, N2 → 100, S1 → 25, confirming A is correct. B is wrong because it confuses a window function with GROUP BY. A GROUP BY query would collapse the North records into one row, producing only two rows total — but OVER (PARTITION BY region) never collapses rows; it annotates them. C is wrong because it misreads what the window function computes. The values 40, 60, and 25 are the individual revenues, not the regional sums. The query computes SUM, not a simple column reference. D is wrong because PARTITION BY region restricts the window to each region. A window function without any PARTITION BY clause would sum across all records, yielding 125 — but that's not what's written here. Study tip: Always distinguish PARTITION BY (window function — keeps all rows, aggregates within groups) from GROUP BY (collapses rows). That distinction appears frequently on analytics exams.
4. How will the accounts be distributed among the four buckets?
   Answer: The bucket sizes will be 3, 3, 2, and 2, with the highest-risk accounts assigned to bucket 1. When you encounter NTILE(n) questions, focus on two things: how SQL distributes remainders across buckets, and which end of the ordering gets bucket 1. NTILE(4) divides rows as evenly as possible across four buckets. With 10 accounts, divide: $10 \div 4 = 2 \text{ remainder } 2$. This means two buckets get an extra row, giving sizes of 3, 3, 2, and 2 — not four equal groups. SQL places the larger buckets first, so bucket 1 gets 3 rows, bucket 2 gets 3, bucket 3 gets 2, and bucket 4 gets 2. Because the ORDER BY risk_score DESC sorts highest scores first, the highest-risk accounts land in bucket 1. That makes B correct. A is wrong on two counts: it reverses both the bucket sizes (putting the smaller buckets first) and assigns the highest-risk accounts to bucket 4. In NTILE with ORDER BY DESC, bucket 1 always receives the top-ranked rows. C gets the bucket-1 assignment right (highest risk = bucket 1) but invents an alternating size pattern of 3, 2, 3, 2. SQL never distributes remainders this way — extra rows are always front-loaded into the earliest buckets consecutively. D reflects a common misconception: that NTILE requires perfectly divisible row counts. It never leaves rows unassigned; every row receives a bucket number. A handy rule to memorize: with NTILE(n) over r rows, the first $r \bmod n$ buckets each get $\lfloor r/n \rfloor + 1$ rows, and the rest get $\lfloor r/n \rfloor$. Pair that with your ORDER BY direction to nail bucket-1 assignment every time.
5. In transaction order, which running totals will the three records display?
   Answer: The records display 20, 100, and 100 because both May 2 records are peers in the default frame. When you see a window function question involving ORDER BY and the default frame, your first instinct should be to ask: what does "current row" mean when multiple rows share the same ORDER BY value? This is the heart of the question. SQL's default frame — RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — uses RANGE mode, not ROW mode. In RANGE mode, "current row" expands to include all peer rows: rows that share the same ORDER BY value. Here, both May 2 transactions have an identical transaction_date, making them peers. When the window function processes either May 2 record, it includes both May 2 rows in the frame. So the running total for the May 1 record is 20, and for both May 2 records it is 20 + 30 + 50 = 100. That gives you 20, 100, 100 — confirming answer C. Answer A is wrong because it assumes the frame is ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which processes each physical row independently. That's a different frame specification entirely. Answer B introduces a fictitious rule about processing the larger transaction first — SQL has no such ordering within peers. Answer D would only be correct if there were no ORDER BY clause at all; without ORDER BY, the default frame expands to the entire partition, but here ORDER BY is explicitly present. Your study tip: memorize the distinction between ROWS and RANGE framing. ROWS counts physical rows; RANGE groups peer rows together. On exam questions, spotting the default RANGE behavior with tied ORDER BY values is a classic trap designed to make you choose A instead of C.
6. Which expression correctly places the final status on every history row?
   Answer: LAST_VALUE(status) OVER (PARTITION BY ticket_id ORDER BY status_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) When working with window functions, the key concept to internalize is that frame specification controls which rows are included in the calculation. Most databases apply a default frame of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW whenever you include an ORDER BY clause — meaning the window only "sees" rows up to and including the current one. This is the trap this question is built around. LAST_VALUE returns the last value within whatever frame the window function can see. With the default frame, each row's "last" value is simply itself — which is exactly the broken behavior described in the passage. To fix this, you must explicitly expand the frame to include all rows in the partition. Option D does exactly that: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING tells the database to look at every row in the partition, so LAST_VALUE correctly returns the final status (Resolved) on every row. Option A is clever but unreliable — reversing the sort order and using FIRST_VALUE can work logically, but it introduces risk when statuses share the same timestamp or when ties exist, potentially returning an unpredictable row. Option B is precisely the broken expression the passage already identified; it suffers from the default frame problem. Option C uses MAX with a cumulative frame (UNBOUNDED PRECEDING only), so early rows see only earlier statuses — the "Open" row would return "Open," not "Resolved." As a study tip, whenever you see LAST_VALUE in an exam question, immediately ask yourself: what is the frame? If it isn't explicitly set to UNBOUNDED FOLLOWING, assume it's broken.
7. What values does this expression return for March and April, respectively?
   Answer: It returns 30 for March and -5 for April because LAG uses the preceding available row. When working with SQL window functions like LAG, the critical thing to understand is that the function operates on rows in the result set, not on logical time periods. It doesn't know or care that February is "missing" — it simply looks at the physically preceding row in the partition. Here's how to trace through this example. The dataset has three rows for this customer: January (100), March (130), April (125). When ordered by month, March's preceding row is January — there is no February row to skip over. So LAG returns 100 for March, giving $130 - 100 = 30$. For April, the preceding row is March (130), giving $125 - 130 = -5$. Answer A captures this exactly and is correct. Answer B is the most tempting distractor — it assumes LAG is "aware" of the calendar and returns null because February is absent. This is a common misconception. LAG has no concept of time gaps; it only sees rows. Answer C incorrectly assumes both March and April compare against January, as if LAG resets or anchors to the first row — that's not how it works; each row compares to its immediate predecessor. Answer D introduces a fictional behavior where missing periods "reset the partition," which has no basis in SQL window function logic. The study tip here: always mentally picture your window function operating on a sorted list of rows, not a calendar. If you want gap-aware calculations, you need explicit date arithmetic or a date spine — LAG alone won't flag missing periods.
8. What pair of values—RANK, then DENSE_RANK—is assigned to the representative with a score of 80?
   Answer: The representative receives the pair 3, 2 because only standard ranking leaves a gap after the tie. Whenever you see a question comparing RANK() and DENSE_RANK(), focus on one key distinction: how each function handles the positions after a tie. With scores of 90, 90, 80, and 70 ordered highest to lowest, both functions agree that the two 90s share the top position and both receive rank 1. The disagreement starts at the next record. RANK() counts how many rows came before the current row and adds 1, so the score of 80 sits in position 3 (two rows precede it), receiving a RANK of 3. DENSE_RANK(), by contrast, counts how many distinct rank values came before and adds 1 — only one distinct rank (rank 1) precedes 80, so it receives a DENSE_RANK of 2. That gives the score of 80 the pair 3, 2, confirming D is correct. A is wrong because it assigns 2, 2 — this misapplies both functions identically and ignores the gap that RANK() creates after a tie. B reverses the logic entirely: 2, 3 would mean DENSE_RANK is larger than RANK, which is never possible since dense ranking never skips numbers. C assigns 3, 3, which gets RANK right but incorrectly applies the same gap-skipping logic to DENSE_RANK, which specifically exists to avoid gaps. A reliable memory trick: Dense = no gaps, Standard = gaps allowed. After a two-way tie at rank 1, RANK() jumps to 3, while DENSE_RANK() moves to 2. The dense rank is always ≤ the standard rank — never greater.
9. Which window specification should be used with SUM(daily_conversions)?
   Answer: OVER (PARTITION BY variant ORDER BY test_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) When working with window functions, you need to ask two questions: what group should the running total reset for? and in what order should rows accumulate? The answer to the first question becomes your PARTITION BY clause; the answer to the second becomes your ORDER BY clause. Here, the requirement is a cumulative count that restarts for each variant and advances chronologically. That maps directly to PARTITION BY variant ORDER BY test_date, with an explicit frame of ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW to sum from the first row of each partition up to the current row. This is exactly what B provides — a running total per variant, in date order, starting fresh at zero when a new variant begins. A is missing the PARTITION BY variant clause entirely. Without it, SQL treats the entire dataset as one partition, so the running total never resets — it accumulates across all variants together, producing one continuous sum rather than separate ones per variant. C partitions by test_date and orders by variant, which inverts the logic entirely. Partitioning by date means the window resets for every date, and ordering by variant name has no chronological meaning. This would produce nonsensical results for a time-series cumulative sum. D partitions correctly by variant but uses ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING with no ORDER BY. This gives every row the total sum for its variant rather than a running cumulative — it's a grand total, not a progressive accumulation. A useful rule of thumb: "partition = reset boundary, order = accumulation direction." Whenever a question asks for a running total that restarts per group, you need both clauses working together as in B.
10. Which query correctly returns at most two variants per campaign?
    Answer: WITH ranked AS (SELECT results.*, ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY conversion_rate DESC, variant_id ASC) AS rn FROM results) SELECT * FROM ranked WHERE rn <= 2; When you need to rank rows within groups and filter by that rank, the standard pattern is: compute the rank in a subquery or CTE, then filter in an outer query. This question tests whether you know why that two-step structure is required. Option C does exactly this. The CTE ranked uses ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY conversion_rate DESC, variant_id ASC) to assign each variant a rank within its campaign — higher conversion rates rank first, with ties broken alphabetically by variant_id. The outer SELECT then filters WHERE rn <= 2, returning at most two variants per campaign. This is the correct, portable pattern when QUALIFY is unavailable. Option A is the most tempting trap. It looks almost identical to C but tries to use ROW_NUMBER() directly in a WHERE clause. This is illegal in SQL — window functions cannot appear in WHERE because filtering happens before window functions are evaluated in the logical query order. Most databases will throw a syntax or evaluation error here. Option B is simply wrong for the task. LIMIT 2 returns only two rows from the entire table, not two per campaign. It ignores the partitioning requirement entirely. Option D misuses HAVING. HAVING filters aggregated groups, and placing a window function inside HAVING is not standard SQL. Even if a database allowed it, the query lacks any partitioning by campaign_id, so it still wouldn't return two variants per campaign. Your study takeaway: whenever you see "top N per group," immediately think CTE (or subquery) → window function → outer filter. That two-step structure is the reliable, portable solution.

Start this quiz