Transforming Tabular Data - Business Analytics

1. Which representatives are retained?
   Answer: A, C, D, E, and F When working with window functions like dense rank, two concepts are critical: (1) filtering happens before ranking, and (2) dense rank assigns the same rank to ties, with no gaps in the sequence. Start by applying the active filter. B is immediately eliminated — inactive representatives never enter the analysis, regardless of their sales figures. That leaves A ($100$), C ($90$), and G ($80$) in the East, and D ($120$), E ($110$), and F ($110$) in the West. Now rank within each region using descending dense rank (highest sales = rank 1). In the East: A gets rank $1$, C gets rank $2$, G gets rank $3$. Retaining ranks $\leq 2$ keeps A and C. In the West: D gets rank $1$, and since E and F are tied, both receive rank $2$ — this is the defining behavior of dense rank. Retaining ranks $\leq 2$ keeps D, E, and F. The final retained set is A, C, D, E, and F, confirming answer C. Choice A drops F, incorrectly treating the E–F tie as if only one representative can hold rank $2$ — a confusion with ROW_NUMBER behavior. Choice B includes B, forgetting that the active filter removes inactive reps before any ranking occurs. Choice D drops C, which would only make sense if the rank cutoff were $1$ rather than $2$. Study tip: Always trace the order of operations — filter, then rank, then retain. And remember that dense rank never skips a rank number, so ties expand the retained set rather than eliminating anyone.
2. After the filter and transformation, which result is correct?
   Answer: Three rows remain, with total net revenue of $198$. When a transformation pipeline applies a filter before a calculation, you must handle each step in order — skipping the filter or misapplying the business rule will send you to a wrong answer. First, apply the quantity filter (≥ 10): R1 (12 ✓), R2 (9 ✗), R3 (15 ✓), R4 (10 ✓). R2 is eliminated, leaving three rows. Next, apply the net revenue rule. Returned transactions get $\$0$; all others use $quantity \times price \times (1 - discount)$: - R1: $12 \times 10 \times (1 - 0.10) = 12 \times 10 \times 0.90 = 108$ - R3: returned → $0$ - R4: $10 \times 12 \times (1 - 0.25) = 10 \times 12 \times 0.75 = 90$ Total net revenue: $108 + 0 + 90 = \$198$. That confirms answer A. Answer B is wrong on both counts — it keeps all four rows (ignoring the filter on R2) and calculates revenue incorrectly. Answer C keeps the correct three rows but inflates the total to $\$318$, likely by including R3's full revenue ($15 \times 8 = 120$) instead of zeroing it out for the return. Answer D correctly totals $\$198$ but drops to only two rows, probably by also filtering out R3 due to its returned status rather than understanding that returned records are retained but assigned zero revenue. The key study tip: in multi-step transformations, filters remove rows, but business rules transform values within retained rows — a "returned" flag zeroes the revenue; it does not eliminate the record.
3. Which events and derived business dates appear in the output?
   Answer: E1 dated June 30 and E2 dated July 31 When a query applies a time-window filter and then derives a new column, you must evaluate each step separately — first check which events survive the filter, then apply the transformation only to survivors. The filter keeps timestamps where $\text{July 1, 00:00 UTC} \leq t < \text{August 1, 00:00 UTC}$. E1 (July 1 at 2:00 a.m. UTC) satisfies this — it's on or after the lower bound and before the upper bound. E2 (July 31 at 11:00 p.m. UTC) also satisfies it. E3 (August 1 at 12:00 a.m. UTC) fails because the upper bound is strict — exactly midnight August 1 is excluded. E4 (June 30 at 11:00 p.m. UTC) fails because it's before the lower bound. So only E1 and E2 pass. Now subtract four hours to create business_date. E1's timestamp (July 1 at 2:00 a.m. UTC) minus four hours = June 30 at 10:00 p.m., which falls on June 30. E2's timestamp (July 31 at 11:00 p.m. UTC) minus four hours = July 31 at 7:00 p.m., which stays on July 31. That gives you E1 dated June 30 and E2 dated July 31 — confirming D. Choice A incorrectly includes E4, which was excluded by the filter. Choice B incorrectly includes E3, which fails the strict upper-bound check, and drops E1. Choice C keeps E1 and E2 correctly but assigns E1 the wrong date (July 1 instead of June 30), showing the four-hour subtraction was not applied. The key trap here is forgetting that strict inequalities exclude boundary values, and that the date shift can push an event into the prior calendar day — always apply the offset before reading the date.
