Business Analytics Quiz: Window Functions
10 questions · exam conditions
0:00
Window FunctionsQuestion 1 of 10

A transaction dataset contains three records: transaction N1 is in the North region with revenue of 40, transaction N2 is in the North region with revenue of 60, and transaction S1 is in the South region with revenue of 25. An analyst runs: SELECT transaction_id, region, SUM(revenue) OVER (PARTITION BY region) AS region_revenue FROM transactions;

Which result best describes the output of the query?

Three rows: N1 and N2 each show region revenue of 100, while S1 shows region revenue of 25.
Two rows: North shows region revenue of 100, while South shows region revenue of 25.
Three rows: N1 shows 40, N2 shows 60, and S1 shows 25 as their region revenues.
One row shows total revenue of 125 because the window includes all records in the dataset.
← Back to quizzes

Business Analytics Quiz

Business Analytics Quiz: Window Functions

Practice Window Functions in Business Analytics with focused quiz questions that help you check what you know, review explanations, and build confidence with test-style prompts.

What this quiz covers

This quiz focuses on Window Functions, giving you a quick way to practice the rules, question types, and explanations that matter most for Business Analytics.

How to use this quiz

Try each quiz question before looking at the correct answer. Use the explanations to review missed ideas, then come back to similar questions until the pattern feels familiar.

All questions

Question 1

A transaction dataset contains three records: transaction N1 is in the North region with revenue of 40, transaction N2 is in the North region with revenue of 60, and transaction S1 is in the South region with revenue of 25. An analyst runs: SELECT transaction_id, region, SUM(revenue) OVER (PARTITION BY region) AS region_revenue FROM transactions;

Which result best describes the output of the query?

  1. Three rows: N1 and N2 each show region revenue of 100, while S1 shows region revenue of 25. (correct answer)
  2. Two rows: North shows region revenue of 100, while South shows region revenue of 25.
  3. Three rows: N1 shows 40, N2 shows 60, and S1 shows 25 as their region revenues.
  4. One row shows total revenue of 125 because the window includes all records in the dataset.
Explanation: 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=10040 + 60 = 100. The South partition contains only S1 (25), so its regional sum is 2525. 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.

Question 2

One customer has transactions of 20 on May 1 and transactions of 30 and 50 on May 2. The database uses the standard default ordered frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The analyst runs: SUM(amount) OVER (PARTITION BY customer_id ORDER BY transaction_date).

In transaction order, which running totals will the three records display?

  1. The records display 20, 50, and 100 because each physical row is added separately.
  2. The records display 20, 70, and 100 because the larger May 2 transaction is processed first.
  3. The records display 20, 100, and 100 because both May 2 records are peers in the default frame. (correct answer)
  4. The records display 100, 100, and 100 because an ordered window always includes the entire partition.
Explanation: 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.

Question 3

Weekly demand for an item is 10, 20, 50, and 40 units over four consecutive weeks. An inventory analyst calculates AVG(demand) OVER (ORDER BY week ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).

What moving-average value is returned for the fourth week?

  1. The value is 30.00 because all four observed weeks are included in the average.
  2. The value is 36.67 because the current week and two preceding weeks are included. (correct answer)
  3. The value is 45.00 because only the current week and immediately preceding week are included.
  4. The value is 40.00 because the current row is weighted more heavily than preceding rows.
Explanation: 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: 20+50+403=1103≈36.67\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: 10+20+50+404=30\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 50+402=45\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).

Question 4

A database supports common table expressions but does not support QUALIFY. A marketing analyst needs the two variants with the highest conversion rates within each campaign. Ties must be resolved deterministically by placing the alphabetically earlier variant_id first.

Which query correctly returns at most two variants per campaign?

  1. SELECT * FROM results WHERE ROW_NUMBER() OVER (PARTITION BY campaign_id ORDER BY conversion_rate DESC, variant_id ASC) <= 2;
  2. SELECT * FROM results ORDER BY conversion_rate DESC, variant_id ASC LIMIT 2;
  3. 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; (correct answer)
  4. SELECT campaign_id, variant_id, conversion_rate FROM results GROUP BY campaign_id, variant_id, conversion_rate HAVING ROW_NUMBER() OVER (ORDER BY conversion_rate DESC) <= 2;
Explanation: 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.

Question 5

