SQL Quiz: Communicating Query Assumptions
10 questions · exam conditions
0:00
Communicating Query AssumptionsQuestion 1 of 10

An analyst reports revenue from orders containing accessories with this query:

SELECT o.region, SUM(o.total_amount) FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id WHERE i.category = 'Accessories' GROUP BY o.region;

orders has one row per order, while order_items has one row per item.

Which statement best communicates the query's key assumption and limitation?

The result measures accessory line-item revenue, assuming total_amount contains only the value assigned to the joined accessory item.
The result repeats full order value for each matching accessory item; it is valid only if that repetition and full-order attribution are intended.
The result measures full order value once per qualifying order, because grouping by region automatically removes repeated joined order rows.
The result excludes orders with multiple accessory items, because an inner join retains only orders having exactly one matching item row.
← Back to quizzes

SQL Quiz

SQL Quiz: Communicating Query Assumptions

Practice Communicating Query Assumptions 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 Communicating Query Assumptions, 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

An analyst reports revenue from orders containing accessories with this query:

SELECT o.region, SUM(o.total_amount) FROM orders AS o JOIN order_items AS i ON i.order_id = o.order_id WHERE i.category = 'Accessories' GROUP BY o.region;

orders has one row per order, while order_items has one row per item.

Which statement best communicates the query's key assumption and limitation?

  1. The result measures accessory line-item revenue, assuming total_amount contains only the value assigned to the joined accessory item.
  2. The result repeats full order value for each matching accessory item; it is valid only if that repetition and full-order attribution are intended. (correct answer)
  3. The result measures full order value once per qualifying order, because grouping by region automatically removes repeated joined order rows.
  4. The result excludes orders with multiple accessory items, because an inner join retains only orders having exactly one matching item row.
Explanation: Whenever you JOIN a one-to-many relationship — like orders (one row per order) joined to order_items (many rows per order) — you must ask yourself: how many times does each parent row appear in the result set? That's the heart of this question. When an order contains two accessory items, the JOIN produces two rows for that order, each carrying the same total_amount. Summing that column therefore double-counts the full order value. The query doesn't isolate accessory line-item revenue — it accumulates the entire order total once per matching item row. B is correct because it accurately names both the mechanical behavior (repetition of full order value) and the narrow condition under which the query could be valid (if full-order, multi-attributed credit is genuinely intended). A is wrong because total_amount lives on the orders table and represents the whole order, not a single line item. The JOIN doesn't magically extract accessory-only revenue from that column. C is wrong because GROUP BY region collapses rows by region, not by order. If one order has three accessory items, all three rows still roll into the regional sum — each contributing the full total_amount separately. D is wrong because an inner JOIN retains all matching rows, including orders with multiple accessory items. It drops orders with zero matching items, not orders with many. Study tip: Any time you see SUM or COUNT after a one-to-many JOIN, immediately check whether the aggregated column belongs to the "one" side — if so, duplicate rows will silently inflate your result.

Question 2

A monthly dashboard calculates daily active users with COUNT(DISTINCT user_id) for each date and then adds the daily results to produce a value labeled monthly active users.

Which note best communicates what the summed value actually represents?

  1. It is the exact number of unique monthly users because distinct counts remain distinct when values from separate dates are added.
  2. It is the number of monthly sessions because each user's activity is counted once for every session recorded on a given date.
  3. It is a sum of daily unique users, effectively active user-days; users active on multiple dates contribute more than once. (correct answer)
  4. It is a lower bound on monthly unique users because summing daily distinct counts omits users who return on later dates.
Explanation: When you see a question about aggregating distinct counts, the key concept to examine is whether "distinct" is preserved across the aggregation boundary. Distinctness within a single query partition does not carry over when you combine results arithmetically afterward. Here's the core issue: COUNT(DISTINCT user_id) per date correctly counts unique users on that date, but when you sum those daily figures, a user active on 15 different days gets counted 15 times — once per day. The resulting number measures active user-days, not unique people. That's exactly what C captures: it's a sum of daily unique users, and users active on multiple dates inflate the total. This is the honest, precise label for what the metric represents. A is wrong because it assumes distinctness is transitive across addition — that separate distinct counts somehow remain globally distinct when summed. They don't. Adding two sets of distinct values is not the same as taking the distinct union of those sets. B mischaracterizes the counting mechanism entirely; COUNT(DISTINCT user_id) doesn't count sessions, it collapses multiple sessions per user into a single count per day. The session framing is a fabricated distractor. D gets the direction of the error exactly backwards — summing daily distinct counts overcounts users who return on multiple days, producing a value above the true unique monthly count, not a lower bound. A useful rule to remember: SUM of DISTINCTs ≠ DISTINCT of all values. Whenever you see a metric built by aggregating distinct counts across time windows, ask yourself whether a returning user would be counted multiple times — if yes, the metric measures activity-days, not unique users.

