SQL Quiz: Creating Flags And Buckets
10 questions · exam conditions
0:00
Creating Flags And BucketsQuestion 1 of 10

In PostgreSQL, an analyst must create a monthly date bucket from occurred_at. The bucket will be joined to a calendar table whose month_start column contains dates such as 2025-01-01 and 2026-01-01, so records from the same month in different years must remain separate.

Which expression creates the most appropriate monthly bucket?

DATE_TRUNC('month', occurred_at)::date
EXTRACT(MONTH FROM occurred_at)::integer
TO_CHAR(occurred_at, 'Month')
occurred_at::date - INTERVAL '30 days'
← Back to quizzes

SQL Quiz

SQL Quiz: Creating Flags And Buckets

Practice Creating Flags And Buckets in SQL 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 Creating Flags And Buckets, giving you a quick way to practice the rules, question types, and explanations that matter most for SQL.

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

In PostgreSQL, an analyst must create a monthly date bucket from occurred_at. The bucket will be joined to a calendar table whose month_start column contains dates such as 2025-01-01 and 2026-01-01, so records from the same month in different years must remain separate.

Which expression creates the most appropriate monthly bucket?

  1. DATE_TRUNC('month', occurred_at)::date (correct answer)
  2. EXTRACT(MONTH FROM occurred_at)::integer
  3. TO_CHAR(occurred_at, 'Month')
  4. occurred_at::date - INTERVAL '30 days'
Explanation: When creating a bucket for joining to another table, you need an expression that produces a value matching the data type and format of the join column. Since month_start contains actual dates like 2025-01-01, your bucket must also be a date — and it must preserve the year so that January 2025 and January 2026 don't collapse into the same group. DATE_TRUNC('month', occurred_at)::date does exactly this. DATE_TRUNC snaps a timestamp back to the first moment of its month (e.g., 2025-03-15 becomes 2025-03-01 00:00:00), and casting to ::date strips the time component, leaving 2025-03-01 — a clean date ready to join against month_start. The three distractors each fail in a different way. B — EXTRACT(MONTH FROM occurred_at)::integer — returns just the month number (1–12), discarding the year entirely, which means January 2025 and January 2026 both become 1. You'd get incorrect cross-year matches. C — TO_CHAR(occurred_at, 'Month') — returns a text string like 'March ' (with padding), which can't join to a date column and still loses the year. D — subtracting INTERVAL '30 days' is arbitrary and logically meaningless for bucketing; February has 28 days, so subtracting 30 days from a date in that month could push you into the prior month unpredictably. A good study rule: whenever a question asks about time bucketing for joins, check three things — does the expression preserve the year, produce the correct data type, and snap to a consistent boundary (like the first of the month)? DATE_TRUNC satisfies all three.

Question 2

A PostgreSQL table stores event_ts as a timestamp without time zone. A flag must equal 1 only for events occurring during January 2026, including every time on January 31 but excluding exactly midnight on February 1.

Which expression creates the flag correctly?

  1. CASE WHEN event_ts BETWEEN TIMESTAMP '2026-01-01' AND TIMESTAMP '2026-01-31' THEN 1 ELSE 0 END
  2. CASE WHEN event_ts >= TIMESTAMP '2026-01-01' AND event_ts < TIMESTAMP '2026-02-01' THEN 1 ELSE 0 END (correct answer)
  3. CASE WHEN event_ts >= TIMESTAMP '2026-01-01' AND event_ts <= TIMESTAMP '2026-02-01' THEN 1 ELSE 0 END
  4. CASE WHEN EXTRACT(MONTH FROM event_ts) = 1 THEN 1 ELSE 0 END
Explanation: When filtering timestamps by a date range, the key question is always: what are the exact boundary conditions? The requirement here is to include all of January 2026 — every moment through 23:59:59.999... on January 31 — while excluding midnight on February 1 (2026-02-01 00:00:00). This calls for a half-open interval: >= start and < exclusive_end. Option B nails this with event_ts >= TIMESTAMP '2026-01-01' AND event_ts < TIMESTAMP '2026-02-01'. The lower bound includes midnight on January 1, and the upper bound excludes exactly midnight on February 1, capturing every possible timestamp in January without letting that boundary moment slip through. Option A uses BETWEEN, which in SQL is fully inclusive on both ends. BETWEEN TIMESTAMP '2026-01-01' AND TIMESTAMP '2026-01-31' only extends to 2026-01-31 00:00:00 — midnight on January 31. Any event at 9 AM or 11:59 PM on January 31 would be missed entirely, making this a silent data loss bug. Option C uses <=TIMESTAMP '2026-02-01', making the upper bound inclusive. This means midnight on February 1 would receive a flag of 1, which directly violates the requirement. Option D extracts only the month number, ignoring the year entirely. Events from January 2020, January 2030, or any other January would all incorrectly receive a flag of 1. Study tip: Whenever you need to capture a full calendar month in SQL, the safest pattern is always >= first_day_of_month AND < first_day_of_next_month. This avoids BETWEEN's inclusivity trap and eliminates year-scoping errors.