4. What output size and growth value result for Product B's Q3 row?
   Answer: Four rows remain, and Product B's Q3 growth is $25\%$. When working with unpivoting (also called "melting" or reshaping wide-to-long data), you need to carefully track which rows survive after filtering, because that directly affects both your row count and any calculations that depend on sequential ordering. Starting with six total rows after unpivoting (two products × three quarters), you discard rows with missing sales. Product A loses its Q3 row, and Product B loses its Q2 row — that's two rows removed, leaving four rows total. This immediately eliminates choice A, which incorrectly claims six rows remain after the discard step. For Product B's growth calculation, the surviving rows are Q1 ($80$) and Q3 ($100$). Because Q2 was discarded, Q3's "immediately preceding retained row" is Q1 — not Q2. Growth is computed as $\frac{100 - 80}{80} = \frac{20}{80} = 25\%$. So the correct answer is C: four rows remain and Product B's Q3 growth is $25\%$. Choice B is wrong because the growth value is not missing — both Q1 and Q3 values exist, so a calculation is entirely possible. Choice D is tempting but uses the wrong denominator: $\frac{20}{100} = 20\%$ divides by the ending value rather than the starting value, which is a classic percentage-change error. Study tip: On reshaping and growth questions, always rebuild the surviving dataset row by row before calculating. The key trap is assuming the "previous period" is the calendar predecessor — it's actually the closest retained row, which changes when gaps exist after filtering.
5. Which customers remain after the stated sequence of operations?
   Answer: Customer B only Whenever you see a question about sequential data operations, the order of steps is everything — applying filters before or after a deduplication step produces completely different results. Here, the analyst follows a strict three-step sequence: (1) sort each customer's rows by effective date descending, (2) keep only the first (most recent) row per customer, and (3) filter for status = 'Active' AND balance $> 100$. The key insight is that the filter applies after the snapshot is reduced to one row per customer — so only the most recent row matters, regardless of what older rows contain. Customer A: The most recent row is inactive with balance $200$. It fails the status filter. Customer A is removed. Customer B: The most recent row is active with balance $150 > 100$. Both conditions pass. Customer B survives. Customer C: The most recent row is active but has balance $90 \leq 100$. It fails the balance filter. Customer C is removed. Only Customer B remains, making A the correct answer. Choice B is wrong because it includes Customer A — a tempting trap if you assume the older active row with balance $300$ is evaluated, but it isn't; the newest row wins. Choice C is wrong for the same structural reason applied to Customer C: the newer active row at $90$ fails the balance threshold, so the older $500$ row is irrelevant. Choice D incorrectly assumes all rows across all customers are eligible, ignoring the deduplication step entirely. Study tip: On operations-sequencing questions, trace each customer through every step in order — never skip ahead to the filter and work backward.
6. After transforming to one record per user, what are the conversion results by retained variant?
   Answer: Variant A converts two of three users; variant B converts one of two users. When analyzing A/B test transformations, your job is to apply three rules sequentially: (1) keep only the earliest assignment per user, (2) discard purchases that occurred before that assignment, and (3) flag conversion if any purchase falls within seven days after assignment. Walk through each user carefully. U1 keeps variant A (Jan 1), purchases Jan 3 and Jan 4 — both within seven days → converted. U2 keeps variant A (Jan 1), purchases Jan 10 — that's nine days after assignment, outside the window → not converted. U3 purchased Jan 1, but was assigned B on Jan 2, so the Jan 1 purchase is discarded (it predates assignment); the Jan 8 purchase is six days after Jan 2 → converted under B. U4 keeps variant B (Jan 1), no purchases → not converted. U5 was assigned A on Jan 1 and reassigned B on Jan 2 — the rule retains the earliest assignment, so U5 stays in variant A; the Jan 3 purchase is two days after Jan 1 → converted. That gives variant A: U1 (converted), U2 (not converted), U5 (converted) — 2 of 3. Variant B: U3 (converted), U4 (not converted) — 1 of 2. This matches answer B. Choice A incorrectly counts U2 as converted, ignoring the seven-day window. Choice C misassigns U5 to variant B instead of A (the earliest-assignment rule). Choice D likely applies the window incorrectly across multiple users, understating A's conversions. The key study tip: always apply transformation rules in strict order — assignment retention first, purchase filtering second, conversion window third. Mixing the sequence is the most common trap on these questions.
7. Which ordered sequence contains the first three products after the sort?
   Answer: Q, S, P Multi-key sorting works like a tiebreaker system: the first sort key separates records into groups, the second key orders records within those groups, and the third key breaks any remaining ties. When you see a question like this, mentally process each key in sequence rather than trying to sort everything at once. Start with margin score descending. Products P, Q, S, and U all have a margin score of $30$, while R and T have $25$. So the top group is {P, Q, S, U} and the bottom group is {R, T} — the first three results will come entirely from the top group. Now apply the second key — revenue descending — within the $30$-margin group. The revenues are: Q = $120$, S = $120$, P = $100$, U = $90$. Q and S tie at the top with $120$, then P at $100$, then U at $90$. Finally, break the Q-S tie using product name ascending (alphabetical order). "Q" comes before "S" alphabetically, so Q ranks first, S second. The full ordering of the first three is Q, S, P — answer A. Answer B (S, Q, P) reverses Q and S, applying the product-name sort descending instead of ascending — a common misread. Answer C (T, R, Q) places the $25$-margin products first, which would only happen if margin were sorted ascending rather than descending. Answer D (Q, P, S) skips S before P, ignoring that the revenue tiebreaker must be applied before the name tiebreaker. When multi-key sorting appears on an exam, always resolve each key fully before moving to the next — and double-check the direction (ascending vs. descending) for every key.