Question 3

A query chooses each customer's latest address using:

ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY updated_at DESC ) AS rn

It then keeps rn = 1. The system permits two address rows for the same customer to have identical updated_at values.

Which assumption or limitation should be documented?

  1. The query always selects the address inserted last, because descending timestamp order implicitly uses insertion order to resolve equal values.
  2. The query returns every address tied for the latest timestamp, so customers with ties can appear more than once in the final result.
  3. The selected address is arbitrary among rows tied for the latest timestamp unless an additional deterministic tie-breaker is specified. (correct answer)
  4. The selected address is deterministic as long as customer_id is unique within each window partition, even when update timestamps tie.
Explanation: Whenever you work with window functions like ROW_NUMBER(), the critical question to ask is: what happens when the ordering criteria produces ties? Unlike RANK() or DENSE_RANK(), ROW_NUMBER() always assigns a unique integer to every row — but when the ORDER BY column has duplicate values, the database engine gets to decide which tied row receives which number. That decision is not guaranteed to be consistent across executions, query plans, or database versions. This is exactly why C is correct. When two rows share the same updated_at value, ROW_NUMBER() will assign rn = 1 to exactly one of them — but which one is implementation-defined and effectively arbitrary. The query will still return only one row per customer (that's ROW_NUMBER()'s guarantee), but you cannot predict or rely on which tied address gets selected. This behavior must be documented so engineers don't assume correctness that isn't there. A is wrong because SQL databases do not implicitly fall back to insertion order when timestamps tie. Physical storage order has no formal role in ORDER BY semantics, so this assumption is dangerous and unfounded. B describes the behavior of RANK() or DENSE_RANK() filtered with rn = 1, not ROW_NUMBER() — ROW_NUMBER() never produces duplicate rank values, so tied customers will not appear more than once. D is wrong because uniqueness of customer_id within the partition is unrelated to tie-breaking on updated_at; it doesn't make the result deterministic. As a study tip: whenever you see ROW_NUMBER() ORDER BY on a non-unique column, immediately flag the need for a deterministic tie-breaker (like a primary key) appended to the ORDER BY clause.

Question 4

A historical sales report joins each order to customers.customer_segment. The customer table stores only the customer's current segment, and customers may move between segments after placing an order.

Which limitation should be stated when presenting sales by customer segment?

  1. Historical orders are classified using each customer's current segment, so the result is not necessarily the segment distribution that existed when sales occurred. (correct answer)
  2. Historical orders are classified using the segment effective on the order date, provided the customer table contains one current row per customer.
  3. Segment changes cause historical orders to appear in every segment the customer has occupied, even though only the current segment is stored.
  4. Segment totals remain historically accurate as long as segment names are unique, because segment movement does not affect completed orders.
Explanation: When a SQL query joins orders to a dimension table that stores only current state — like a customer's present segment — you must ask yourself: does this table reflect what was true when the event happened, or only what is true right now? That distinction is the heart of this question. Because the customers table holds only each customer's current segment, any join between historical orders and that table stamps every past order with today's segment label. If a customer moved from "Bronze" to "Gold" after placing an order, that old order now appears under "Gold." The segment distribution in your report reflects the present, not the past. Answer A correctly identifies this limitation — the historical classification is driven by the current segment, not the one active at order time. B is wrong because it falsely implies the join captures the segment effective on the order date. That would require a slowly changing dimension (SCD) or a segment-history table — neither of which exists here. One current row per customer does not solve the problem. C describes a different scenario entirely — one where a customer appears in multiple segments simultaneously. Since only the current segment is stored, a customer occupies exactly one segment in the report, not all historical ones. D is dangerously misleading. Segment name uniqueness has no bearing on historical accuracy. A customer's completed order does not freeze their segment; the join will always pull today's value regardless. As a study habit, whenever you see a join to a "current-state" table, immediately flag it as a potential point-in-time accuracy problem — this is a classic data warehouse pitfall that exams love to test.