Question 3

In PostgreSQL, a numeric amount must be assigned the lower endpoint of its width-100 bucket. Buckets are half-open, so the bucket labeled -100 covers values from -100 up to but not including 0, and the bucket labeled 0 covers values from 0 up to but not including 100. The expression used is FLOOR(amount / 100.0) * 100.

What bucket labels are produced, respectively, for amount values -1, 0, 99, and 100?

  1. -100, 0, 0, and 100 (correct answer)
  2. 0, 0, 0, and 100
  3. -100, 0, 100, and 100
  4. -100, -100, 0, and 100
Explanation: When working with bucket-labeling expressions like FLOOR(amount / 100.0) * 100, the key is understanding how FLOOR behaves with negative numbers — this is where most mistakes happen. FLOOR always rounds toward negative infinity, not toward zero. So while FLOOR(0.99) = 0, FLOOR(-0.01) = -1. Let's trace each value:
  • amount = -1: ⌊−1/100.0⌋×100=⌊−0.01⌋×100=−1×100=−100\lfloor -1/100.0 \rfloor \times 100 = \lfloor -0.01 \rfloor \times 100 = -1 \times 100 = -100
  • amount = 0: ⌊0/100.0⌋×100=⌊0⌋×100=0\lfloor 0/100.0 \rfloor \times 100 = \lfloor 0 \rfloor \times 100 = 0
  • amount = 99: ⌊99/100.0⌋×100=⌊0.99⌋×100=0\lfloor 99/100.0 \rfloor \times 100 = \lfloor 0.99 \rfloor \times 100 = 0
  • amount = 100: ⌊100/100.0⌋×100=⌊1.0⌋×100=100\lfloor 100/100.0 \rfloor \times 100 = \lfloor 1.0 \rfloor \times 100 = 100
This gives -100, 0, 0, 100 — confirming A is correct. B is wrong because it assigns 0 to amount = -1, which would only be true if FLOOR truncated toward zero (like integer division might in some languages). C incorrectly labels amount = 99 as 100, confusing the lower endpoint with the upper boundary — 99 belongs inside the 0-to-99 bucket. D is wrong because it places amount = 0 in the -100 bucket, ignoring that FLOOR(0) = 0, not -1. The study tip here: always test a small negative value (like -1) when evaluating FLOOR-based bucketing. That's exactly where intuition breaks down, and exam questions exploit it.

Question 4

In PostgreSQL, an organization uses fiscal quarters beginning on February 1, May 1, August 1, and November 1. An analyst needs a date representing the first day of the fiscal quarter containing order_date.

Which expression creates the correct fiscal-quarter start date?

  1. DATE_TRUNC('quarter', order_date)::date
  2. (DATE_TRUNC('quarter', order_date + INTERVAL '1 month') - INTERVAL '1 month')::date
  3. (DATE_TRUNC('quarter', order_date - INTERVAL '1 month') + INTERVAL '1 month')::date (correct answer)
  4. (DATE_TRUNC('quarter', order_date - INTERVAL '2 months') + INTERVAL '2 months')::date
Explanation: When working with non-standard fiscal calendars in SQL, the core challenge is that DATE_TRUNC('quarter', ...) always snaps to calendar quarters starting January 1, April 1, July 1, and October 1 — one month behind this organization's fiscal quarters. Your job is to shift dates so the fiscal boundaries align with those calendar boundaries before truncating, then reverse the shift afterward. The correct approach in C works elegantly: subtracting one month from order_date maps each fiscal quarter onto a standard calendar quarter (February→January, May→April, August→July, November→October), then DATE_TRUNC('quarter', ...) snaps to the calendar quarter start, and finally adding one month back produces the correct fiscal quarter start. For example, if order_date is March 15, subtracting one month gives February 15, which truncates to January 1, and adding one month yields February 1 — exactly right. A simply truncates to the standard calendar quarter with no offset adjustment, so a March order would return January 1 instead of February 1. This ignores the fiscal calendar entirely. B adds one month before truncating, then subtracts one month after. This shifts in the wrong direction — a March order becomes April 15, truncates to April 1, then becomes March 1, which is incorrect. D uses a two-month offset (subtract two, then add two). Testing March 15: subtract two months → January 15, truncate → January 1, add two months → March 1. That's wrong; the correct answer is February 1. A useful strategy: whenever fiscal quarters are offset by N months from calendar quarters, subtract N months before truncating and add N months after. Always verify with a concrete date from each quarter boundary.

