Business Analytics Quiz: Transforming Tabular Data

10 questions · exam conditions

0:00

Transforming Tabular DataQuestion 1 of 10

An Orders dataset contains twelve rows. Five completed orders have a missing return_date; three canceled orders have a missing return_date; two completed orders have a populated return_date; and two orders have both a missing return_date and a missing status.

Under standard SQL null-handling rules, how many rows are returned by WHERE return_date IS NULL AND status <> 'Canceled'?

Five orders are returned.

Seven orders are returned.

Eight orders are returned.

Ten orders are returned.

← Back to quizzes

Business Analytics Quiz

Business Analytics Quiz: Transforming Tabular Data

Practice Transforming Tabular Data 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 Transforming Tabular Data, 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

Under standard SQL null-handling rules, how many rows are returned by WHERE return_date IS NULL AND status <> 'Canceled'?

Five orders are returned. (correct answer)
Seven orders are returned.
Eight orders are returned.
Ten orders are returned.

Explanation: 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.

Question 2

A query first retains event timestamps greater than or equal to July 1 at 12:00 a.m. UTC and strictly earlier than August 1 at 12:00 a.m. UTC. It then creates a business_date by subtracting four hours from each retained timestamp. E1 occurred July 1 at 2:00 a.m. UTC; E2 occurred July 31 at 11:00 p.m. UTC; E3 occurred August 1 at 12:00 a.m. UTC; and E4 occurred June 30 at 11:00 p.m. UTC.

Which events and derived business dates appear in the output?

E1 dated June 30, E2 dated July 31, and E4 dated June 30
E2 dated July 31 and E3 dated July 31
E1 dated July 1 and E2 dated July 31
E1 dated June 30 and E2 dated July 31 (correct answer)

Explanation: 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.

Question 3

A marketing dataset is processed by first retaining online-channel rows. Missing spend is then replaced with the median of the observed spend values among those retained rows. Finally, rows with spend greater than or equal to that median are kept. The online rows are O1 with spend $10$ , O2 with missing spend, O3 with spend $30$ , and O4 with spend $50$ . Two offline rows have spend values of $100$ and $200$ .

Which online rows remain after the complete transformation?

O3 and O4 only
O2, O3, and O4 (correct answer)
O2 and O4 only
O1, O2, O3, and O4

Explanation: 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.

Question 4

An order file contains O1 and O3 as completed orders and O2 as canceled. A line-item file contains two lines for O1 worth $60$ and $40$ , one line for O2 worth $200$ , and three lines for O3 worth $30$ each. The analyst must report average completed-order revenue, giving each completed order equal weight.

Which transformation and result meet the requirement?

Filter completed orders, sum all retained lines, then divide by all three orders to obtain about $63.33$ .
Filter completed orders, then average the retained line values directly to obtain $38$ .
Sum lines by order, include all order statuses, then average the totals to obtain $130$ .
Filter completed orders, sum lines by order, then average the order totals to obtain $95$ . (correct answer)

Explanation: 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.

Question 5

A wide product file has separate Q1, Q2, and Q3 sales columns. Product A has values $100$ , $120$ , and missing. Product B has values $80$ , missing, and $100$ . The analyst unpivots the file into product-quarter rows, discards rows with missing sales, sorts each product chronologically, and computes growth from the immediately preceding retained row.

What output size and growth value result for Product B's Q3 row?

Six rows remain, and Product B's Q3 growth is $25\%$ .
Four rows remain, and Product B's Q3 growth is missing.
Four rows remain, and Product B's Q3 growth is $25\%$ . (correct answer)
Four rows remain, and Product B's Q3 growth is $20\%$ .

Explanation: 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.

Question 6

A customer snapshot file may contain multiple rows per customer. The analyst sorts each customer's rows by effective date descending, retains the first row, and only then filters for status = 'Active' and balance greater than $100$ . Customer A has an older active row with balance $300$ and a newer inactive row with balance $200$ . Customer B's newest row is active with balance $150$ . Customer C has an older active row with balance $500$ and a newer active row with balance $90$ .

Which customers remain after the stated sequence of operations?

Customer B only (correct answer)
Customers A and B only
Customers B and C only
Customers A, B, and C

Explanation: 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.

Question 7

An A/B test transformation retains each user's earliest experiment assignment, discards purchase events occurring before that assignment, and marks a user as converted if at least one purchase occurs within seven days after assignment. U1 was assigned A on January 1 and purchased on January 3 and January 4. U2 was assigned A on January 1 and purchased on January 10. U3 purchased on January 1, was assigned B on January 2, and purchased again on January 8. U4 was assigned B on January 1 and made no purchase. U5 was assigned A on January 1, was reassigned to B on January 2, and purchased on January 3.

After transforming to one record per user, what are the conversion results by retained variant?

Variant A converts three of three users; variant B converts one of two users.
Variant A converts two of three users; variant B converts one of two users. (correct answer)
Variant A converts two of two users; variant B converts two of three users.
Variant A converts one of three users; variant B converts two of two users.

Explanation: 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.

Question 8

A product dataset contains the following records, stated as product, margin score, and revenue: P, $30$ , $100$ ; Q, $30$ , $120$ ; R, $25$ , $200$ ; S, $30$ , $120$ ; T, $25$ , $250$ ; and U, $30$ , $90$ . An analyst sorts by margin score descending, then revenue descending, and finally product name ascending.

Which ordered sequence contains the first three products after the sort?

Q, S, P (correct answer)
S, Q, P
T, R, Q
Q, P, S

Explanation: 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.

Question 9

A sales transformation first retains records with quantity of at least $10$ . It then defines net revenue as zero for returned transactions and, for all other retained transactions, as $quantity \times price \times (1-discount)$ . Four records are processed: R1 has quantity $12$ , price $10$ , discount $0.10$ , and is not returned; R2 has quantity $9$ , price $20$ , no discount, and is not returned; R3 has quantity $15$ , price $8$ , no discount, and is returned; R4 has quantity $10$ , price $12$ , discount $0.25$ , and is not returned.

After the filter and transformation, which result is correct?

Three rows remain, with total net revenue of $198$ . (correct answer)
Four rows remain, with total net revenue of $378$ .
Three rows remain, with total net revenue of $318$ .
Two rows remain, with total net revenue of $198$ .

Explanation: 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.

Question 10

A sales-representative dataset is first filtered to active representatives. In the East region, A is active with sales of $100$ , B is inactive with sales of $100$ , C is active with sales of $90$ , and G is active with sales of $80$ . In the West region, D is active with sales of $120$ , while E and F are active with sales of $110$ each. Within each region, the analyst assigns descending dense ranks and retains ranks of at most $2$ .

Which representatives are retained?

A, C, D, and E
A, B, C, D, E, and F
A, C, D, E, and F (correct answer)
A, D, E, and F

Explanation: 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.