A service ticket has three chronological status records: Open, Investigating, and Resolved. An analyst wants every status-history row to display the ticket's final status. The existing expression LAST_VALUE(status) OVER (PARTITION BY ticket_id ORDER BY status_time) displays the current row's status in a database whose default ordered frame ends at the current row.

Which expression correctly places the final status on every history row?

  1. FIRST_VALUE(status) OVER (PARTITION BY ticket_id ORDER BY status_time DESC)
  2. LAST_VALUE(status) OVER (PARTITION BY ticket_id ORDER BY status_time)
  3. MAX(status) OVER (PARTITION BY ticket_id ORDER BY status_time ROWS UNBOUNDED PRECEDING)
  4. LAST_VALUE(status) OVER (PARTITION BY ticket_id ORDER BY status_time ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) (correct answer)
Explanation: 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.

Question 6

A predictive model assigns ten customer accounts unique churn-risk scores. An analyst applies NTILE(4) OVER (ORDER BY risk_score DESC) so that the retention team can prioritize the highest-risk bucket.

How will the accounts be distributed among the four buckets?

  1. The bucket sizes will be 2, 2, 3, and 3, with the highest-risk accounts assigned to bucket 4.
  2. The bucket sizes will be 3, 3, 2, and 2, with the highest-risk accounts assigned to bucket 1. (correct answer)
  3. The bucket sizes will be 3, 2, 3, and 2, with the highest-risk accounts assigned to bucket 1.
  4. The bucket sizes will be 2, 2, 2, and 2, with the two remaining accounts left unassigned.
Explanation: 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÷4=2 remainder 210 \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 mod nr \bmod n buckets each get ⌊r/n⌋+1\lfloor r/n \rfloor + 1 rows, and the rest get ⌊r/n⌋\lfloor r/n \rfloor. Pair that with your ORDER BY direction to nail bucket-1 assignment every time.

Question 7

An analyst groups transaction data by region and store_id. The query begins SELECT region, store_id, SUM(sales) AS store_sales, ... FROM transactions GROUP BY region, store_id. The analyst wants the final expression to show each store's sales as a share of total sales for its region. Assume the database performs non-integer division.

Which expression should replace the ellipsis?

  1. SUM(sales) / SUM(SUM(sales)) OVER (PARTITION BY region) AS region_share (correct answer)
  2. SUM(sales) / SUM(SUM(sales)) OVER () AS region_share
  3. SUM(sales) / SUM(SUM(sales)) OVER (PARTITION BY store_id) AS region_share
  4. SUM(sales) / SUM(sales) AS region_share
Explanation: 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)).

Question 8

Four sales representatives have quarterly sales scores of 90, 90, 80, and 70. Scores are ordered from highest to lowest. An analyst calculates both RANK() and DENSE_RANK() using this ordering.

What pair of values—RANK, then DENSE_RANK—is assigned to the representative with a score of 80?

  1. The representative receives the pair 2, 2 because the two higher scores are tied.
  2. The representative receives the pair 2, 3 because dense ranking preserves the skipped position.
  3. The representative receives the pair 3, 3 because both functions count the two higher records.
  4. The representative receives the pair 3, 2 because only standard ranking leaves a gap after the tie. (correct answer)
Explanation: 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.

Question 9

An A/B test dataset has one record per test variant per day. The fields are variant, test_date, and daily_conversions. An analyst needs a cumulative conversion count that begins again at zero for each variant and advances in chronological order.

Which window specification should be used with SUM(daily_conversions)?

  1. OVER (ORDER BY test_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  2. OVER (PARTITION BY variant ORDER BY test_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) (correct answer)
  3. OVER (PARTITION BY test_date ORDER BY variant ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
  4. OVER (PARTITION BY variant ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
Explanation: 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.

Question 10

A subscription business has monthly recurring revenue records for one customer: 100 in January, 130 in March, and 125 in April. There is no February record. The analyst calculates monthly_revenue - LAG(monthly_revenue) OVER (PARTITION BY customer_id ORDER BY month).

What values does this expression return for March and April, respectively?

  1. It returns 30 for March and -5 for April because LAG uses the preceding available row. (correct answer)
  2. It returns null for March and -5 for April because February has no stored record.
  3. It returns 30 for March and 25 for April because each value is compared with January.
  4. It returns null for March and 125 for April because missing periods reset the window partition.
Explanation: 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=30130 - 100 = 30. For April, the preceding row is March (130), giving 125−130=−5125 - 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.