Question 5

A report labeled January sign-ins uses the following filter:

WHERE occurred_at >= TIMESTAMP '2026-01-01 00:00:00+00' AND occurred_at < TIMESTAMP '2026-02-01 00:00:00+00'

The occurred_at column stores UTC timestamps. Most users are in North American time zones.

Which note most accurately communicates the reporting assumption created by this filter?

  1. January is defined by each user's local calendar, so events near midnight are assigned according to the user's stored time zone.
  2. January includes all events ingested during the month, even when their actual occurrence timestamps fall outside the stated boundaries.
  3. January is defined by UTC boundaries using a half-open interval, so local-calendar January may differ for events near either boundary. (correct answer)
  4. January is defined by UTC boundaries with both endpoints included, so events exactly at the start of February remain in January.
Explanation: When SQL filters define a time period, two things matter: the reference time zone and whether the interval is open or closed at each end. This question asks you to identify what assumption the WHERE clause actually encodes — not what a reader might wish it said. The filter uses >= '2026-01-01 00:00:00+00' and < '2026-02-01 00:00:00+00', which is a classic half-open interval — the start is included, the end is excluded. Both boundaries are expressed in UTC (+00). That means "January" in this report is purely a UTC construct. A user in New York (UTC−5) who logs in at 11:30 PM on December 31st local time is actually at 4:30 AM UTC on January 1st — captured in the report. Conversely, a user who signs in at 11:30 PM on January 31st local time (4:30 AM February 1st UTC) is excluded. This is exactly what C describes: UTC boundaries with a half-open interval, where local-calendar January can differ near either edge. A is wrong because the filter does not reference user time zones at all — there's no per-user conversion happening. B introduces the concept of ingestion time, which has nothing to do with the filter shown; occurred_at reflects event timestamps, not pipeline arrival times. D misreads the interval — the < operator excludes February 1st exactly, so events at that precise moment are not included in January. As a study habit, always parse interval endpoints carefully: < vs <= is a one-character difference with real business consequences, especially when time zones are involved.

Question 6

A dashboard for the previous day's transactions refreshes at 02:00 UTC. The source team's documented service level states that all prior-day files are normally delivered and processed by 06:00 UTC. The query itself succeeds and shows no processing errors.

Which caveat is most appropriate for the dashboard?

  1. The results are complete because a successful query proves that every source file expected for the prior day has already arrived.
  2. The results exclude all prior-day transactions because the dashboard refresh occurs before the source team's completion deadline.
  3. The results are complete for transactions recorded before midnight, since the source service level affects only query execution speed.
  4. The results should be treated as provisional because the refresh precedes the source completion deadline, even though the query ran successfully. (correct answer)
Explanation: When working with dashboards and data pipelines, you need to think beyond whether a query ran successfully and ask whether the underlying data was complete at the time of execution. A successful query only means the SQL executed without errors — it says nothing about whether all expected source records had arrived before the query ran. Here, the dashboard refreshes at 02:00 UTC, but the source team's service level agreement (SLA) allows until 06:00 UTC for all prior-day files to be delivered and processed. That four-hour gap is the critical detail. Because the refresh precedes the completion deadline, some files may legitimately still be in transit or processing when the query runs. The results could be accurate or incomplete — you simply cannot know for certain. That uncertainty is exactly why D is correct: the results should be flagged as provisional, even though the query itself succeeded without errors. A is wrong because it conflates query success with data completeness — a query can run perfectly against an incomplete dataset. B overcorrects by claiming all prior-day transactions are excluded, which is too extreme; many files may have already arrived, so the data is partial, not absent. C mischaracterizes the SLA entirely — the service level governs when data is available, not how fast queries execute. Query execution speed is irrelevant here. A useful rule of thumb: whenever a question mentions a data refresh time and a source delivery deadline, immediately check whether the refresh precedes the deadline. If it does, "provisional" or "incomplete" language is almost always the right caveat — query success is never proof of data completeness.