Question 5

As of 2026-03-31, an event must be classified as follows: a future event is Invalid; an event at most 30 days old is Recent; an event from 31 through 90 days old is Established; and an older event is Old. There are no NULL event dates.

Which ordered CASE expression applies these date buckets correctly?

  1. CASE WHEN event_date >= DATE '2026-03-01' THEN 'Recent' WHEN event_date >= DATE '2025-12-31' THEN 'Established' WHEN event_date > DATE '2026-03-31' THEN 'Invalid' ELSE 'Old' END
  2. CASE WHEN event_date > DATE '2026-03-31' THEN 'Invalid' WHEN event_date >= DATE '2026-03-01' THEN 'Recent' WHEN event_date >= DATE '2025-12-31' THEN 'Established' ELSE 'Old' END (correct answer)
  3. CASE WHEN event_date >= DATE '2026-03-31' THEN 'Invalid' WHEN event_date > DATE '2026-03-01' THEN 'Recent' WHEN event_date > DATE '2025-12-31' THEN 'Established' ELSE 'Old' END
  4. CASE WHEN event_date > DATE '2026-03-31' THEN 'Invalid' WHEN event_date >= DATE '2025-12-31' THEN 'Established' WHEN event_date >= DATE '2026-03-01' THEN 'Recent' ELSE 'Old' END
Explanation: When writing a CASE expression that classifies date ranges, two things matter equally: using the correct boundary values and placing conditions in the right order. SQL evaluates CASE WHEN clauses top to bottom and returns the first match, so a misplaced condition can silently swallow rows meant for another bucket. First, let's establish the correct boundaries from the passage (reference date: 2026-03-31): future dates (> 2026-03-31) → Invalid; within 30 days (≥ 2026-03-01) → Recent; 31–90 days old (≥ 2025-12-31) → Established; older → Old. Notice that "at most 30 days old" means event_date >= 2026-03-31 - 30 days = 2026-03-01, and "90 days old" means 2026-03-31 - 90 days = 2025-12-31. Option B correctly checks event_date > '2026-03-31' first for Invalid, then >= '2026-03-01' for Recent, then >= '2025-12-31' for Established, with Old as the fallback. The order flows from newest to oldest, preventing overlap. Option A fails immediately because it checks Recent before Invalid — a future date like 2026-04-15 satisfies >= '2026-03-01' and would be wrongly labeled Recent, never reaching the Invalid check. Option C uses >= '2026-03-31' for Invalid (with >= instead of >), which incorrectly labels 2026-03-31 itself as Invalid when it should be Recent (0 days old). It also uses strict > for Recent and Established, shifting all boundaries off by one day. Option D places Established before Recent — a date like 2026-03-15 would match Established's >= '2025-12-31' check first and never reach Recent. The strategy to remember: always order CASE branches from the most restrictive/newest condition to least restrictive/oldest, and double-check whether each boundary needs > or >= by asking whether the boundary date itself belongs to that bucket.

Question 6

Seven rows have IDs 1 through 7 and corresponding scores 10, 10, 20, 30, 30, 30, and 40. PostgreSQL evaluates NTILE(3) OVER (ORDER BY score, id) for these rows.

Which sequence of bucket numbers is assigned in ID order?

  1. 1, 1, 2, 3, 3, 3, 3
  2. 1, 1, 2, 2, 3, 3, 3
  3. 1, 1, 1, 2, 2, 2, 3
  4. 1, 1, 1, 2, 2, 3, 3 (correct answer)
Explanation: When you see NTILE(n) in SQL, your job is to distribute rows as evenly as possible across n buckets, with larger buckets coming first whenever rows don't divide evenly. Here's how to work through it: NTILE(3) splits 7 rows into 3 buckets. Dividing 7 ÷ 3 gives 2 remainder 1, meaning one bucket gets an extra row. PostgreSQL front-loads that extra row, so the distribution is 3, 2, 2 — bucket 1 gets 3 rows, buckets 2 and 3 each get 2. The window orders by (score, id), so the assignment follows the sequence: rows with IDs 1, 2, 3 → bucket 1; IDs 4, 5 → bucket 2; IDs 6, 7 → bucket 3. Restated in ID order: 1, 1, 1, 2, 2, 3, 3 — which is answer D. Choice A (1, 1, 2, 3, 3, 3, 3) puts four rows in bucket 3, which violates the rule that extra rows go to earlier buckets, not later ones. Choice B (1, 1, 2, 2, 3, 3, 3) distributes as 2, 2, 3 — the extra row is incorrectly placed in the last bucket instead of the first. Choice C (1, 1, 1, 2, 2, 2, 3) gives a 3, 3, 1 distribution, which would only be correct if there were 2 remainder rows (e.g., 7 ÷ 3 leaves 1, not 2). A handy tip: always compute rows ÷ n first. The remainder tells you how many buckets get the extra row, and those are always the leading buckets. That single rule eliminates most NTILE distractors instantly.