8. Which online rows remain after the complete transformation?
   Answer: O2, O3, and O4 When a multi-step data pipeline filters and imputes in sequence, you must apply each transformation in order, using only the data available at that exact stage — not the full original dataset. Here's how to trace through this problem. After step one, you retain only the four online rows: O1 (spend = $10$), O2 (spend = missing), O3 (spend = $30$), and O4 (spend = $50$). The offline rows with $100$ and $200$ are gone. Step two imputes missing spend using the median of observed spend among the retained rows. The observed values are $10$, $30$, and $50$. The median of these three values is $30$, so O2's spend becomes $30$. Step three keeps rows where spend $\geq 30$. Checking each row: O1 has $10$ (fails), O2 now has $30$ (passes), O3 has $30$ (passes), O4 has $50$ (passes). The surviving rows are O2, O3, and O4 — confirming B. Choice A (O3 and O4 only) reflects the trap of forgetting that O2 was imputed with $30$ and therefore meets the threshold — it incorrectly drops O2. Choice C (O2 and O4 only) wrongly eliminates O3, perhaps misreading the condition as strictly greater than $30$ rather than greater than or equal to $30$. Choice D (all four) ignores that O1's spend of $10$ falls below the $30$ cutoff. Your strategy tip: always flag the words "greater than or equal to" versus "greater than" — one word changes which rows survive — and remember that imputed values must be rechecked against subsequent filters just like any other value.
9. Which transformation and result meet the requirement?
   Answer: Filter completed orders, sum lines by order, then average the order totals to obtain $95$. Whenever you see a question about averaging across groups, ask yourself: what is the unit being averaged? Here, the requirement is that each completed order carries equal weight — meaning you must first collapse line items into order-level totals, then average those totals. The correct approach in D does exactly this. First, filter to completed orders (O1 and O3, dropping canceled O2). Then sum the line items per order: O1 totals $60 + 40 = 100$, and O3 totals $30 + 30 + 30 = 90$. Finally, average the two order totals: $\frac{100 + 90}{2} = 95$. Each order gets equal weight, which is precisely what the analyst requires. A makes two errors: it divides by three orders instead of two, ignoring that O2 was filtered out, and it skips the per-order grouping step. The result, $\approx 63.33$, is both mathematically wrong and conceptually flawed. B averages the raw line-item values directly — $\frac{60 + 40 + 30 + 30 + 30}{5} = 38$ — which gives orders with more line items disproportionate influence. O1 and O3 both contribute lines, but O3's three entries drag the average down unfairly. This confuses line-level averaging with order-level averaging. C forgets to filter entirely, leaving O2's $200$ in the dataset. Averaging all three order totals $(100 + 200 + 90) \div 3 = 130$ contaminates the result with canceled-order revenue. Study tip: On aggregation questions, always identify the correct grain (order vs. line item) before computing any average — filtering and grouping must happen in the right sequence.
10. Under standard SQL null-handling rules, how many rows are returned by WHERE return_date IS NULL AND status <> 'Canceled'?
    Answer: Five orders are returned. Whenever you see a SQL WHERE clause combining IS NULL with a comparison like <> 'Canceled', the critical concept being tested is how SQL handles NULL values in boolean logic — specifically, that NULL is never equal to or not equal to anything; comparisons involving NULL always evaluate to UNKNOWN, not TRUE or FALSE. Let's map out the twelve rows: 5 completed / missing return_date, 3 canceled / missing return_date, 2 completed / populated return_date, and 2 rows with both fields missing. The filter WHERE return_date IS NULL AND status <> 'Canceled' requires both conditions to be TRUE. First, return_date IS NULL filters to the 10 rows missing a return date (5 completed + 3 canceled + 2 with missing status). Then status <> 'Canceled' is applied: the 5 completed rows pass (TRUE), the 3 canceled rows fail (FALSE), and the 2 rows with NULL status produce UNKNOWN — SQL excludes UNKNOWN rows just like FALSE rows. That leaves exactly 5 rows, confirming answer A is correct. Answer B (seven rows) likely comes from adding the 5 completed rows to the 2 NULL-status rows, forgetting that UNKNOWN fails the WHERE clause. Answer C (eight rows) may reflect incorrectly including the 3 canceled rows, misreading <> as =. Answer D (ten rows) ignores the second condition entirely and counts all rows with a missing return_date. Your study tip: always remember that in SQL, NULL compared to any value — even using <> — returns UNKNOWN, and WHERE clauses silently drop UNKNOWN results. When you see NULL-status rows, they will never satisfy an equality or inequality filter.

Start this quiz