Question 7

A report totals January orders in a common currency by joining each order to exchange_rates on currency code and rate_date = CURRENT_DATE. The join is an inner join, and the rate table does not always contain a current-date row for every currency.

Which note most accurately communicates the report's assumptions and limitations?

  1. Orders are converted using the rate effective on each order date, while missing historical rates are replaced with the current rate.
  2. Orders are restated using current-date rates, and orders in currencies lacking a current rate are omitted rather than left unconverted. (correct answer)
  3. Orders retain their original currency values because joining on the current date prevents exchange rates from affecting historical transactions.
  4. Orders use January average rates, and currencies with missing rates contribute zero after the inner join is evaluated.
Explanation: When analyzing SQL join behavior, always ask two questions: what data does the join keep, and what data does it discard? An inner join only returns rows where a match exists in both tables. That single rule unlocks this entire question. Here, the report joins orders to exchange_rates on currency code and rate_date = CURRENT_DATE. This means every order is converted using today's exchange rate — not its original order date's rate. If exchange_rates has no current-date row for a given currency, that join condition fails, and the order is silently dropped from the result set entirely. Answer B captures exactly this behavior: current-date rates are applied, and orders in currencies without a current rate are omitted. Answer A is wrong because it claims missing historical rates are "replaced with the current rate" — the opposite of what happens. The report doesn't touch historical rates at all; it only looks up today's rate, and if that's missing, the order disappears rather than being substituted. Answer C is wrong because it misunderstands the join entirely — joining on CURRENT_DATE doesn't freeze values in their original currency; it actively applies a rate conversion to restate amounts. Answer D invents behavior that doesn't exist anywhere in the query: there's no averaging logic, and inner joins don't produce zero-value rows for unmatched records — they produce no rows at all. Your takeaway: on SQL questions, distinguish between what an inner join excludes versus what a LEFT JOIN would preserve as NULL. Silently dropping rows is a common real-world data integrity trap worth flagging in any report design.

Question 8

A support dashboard calculates:

AVG(closed_at - created_at)

The report filters tickets by created_at during the quarter. Open tickets have a null closed_at.

Which annotation most accurately describes the reported average?

  1. Average resolution time for every ticket created during the quarter, with currently open tickets treated as having zero resolution time.
  2. Average resolution time for tickets closed during the quarter, regardless of when those tickets were originally created.
  3. Average ticket age for the quarter, with open tickets measured through the report run time and closed tickets measured through closure.
  4. Average resolution time among quarter-created tickets that have a non-null closure time; open tickets do not contribute to the average. (correct answer)
Explanation: When working with aggregate functions like AVG() in SQL, the critical concept to understand is how NULL values are handled. SQL's AVG() function ignores NULLs entirely — it sums only the non-null values and divides by the count of non-null rows, not the total row count. Here, closed_at - created_at produces a NULL whenever closed_at is null (i.e., the ticket is still open). So AVG() only sees results from tickets that have actually been closed, and those open tickets contribute nothing — not a zero, not a partial value, nothing. The filter on created_at means the pool of tickets considered is restricted to those created during the quarter, but the average is then computed only over the subset with non-null closure times. That makes D the most precise description. A is wrong because it claims open tickets are treated as having zero resolution time. They aren't — a NULL in the average is dropped entirely, not counted as zero. That's a subtle but critical distinction. B is wrong because it misidentifies the filter. The query filters by created_at (when tickets were created), not by closed_at (when they were closed). Tickets closed this quarter but created last quarter would be excluded. C is wrong because it implies open tickets contribute to the average measured through the report run time — but since closed_at is null, those rows are silently excluded, not measured through any alternative endpoint. The key study tip: whenever you see AVG(), ask yourself which rows have NULLs in that column? Those rows vanish from the calculation entirely — a frequent source of misleading aggregate results.

Question 9

A report calculates customer conversion using this structure:

FROM customers AS c JOIN sessions AS s ON s.customer_id = c.customer_id AND s.started_at >= :month_start AND s.started_at < :next_month LEFT JOIN purchases AS p ON p.customer_id = c.customer_id AND p.purchased_at >= :month_start AND p.purchased_at < :next_month