Question 7

A customer tier is assigned from annual_spend using the following SQL expression:

CASE WHEN annual_spend >= 1000 THEN 'Gold' WHEN annual_spend >= 500 THEN 'Silver' ELSE 'Bronze' END

What tiers are assigned, respectively, to customers whose annual_spend values are 1000, 500, and NULL?

  1. Gold, Silver, and Bronze (correct answer)
  2. Silver, Bronze, and NULL
  3. Gold, Silver, and NULL
  4. Silver, Silver, and Bronze
Explanation: When working with a CASE expression in SQL, remember that conditions are evaluated in order, and execution stops at the first condition that evaluates to true. This short-circuit behavior is the key to understanding how tier assignments work here. For annual_spend = 1000: the first condition (>= 1000) is true, so the result is 'Gold' — no further conditions are checked. For annual_spend = 500: the first condition fails (500 is not ≥ 1000), but the second condition (>= 500) is true, so the result is 'Silver'. For annual_spend = NULL: this is where many students stumble. Any comparison involving NULL — including NULL >= 1000 and NULL >= 500 — evaluates to UNKNOWN, not true. So both WHEN conditions are skipped, and the ELSE branch fires, returning 'Bronze'. This makes A the correct answer: Gold, Silver, and Bronze. Choice B is wrong because it misapplies the threshold logic — 1000 does satisfy >= 1000, so it's Gold, not Silver. Choice C correctly identifies Gold and Silver but wrongly concludes that NULL produces NULL; the ELSE clause catches all remaining rows, including those with NULL values. Choice D reflects a misreading of the thresholds entirely, suggesting both 1000 and 500 yield Silver. The key study tip: NULL comparisons always produce UNKNOWN, never true — so NULL values always fall through to ELSE in a CASE expression. Internalize this, because NULL behavior is a favorite trap in SQL exams.

Question 8

Three one-hot flags are created from temperature: below_freezing for values below 0, moderate for values from 0 up to but not including 25, and hot for values of at least 25. A missing temperature must set all three flags to 0.

Which sequence of flag triples is correct for temperatures -1, 0, 25, and NULL, respectively?

  1. (1,0,0), (0,1,0), (0,1,0), (0,0,0)
  2. (1,0,0), (1,1,0), (0,1,1), (0,0,0)
  3. (1,0,0), (0,1,0), (0,0,1), (0,0,0) (correct answer)
  4. (1,0,0), (0,1,0), (0,0,1), (0,0,1)
Explanation: When working with one-hot encoding in SQL, each flag represents a mutually exclusive category — meaning exactly one flag should be 1 and the rest 0 for any valid (non-NULL) input. Your job here is to apply three boundary conditions carefully: below_freezing triggers when temperature < 0, moderate when 0 <= temperature < 25, and hot when temperature >= 25. Walking through each value confirms C is correct. For -1: it's below 0, so (1,0,0). For 0: it meets 0 <= 0 < 25, so moderate fires → (0,1,0). For 25: it meets >= 25, so hot fires → (0,0,1). For NULL: per the passage, all flags are forced to 0 → (0,0,0). A is wrong because it maps 25 to (0,1,0), incorrectly placing it in the moderate range. The moderate condition is explicitly up to but not including 25, so 25 belongs to hot, not moderate. B is wrong on multiple counts — it assigns overlapping flags like (1,1,0) and (0,1,1), which violates one-hot encoding entirely. No valid temperature should ever have two flags set to 1 simultaneously. D is almost right but fails on the NULL case. It returns (0,0,1) for NULL, as if it treated NULL like a value >= 25. In SQL, comparisons with NULL produce UNKNOWN, not TRUE — but the passage explicitly requires all flags to be 0 for missing values. A reliable strategy: always handle NULL as a special case in flag logic, and double-check whether boundary values like 0 and 25 are inclusive or exclusive in each range definition.

Question 9