The numerator counts distinct customers with a purchase, and the denominator counts distinct joined customers.

Which description should accompany the conversion rate?

  1. The denominator is all registered customers, because the query begins with customers before applying joins to sessions and purchases.
  2. The denominator is customers with at least one session during the month; registered customers without a session are excluded by the inner join. (correct answer)
  3. The denominator is customers with at least one purchase during the month, because the left join removes customers lacking purchase rows.
  4. The denominator is all customers with either a session or purchase, because the two joins preserve membership from both activity sources.
Explanation: When analyzing a SQL query's denominator, you need to trace which rows survive each join before any aggregation happens. The join type is the critical detail — inner joins filter, left joins preserve. In this query, customers is inner-joined to sessions with a date filter baked directly into the ON clause. That means only customers who have at least one session during the specified month make it past this join. A registered customer with zero sessions in that period simply disappears from the result set — they produce no matching rows and are dropped entirely. The subsequent LEFT JOIN to purchases then operates only on that already-filtered population, preserving all session-having customers regardless of whether they purchased. So the denominator — your count of distinct joined customers — represents customers with at least one session during the month, making B correct. A mistakes the starting table for the denominator. Beginning with customers doesn't mean all customers are counted; it just means you're starting there before filtering. The inner join immediately narrows the set. C misreads the left join's behavior. A LEFT JOIN keeps rows from the left side even when no match exists on the right — it doesn't remove them. Customers without purchases still appear; their purchase columns are simply NULL. D describes a FULL OUTER JOIN or a UNION-style logic. Here, the inner join on sessions creates an intersection, not a union — customers without sessions are excluded regardless of purchase activity. As a study tip: always read join types and ON clause conditions together. A date-filtered inner join is a hidden filter that shapes your dataset before any aggregation occurs.

Question 10

To estimate unique visitors in a very large event table, an analyst applies block-level TABLESAMPLE SYSTEM (10), computes APPROX_COUNT_DISTINCT(visitor_id), and multiplies the result by 10. Events are physically clustered by account, and visitors may generate different numbers of events.

Which disclosure most accurately states the assumptions and limitations of this estimate?

  1. The estimate relies on sampled blocks producing representative visitor inclusion for tenfold scaling, and it also retains the approximation error introduced by the distinct-count algorithm. (correct answer)
  2. The estimate is exact when the sampled event count equals one tenth of all events, because proportional event sampling guarantees proportional visitor coverage across all accounts.
  3. The estimate carries only the distinct-count approximation error, because system-level sampling is automatically uniform across accounts and visitor activity levels regardless of physical clustering.
  4. The estimate is necessarily biased downward, because multiplying a sampled approximate distinct count cannot mathematically produce a value that exceeds the true total visitor count.
Explanation: When combining two sources of statistical error in a sampling estimate, you need to account for each independently — neither cancels the other out. That's exactly what this question tests. Here, the analyst uses TABLESAMPLE SYSTEM, which samples at the block level, not the row level. Because events are physically clustered by account, entire blocks may belong to specific accounts, meaning some accounts could be over- or under-represented in the sample. The scale-up assumption — that 10× the sampled visitor count approximates total visitors — only holds if the sampled blocks happen to capture visitors proportionally across all accounts and activity levels. That's an assumption, not a guarantee. Additionally, APPROX_COUNT_DISTINCT introduces its own algorithmic approximation error (typically via HyperLogLog). So the final estimate inherits both sources of uncertainty: the sampling representativeness assumption and the distinct-count algorithm error. Answer A correctly identifies both limitations without overstating or understating either. Answer B is wrong because proportional event sampling does not guarantee proportional visitor coverage — a visitor who generates many events is more likely to appear in any given sample than a low-activity visitor, distorting the distinct count scaling. Answer C is wrong because block-level sampling is not automatically uniform across accounts when data is physically clustered. Clustering breaks the uniformity assumption that row-level random sampling would provide. Answer D is wrong because the estimate can actually exceed the true count — approximation algorithms and non-representative sampling can both inflate results, not just deflate them. A useful rule of thumb: whenever you see stacked estimation techniques (sampling plus approximation), assume stacked errors — each layer contributes its own assumptions and inaccuracies.