In PostgreSQL, completed_units and planned_units are integer columns. A performance flag must equal 1 only when planned_units is positive and the completion ratio is at least 0.8. It must equal 0 when the plan is zero, negative, or NULL, and the expression must not raise a division-by-zero error.

Which expression implements the flag correctly?

  1. CASE WHEN completed_units * 1.0 / COALESCE(NULLIF(planned_units, 0), 1) >= 0.8 THEN 1 ELSE 0 END
  2. CASE WHEN planned_units <> 0 AND completed_units / planned_units >= 0.8 THEN 1 ELSE 0 END
  3. CASE WHEN planned_units > 0 AND completed_units / planned_units >= 0.8 THEN 1 ELSE 0 END
  4. CASE WHEN planned_units > 0 AND completed_units * 1.0 / NULLIF(planned_units, 0) >= 0.8 THEN 1 ELSE 0 END (correct answer)
Explanation: When working with division in SQL, you must guard against two separate problems: division-by-zero errors and incorrect business logic. Keeping these concerns distinct is the key to answering this question correctly. The correct expression is D. It first checks planned_units > 0, which filters out zero, negative, and NULL values at the logic level (NULL comparisons return unknown, so NULL planned_units never reaches the division). Then it uses completed_units * 1.0 / NULLIF(planned_units, 0) to perform floating-point division safely. The NULLIF is a redundant but harmless safety net here — the real work is done by the > 0 guard. A is flawed in its business logic. By wrapping planned_units in COALESCE(NULLIF(planned_units, 0), 1), it substitutes 1 when planned_units is zero or NULL, which means the ratio is computed against a fake denominator. A row with planned_units = NULL could incorrectly receive a flag of 1 if completed_units is large enough — violating the requirement. B uses <> 0, which allows negative planned_units to pass the guard. A negative plan producing a ratio >= 0.8 could incorrectly flag the row. It also uses integer division, which truncates — completed_units / planned_units drops the decimal, so ratios like 0.9 become 0, causing under-counting. C fixes the negative-plan problem with > 0, but still uses integer division, meaning the >= 0.8 threshold can never be reached for ratios below 1.0. As a strategy, always ask two questions about any division expression: Does the guard prevent bad denominators? and Does the arithmetic produce the right numeric type? Both must be yes.

Question 10

A report must place each score into exactly one bucket. Scores below 60 are Fail, scores from 60 through 79 are Pass, scores of at least 80 are Distinction, and missing scores are Missing.

Which CASE expression implements the required buckets correctly?

  1. CASE WHEN score >= 60 THEN 'Pass' WHEN score >= 80 THEN 'Distinction' WHEN score IS NULL THEN 'Missing' ELSE 'Fail' END
  2. CASE WHEN score IS NULL THEN 'Missing' WHEN score >= 80 THEN 'Distinction' WHEN score >= 60 THEN 'Pass' ELSE 'Fail' END (correct answer)
  3. CASE WHEN score = NULL THEN 'Missing' WHEN score > 80 THEN 'Distinction' WHEN score > 60 THEN 'Pass' ELSE 'Fail' END
  4. CASE WHEN score IS NULL THEN 'Missing' WHEN score BETWEEN 60 AND 80 THEN 'Pass' WHEN score >= 80 THEN 'Distinction' ELSE 'Fail' END
Explanation: When writing a CASE expression with overlapping numeric ranges, the order of your WHEN clauses is critical — SQL evaluates them top to bottom and returns the result of the first condition that is true, ignoring everything after it. Option B is correct because it sequences conditions properly. It checks NULL first (using IS NULL, the only valid NULL comparison), then catches scores of 80 or above as Distinction, then catches the remaining 60–79 range as Pass, and lets the ELSE handle anything below 60 as Fail. Every score lands in exactly one bucket. Option A fails because score >= 60 is evaluated before score >= 80. Any score of 80 or higher satisfies the first condition and gets labeled Pass — it never reaches the Distinction branch. The NULL check also comes too late; a NULL score will fall through to ELSE 'Fail' before reaching IS NULL. Option C has two problems: it uses = NULL instead of IS NULL, which always evaluates to UNKNOWN (never true), so NULL scores will silently fall to ELSE 'Fail'. It also uses > 80 and > 60 (strict greater-than), which means a score of exactly 80 returns Pass and a score of exactly 60 returns Fail — both wrong per the requirements. Option D uses BETWEEN 60 AND 80, which includes 80 in the Pass bucket. A score of exactly 80 would match Pass before it ever reaches the Distinction branch. A good rule of thumb: always check NULL first with IS NULL, then order numeric ranges from most restrictive to least (highest to lowest), and double-check your boundary values against the specification.