SQL Quiz: Defining And Implementing Metrics
10 questions · exam conditions
0:00
Defining And Implementing MetricsQuestion 1 of 10

A retailer stores orders(order_id, status), order_items(order_id, quantity, unit_price), and refunds(refund_id, order_id, amount, refund_status). Net merchandise revenue is defined for paid orders as total item quantity times unit price, minus successful refund amounts. An order may have multiple items and multiple refunds, and paid orders without refunds must remain in the metric.

Which SQL pattern calculates net merchandise revenue without introducing join multiplication?

SELECT SUM(i.quantity * i.unit_price) - COALESCE(SUM(r.amount), 0) FROM orders o JOIN order_items i ON i.order_id = o.order_id LEFT JOIN refunds r ON r.order_id = o.order_id AND r.refund_status = 'successful' WHERE o.status = 'paid';
SELECT SUM(i.quantity * i.unit_price - COALESCE(r.amount, 0)) FROM orders o JOIN order_items i ON i.order_id = o.order_id LEFT JOIN refunds r ON r.order_id = o.order_id AND r.refund_status = 'successful' WHERE o.status = 'paid';
WITH item_totals AS (SELECT order_id, SUM(quantity * unit_price) AS gross FROM order_items GROUP BY order_id), refund_totals AS (SELECT order_id, SUM(amount) AS refunded FROM refunds WHERE refund_status = 'successful' GROUP BY order_id) SELECT SUM(i.gross - COALESCE(r.refunded, 0)) FROM orders o JOIN item_totals i ON i.order_id = o.order_id LEFT JOIN refund_totals r ON r.order_id = o.order_id WHERE o.status = 'paid';
WITH item_totals AS (SELECT order_id, SUM(quantity * unit_price) AS gross FROM order_items GROUP BY order_id), refund_totals AS (SELECT order_id, SUM(amount) AS refunded FROM refunds WHERE refund_status = 'successful' GROUP BY order_id) SELECT SUM(i.gross - r.refunded) FROM orders o JOIN item_totals i ON i.order_id = o.order_id JOIN refund_totals r ON r.order_id = o.order_id WHERE o.status = 'paid';
← Back to quizzes

SQL Quiz

SQL Quiz: Defining And Implementing Metrics

Practice Defining And Implementing Metrics 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 Defining And Implementing Metrics, 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

A retailer stores orders(order_id, status), order_items(order_id, quantity, unit_price), and refunds(refund_id, order_id, amount, refund_status). Net merchandise revenue is defined for paid orders as total item quantity times unit price, minus successful refund amounts. An order may have multiple items and multiple refunds, and paid orders without refunds must remain in the metric.

Which SQL pattern calculates net merchandise revenue without introducing join multiplication?

  1. SELECT SUM(i.quantity * i.unit_price) - COALESCE(SUM(r.amount), 0) FROM orders o JOIN order_items i ON i.order_id = o.order_id LEFT JOIN refunds r ON r.order_id = o.order_id AND r.refund_status = 'successful' WHERE o.status = 'paid';
  2. SELECT SUM(i.quantity * i.unit_price - COALESCE(r.amount, 0)) FROM orders o JOIN order_items i ON i.order_id = o.order_id LEFT JOIN refunds r ON r.order_id = o.order_id AND r.refund_status = 'successful' WHERE o.status = 'paid';
  3. WITH item_totals AS (SELECT order_id, SUM(quantity * unit_price) AS gross FROM order_items GROUP BY order_id), refund_totals AS (SELECT order_id, SUM(amount) AS refunded FROM refunds WHERE refund_status = 'successful' GROUP BY order_id) SELECT SUM(i.gross - COALESCE(r.refunded, 0)) FROM orders o JOIN item_totals i ON i.order_id = o.order_id LEFT JOIN refund_totals r ON r.order_id = o.order_id WHERE o.status = 'paid'; (correct answer)
  4. WITH item_totals AS (SELECT order_id, SUM(quantity * unit_price) AS gross FROM order_items GROUP BY order_id), refund_totals AS (SELECT order_id, SUM(amount) AS refunded FROM refunds WHERE refund_status = 'successful' GROUP BY order_id) SELECT SUM(i.gross - r.refunded) FROM orders o JOIN item_totals i ON i.order_id = o.order_id JOIN refund_totals r ON r.order_id = o.order_id WHERE o.status = 'paid';
Explanation: Whenever you join multiple one-to-many relationships in SQL — like orders → items and orders → refunds — you risk join multiplication: each item row fans out against each refund row for the same order, inflating your aggregates before SUM ever runs. The safe pattern is to pre-aggregate each child table independently before joining. That way, each order contributes exactly one item-total row and one refund-total row, so the join is many-to-one and multiplication is impossible. Option C does exactly this. The item_totals CTE collapses every order's items into a single gross value, and refund_totals collapses every order's successful refunds into a single refunded value. The final join is clean: one row per order from each CTE, joined to orders. The LEFT JOIN on refund_totals correctly preserves paid orders that have no refunds, and COALESCE(r.refunded, 0) handles the NULL that results. Option A joins raw order_items and raw refunds simultaneously. For an order with 3 items and 2 refunds, you get 6 rows — SUM(quantity * unit_price) doubles and SUM(amount) triples. Both aggregates are corrupted. Option B has the same structural flaw as A — the flat join still multiplies rows — but tries to "fix" it by subtracting per-row inside SUM. This doesn't remove the duplication; it just distributes the error differently. Option D uses the correct CTE structure but uses an INNER JOIN on refund_totals, which silently drops paid orders that have zero refunds — violating the stated requirement. Study tip: Any time you're aggregating across two or more child tables, pre-aggregate each one in a CTE or subquery first. Flat multi-join aggregations are one of the most common sources of silent, hard-to-detect data errors in SQL.

Question 2

A PostgreSQL database has users(user_id, signup_ts) and activity(user_id, activity_ts). Day-7 retention for the January 2026 signup cohort is defined as the percentage of cohort users with at least one activity timestamp in the half-open interval from exactly seven days after signup through exactly eight days after signup. Users without qualifying activity remain in the denominator.

Which query correctly calculates Day-7 retention?

  1. SELECT COUNT(DISTINCT a.user_id)::numeric / NULLIF(COUNT(DISTINCT u.user_id), 0) FROM users u JOIN activity a ON a.user_id = u.user_id WHERE u.signup_ts >= DATE '2026-01-01' AND u.signup_ts < DATE '2026-02-01' AND a.activity_ts >= u.signup_ts + INTERVAL '7 days' AND a.activity_ts < u.signup_ts + INTERVAL '8 days';
  2. SELECT AVG(CASE WHEN EXISTS (SELECT 1 FROM activity a WHERE a.user_id = u.user_id AND a.activity_ts >= u.signup_ts AND a.activity_ts < u.signup_ts + INTERVAL '7 days') THEN 1.0 ELSE 0.0 END) FROM users u WHERE u.signup_ts >= DATE '2026-01-01' AND u.signup_ts < DATE '2026-02-01';
  3. SELECT AVG(CASE WHEN EXISTS (SELECT 1 FROM activity a WHERE a.user_id = u.user_id AND a.activity_ts >= u.signup_ts + INTERVAL '7 days' AND a.activity_ts < u.signup_ts + INTERVAL '8 days') THEN 1.0 ELSE 0.0 END) FROM users u WHERE u.signup_ts >= DATE '2026-01-01' AND u.signup_ts < DATE '2026-02-01'; (correct answer)
  4. SELECT COUNT(*)::numeric / NULLIF(COUNT(DISTINCT u.user_id), 0) FROM users u LEFT JOIN activity a ON a.user_id = u.user_id AND a.activity_ts >= u.signup_ts + INTERVAL '7 days' AND a.activity_ts < u.signup_ts + INTERVAL '8 days' WHERE u.signup_ts >= DATE '2026-01-01' AND u.signup_ts < DATE '2026-02-01';
Explanation: When calculating cohort retention, you need to keep every cohort member in the denominator — even those who never returned — while only counting those with qualifying activity in the numerator. Day-7 retention specifically means activity in the half-open interval [signup + 7 days, signup + 8 days), not just any activity after signup. Option C gets this exactly right. By querying directly from users with a WHERE clause filtering the January 2026 cohort, every cohort user contributes a row. The EXISTS subquery checks whether that user has activity in the precise Day-7 window (>= signup_ts + INTERVAL '7 days' AND < signup_ts + INTERVAL '8 days'). The AVG of a 1.0/0.0 expression then computes the retention rate as a fraction automatically — retained users get 1.0, everyone else gets 0.0, and the average equals the proportion retained. Option A uses an INNER JOIN, which silently drops users with no qualifying activity from both the numerator and denominator. This inflates the result — you'd be dividing retained users only by retained users, not the full cohort. Option B checks the wrong window entirely. It looks for activity between signup and Day 7 (< signup_ts + INTERVAL '7 days'), which measures early engagement, not Day-7 retention. Option D uses COUNT(*) in the numerator, which counts all matched activity rows rather than distinct retained users. A user with multiple Day-7 activities would be counted multiple times, overstating the numerator. As a study tip: whenever a retention metric problem says "users without qualifying activity remain in the denominator," immediately reach for a LEFT JOIN or correlated EXISTS — anything that preserves non-matching rows — and audit every candidate query for silent inner-join filtering.

Question 3

In PostgreSQL, payments(payment_id, amount, paid_at) stores paid_at as timestamptz, with instants persisted in UTC. Daily revenue must be grouped by the calendar date observed in the America/New_York time zone. The report covers June, when daylight saving time is in effect, but the implementation must also remain correct year-round.

Which grouping expression correctly assigns each payment to its New York calendar date?

  1. GROUP BY paid_at::date
  2. GROUP BY (paid_at - INTERVAL '5 hours')::date
  3. GROUP BY (paid_at AT TIME ZONE 'America/New_York')::date (correct answer)
  4. GROUP BY (paid_at AT TIME ZONE 'UTC')::date
Explanation: When working with time zone conversions in PostgreSQL, the key question is always: at what point in the pipeline does the conversion happen, and does it handle DST correctly? timestamptz values are stored internally as UTC instants. To group by a user-facing calendar date in a specific time zone, you must convert the UTC instant into that zone's local time before casting to date. The correct way to do this in PostgreSQL is the AT TIME ZONE operator with a named time zone: paid_at AT TIME ZONE 'America/New_York'. This tells PostgreSQL to use the IANA time zone database, which automatically applies the correct UTC offset — either UTC−5 in standard time or UTC−4 during daylight saving time. Casting the result to date then gives you the local calendar date. That's exactly what C does, making it correct. A is wrong because paid_at::date truncates the UTC timestamp directly to a date, completely ignoring the New York offset. A payment made at 1 AM New York time would fall on the previous UTC date. B hardcodes a −5 hour offset, which is only valid during Eastern Standard Time. During June — when DST is active — New York is at UTC−4, so this expression is off by one hour and will misassign payments near midnight. D applies AT TIME ZONE 'UTC' to a timestamptz, which simply strips the time zone metadata without any conversion, producing the same wrong result as A. As a study tip: whenever you see a DST-sensitive grouping problem, immediately eliminate any answer with a hardcoded numeric offset — named time zones are always the safe, year-round-correct choice.

Question 4

A company defines April logo churn as the percentage of customers who were active at the start of April but were not active at the start of May. A customer is active at an instant if at least one subscription is active then. Customers may have multiple subscriptions, may churn and reactivate during April, and customers first acquired during April are not part of the denominator.

Which implementation most accurately follows this metric definition?

  1. Count distinct customers with any cancellation timestamp in April, divided by distinct customers active at the start of April.
  2. Build distinct customer sets active at each boundary; count customers in the April-start set but not the May-start set, divided by the April-start set size. (correct answer)
  3. Subtract the May-start active-customer count from the April-start active-customer count, then divide the difference by the April-start count.
  4. Build distinct customer sets active at each boundary; count customers absent from the May-start set, divided by the May-start active-customer set size.
Explanation: When measuring churn, precision matters enormously — small definitional differences produce completely different numbers. The key here is translating the business definition into exact SQL logic: churn = customers active at April 1 but not at May 1, divided by customers active at April 1. Answer B does exactly this. You build two distinct customer sets — one snapshot at April 1, one at May 1 — then use a set difference (think EXCEPT or a LEFT JOIN ... WHERE IS NULL) to find customers present at April 1 but absent at May 1. That count becomes your numerator, and the April 1 set size is your denominator. This precisely mirrors the definition. Answer A fails because having a cancellation event in April doesn't mean a customer churned. They could have cancelled one subscription but kept another active, or they could have reactivated before May 1. Counting cancellation timestamps conflates events with state. Answer C is tempting but wrong. Subtracting May-start count from April-start count measures net change, not individual churn. If 10 customers churned but 8 new customers activated in April, this method yields a numerator of 2 — severely undercounting the 10 true churners. New acquisitions during April mask the real churn. Answer D uses the wrong denominator. Dividing by the May-start customer count instead of the April-start count contradicts the definition, which explicitly anchors the denominator to April-start customers. Strategy tip: When a metric involves "customers who were X but not Y," always think set operations — snapshots and differences — not event counts or arithmetic subtraction.

Question 5

The table inspection_summary(site_id, defect_count, inspected_count) contains one row per site for a reporting period. The enterprise defect rate is defined as total detected defects divided by total inspected units, not as an equally weighted average of site-level rates. Sites may inspect different numbers of units.

Which expression correctly implements the enterprise defect rate while avoiding integer truncation and division by zero?

  1. AVG(defect_count * 1.0 / NULLIF(inspected_count, 0))
  2. SUM(defect_count) * 1.0 / NULLIF(SUM(inspected_count), 0) (correct answer)
  3. SUM(defect_count) * 1.0 / NULLIF(COUNT(inspected_count), 0)
  4. AVG(defect_count) * 1.0 / NULLIF(SUM(inspected_count), 0)
Explanation: When calculating an enterprise-wide rate across groups of different sizes, you must aggregate totals first, then divide — not average the individual rates. This is a weighted aggregation problem, and the distinction matters enormously when group sizes vary. The enterprise defect rate is defined as: \text{Rate} = \frac{\sum \text{defect_count}}{\sum \text{inspected_count}} Option B implements this exactly: SUM(defect_count) * 1.0 / NULLIF(SUM(inspected_count), 0). Multiplying by 1.0 promotes the numerator to a float before division, preventing integer truncation. Wrapping the denominator in NULLIF(..., 0) returns NULL instead of triggering a division-by-zero error if no units were inspected. This is your correct answer. Option A computes each site's individual rate first, then averages those rates with AVG(...). This equally weights every site regardless of how many units it inspected — a classic Simpson's Paradox trap. A site that inspected 10 units gets the same weight as one that inspected 10,000, producing a distorted enterprise figure. Option C divides total defects by COUNT(inspected_count), which counts the number of rows (sites), not the total units inspected. This is a fundamental confusion between COUNT and SUM — you'd essentially be computing defects per site, not per inspected unit. Option D mixes AVG(defect_count) with SUM(inspected_count), combining two incompatible aggregation levels. The numerator is an average across sites while the denominator is a grand total, producing a meaningless ratio. Study tip: Whenever a question asks for a rate across unequal groups, your reflex should be sum numerator, sum denominator, then divide — never average the pre-computed rates.

Question 6

The table subscriptions(subscription_id, customer_id, started_at, canceled_at) stores effective timestamps. A subscription is active at timestamp :as_of if it has started by that instant and its cancellation, when present, becomes effective exactly at canceled_at. The metric counts distinct customers with at least one active subscription.

Which predicate correctly identifies subscriptions active at :as_of?

  1. started_at < :as_of AND (canceled_at >= :as_of OR canceled_at IS NULL)
  2. started_at <= :as_of AND (canceled_at >= :as_of OR canceled_at IS NULL)
  3. started_at < :as_of AND (canceled_at > :as_of OR canceled_at IS NULL)
  4. started_at <= :as_of AND (canceled_at > :as_of OR canceled_at IS NULL) (correct answer)
Explanation: When filtering for "point-in-time active" records, you need to think carefully about boundary inclusivity on both ends of a time interval. Ask yourself: at exactly :as_of, should a subscription that just started count? Should one that just canceled count? The passage tells you cancellation becomes effective exactly at canceled_at — meaning at that precise instant the subscription is no longer active. So canceled_at = :as_of should exclude the subscription. Meanwhile, a subscription that started exactly at :as_of has started by that instant and should include it. This gives you the interval [\text{started_at},\ \text{canceled_at}) — closed on the left, open on the right. Translated into SQL: started_at <= :as_of AND canceled_at > :as_of. For subscriptions with no cancellation, canceled_at IS NULL handles the open-ended case. That reasoning confirms D is correct. A fails on both boundaries: started_at < :as_of wrongly excludes subscriptions that started exactly at :as_of, and canceled_at >= :as_of wrongly includes subscriptions canceled exactly at :as_of. B gets the left boundary right (<=) but still uses canceled_at >= :as_of, which incorrectly counts a subscription whose cancellation is effective right at :as_of as still active. C fixes the cancellation side (canceled_at > :as_of) but uses started_at < :as_of, excluding subscriptions that began precisely at :as_of. As a rule of thumb: when a boundary is described as "effective at" a timestamp, treat it as a half-open interval — inclusive on start, exclusive on end. This pattern appears frequently in SCD and event-sourcing queries.

Question 7

A PostgreSQL database contains campaign_events(user_id, event_type, event_ts) and orders(order_id, user_id, order_ts, status). The January conversion rate is defined as the percentage of users with at least one campaign_view during January 2026 who placed at least one completed order within the half-open interval beginning at that user's first January view and ending seven days later. Each user must contribute at most once to both the numerator and denominator.

Which query correctly implements the January conversion rate?

  1. SELECT COUNT(DISTINCT o.user_id)::numeric / NULLIF(COUNT(DISTINCT e.user_id), 0) FROM campaign_events e JOIN orders o ON o.user_id = e.user_id WHERE e.event_type = 'campaign_view' AND e.event_ts >= DATE '2026-01-01' AND e.event_ts < DATE '2026-02-01' AND o.status = 'completed';
  2. WITH exposed AS (SELECT user_id, MIN(event_ts) AS first_view FROM campaign_events WHERE event_type = 'campaign_view' AND event_ts >= DATE '2026-01-01' AND event_ts < DATE '2026-02-01' GROUP BY user_id) SELECT AVG(CASE WHEN EXISTS (SELECT 1 FROM orders o WHERE o.user_id = e.user_id AND o.status = 'completed' AND o.order_ts >= e.first_view AND o.order_ts < e.first_view + INTERVAL '7 days') THEN 1.0 ELSE 0.0 END) FROM exposed e; (correct answer)
  3. WITH exposed AS (SELECT user_id, MIN(event_ts) AS first_view FROM campaign_events WHERE event_type = 'campaign_view' AND event_ts >= DATE '2026-01-01' AND event_ts < DATE '2026-02-01' GROUP BY user_id) SELECT COUNT(*)::numeric / NULLIF(COUNT(DISTINCT e.user_id), 0) FROM exposed e JOIN orders o ON o.user_id = e.user_id AND o.status = 'completed' AND o.order_ts < e.first_view + INTERVAL '7 days';
  4. WITH exposed AS (SELECT user_id, MIN(event_ts) AS first_view FROM campaign_events WHERE event_type = 'campaign_view' AND event_ts >= DATE '2026-01-01' AND event_ts < DATE '2026-02-01' GROUP BY user_id) SELECT AVG(CASE WHEN EXISTS (SELECT 1 FROM orders o WHERE o.user_id = e.user_id AND o.status = 'completed' AND o.order_ts >= e.first_view AND o.order_ts <= e.first_view + INTERVAL '7 days') THEN 1.0 ELSE 0.0 END) FROM exposed e;
Explanation: When computing a conversion rate tied to a user-level window, you need three things to be exactly right: the denominator must count unique exposed users, the numerator must count each converting user at most once, and the time window must be a half-open interval — meaning the start is inclusive and the end is exclusive (>= first_view and < first_view + 7 days). Option B nails all three. The CTE isolates each exposed user and their first January view. The outer query uses AVG over a 0/1 indicator, which is mathematically identical to converted_users / total_exposed_users — each user contributes exactly one row, so no double-counting is possible. The EXISTS subquery enforces >= first_view AND < first_view + INTERVAL '7 days', correctly implementing the half-open interval described in the problem. Option A is the most tempting distractor, but it skips the 7-day window entirely — it counts any completed order ever linked to an exposed user, inflating the numerator. It also never computes first_view, so it can't enforce the window even in principle. Option C joins exposed directly to orders, which can produce multiple rows per user when a user has several orders. COUNT(*) then counts order rows, not users, breaking the "at most once" requirement. It also omits o.order_ts >= e.first_view, so orders placed before the first view could be counted. Option D is almost correct but uses a closed interval (<= first_view + INTERVAL '7 days'), which includes a moment 7 full days later — violating the half-open requirement. Your study tip: whenever a problem specifies a "half-open interval," immediately verify that your WHERE clause uses >= on one side and strict < on the other — a single <= vs < distinction can quietly invalidate an otherwise correct query.

Question 8

A PostgreSQL database contains orders(order_id, paid_at) and shipments(shipment_id, order_id, shipped_at). The fulfillment metric is the median number of hours from payment to first shipment among orders that have at least one shipment. An order can have several shipment records.

Which query calculates the specified median at the correct metric grain?

  1. WITH first_shipments AS (SELECT o.order_id, o.paid_at, MIN(s.shipped_at) AS first_shipped_at FROM orders o JOIN shipments s ON s.order_id = o.order_id GROUP BY o.order_id, o.paid_at) SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (first_shipped_at - paid_at)) / 3600.0) FROM first_shipments; (correct answer)
  2. SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (s.shipped_at - o.paid_at)) / 3600.0) FROM orders o JOIN shipments s ON s.order_id = o.order_id;
  3. WITH last_shipments AS (SELECT o.order_id, o.paid_at, MAX(s.shipped_at) AS shipped_at FROM orders o JOIN shipments s ON s.order_id = o.order_id GROUP BY o.order_id, o.paid_at) SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM (shipped_at - paid_at)) / 3600.0) FROM last_shipments;
  4. WITH first_shipments AS (SELECT o.order_id, o.paid_at, MIN(s.shipped_at) AS first_shipped_at FROM orders o JOIN shipments s ON s.order_id = o.order_id GROUP BY o.order_id, o.paid_at) SELECT AVG(EXTRACT(EPOCH FROM (first_shipped_at - paid_at)) / 3600.0) FROM first_shipments;
Explanation: When calculating a median over a business metric, you must first establish the correct grain — one row per unit of analysis — before aggregating. Here, the unit is one order, and the metric is hours from payment to first shipment. This means you need to collapse multiple shipment rows per order before feeding values into the median function. Answer A does exactly this: the CTE uses MIN(s.shipped_at) to find each order's first shipment, grouping by order_id so you get one row per order. Then PERCENTILE_CONT(0.5) computes the true statistical median of the resulting hour differences. This is correct in grain, timing choice, and aggregation method. Answer B skips the pre-aggregation step entirely, joining orders to all shipment rows and passing every row directly into PERCENTILE_CONT. An order with three shipments contributes three values instead of one, inflating and distorting the distribution — a classic fan-out problem. Answer C uses MAX(s.shipped_at) rather than MIN, so it measures time to the last shipment per order, not the first. The grain is correct (one row per order), but the business logic is wrong — you'd be measuring fulfillment completion, not fulfillment speed. Answer D has the right CTE from A but replaces PERCENTILE_CONT(0.5) with AVG. The average and median are different statistics; the question explicitly asks for the median, so using AVG fails to meet the specification regardless of how clean the data setup is. The key study takeaway: always ask "what is one row in my final dataset supposed to represent?" before writing an aggregation. Resolve fan-outs in a CTE first, then aggregate.

Question 9

A PostgreSQL database contains tickets(ticket_id, created_at) and messages(message_id, ticket_id, sent_at, sender_role, is_public). The May SLA attainment metric is the percentage of tickets created in May 2026 that received at least one public agent message within 24 hours after creation. Tickets with no qualifying response count as misses, and each ticket has equal weight.

Which query correctly calculates SLA attainment?

  1. SELECT AVG(CASE WHEN EXISTS (SELECT 1 FROM messages m WHERE m.ticket_id = t.ticket_id AND m.sent_at < t.created_at + INTERVAL '24 hours') THEN 1.0 ELSE 0.0 END) FROM tickets t WHERE t.created_at >= DATE '2026-05-01' AND t.created_at < DATE '2026-06-01';
  2. SELECT AVG(CASE WHEN m.sent_at < t.created_at + INTERVAL '24 hours' THEN 1.0 ELSE 0.0 END) FROM tickets t LEFT JOIN messages m ON m.ticket_id = t.ticket_id AND m.sender_role = 'agent' AND m.is_public WHERE t.created_at >= DATE '2026-05-01' AND t.created_at < DATE '2026-06-01';
  3. SELECT AVG(CASE WHEN EXISTS (SELECT 1 FROM messages m WHERE m.ticket_id = t.ticket_id AND m.sender_role = 'agent' AND m.is_public AND m.sent_at >= t.created_at AND m.sent_at < t.created_at + INTERVAL '24 hours') THEN 1.0 ELSE NULL END) FROM tickets t WHERE t.created_at >= DATE '2026-05-01' AND t.created_at < DATE '2026-06-01';
  4. SELECT AVG(CASE WHEN EXISTS (SELECT 1 FROM messages m WHERE m.ticket_id = t.ticket_id AND m.sender_role = 'agent' AND m.is_public AND m.sent_at >= t.created_at AND m.sent_at < t.created_at + INTERVAL '24 hours') THEN 1.0 ELSE 0.0 END) FROM tickets t WHERE t.created_at >= DATE '2026-05-01' AND t.created_at < DATE '2026-06-01'; (correct answer)
Explanation: When calculating a percentage metric like SLA attainment, you need to count qualifying tickets as 1, non-qualifying tickets as 0, and divide by the total ticket count — including misses. This is exactly what AVG() over a binary 0/1 expression does. The traps in this question all involve either miscounting which messages qualify or mishandling the miss cases. D is correct because it checks each May ticket for the existence of a message that satisfies all three conditions simultaneously: sender_role = 'agent', is_public = true, and sent_at falling within the [created_at, created_at + 24h) window. When no such message exists, it returns 0.0, so missed tickets pull the average down correctly. AVG() over 1s and 0s across all May tickets gives exactly the attainment percentage. A is wrong because the EXISTS subquery omits both the sender_role = 'agent' and is_public filters. Any message — even a customer reply or a private note — would count as an SLA hit, inflating attainment. B is wrong for two reasons: the LEFT JOIN can produce multiple rows per ticket (one per qualifying message), so tickets with several agent messages get counted multiple times, distorting the average. Also, when a ticket has no qualifying message, the sent_at is NULL, and NULL < anything evaluates to NULL rather than 0, so those misses silently disappear from the calculation instead of being penalized. C looks close but uses ELSE NULL instead of ELSE 0.0. Since AVG() ignores NULLs, miss tickets are excluded from the denominator entirely — turning the metric into "attainment rate among tickets that had some response," not among all May tickets. Study tip: Whenever a question asks for a rate across a full population, confirm your query produces exactly one row per population member and that non-qualifying members contribute 0 (not NULL) to the average.

Question 10

The table activity(user_id, activity_date) may contain several rows for the same user on the same date. A table report_dates(report_date) contains each date to report. For every report date, rolling seven-day active users means distinct users with activity on that date or any of the preceding six calendar dates.

Which query correctly produces the rolling seven-day active-user metric?

  1. SELECT d.report_date, (SELECT COUNT(DISTINCT a.user_id) FROM activity a WHERE a.activity_date >= d.report_date - 6 AND a.activity_date <= d.report_date) AS active_users FROM report_dates d; (correct answer)
  2. SELECT d.report_date, SUM(x.daily_users) AS active_users FROM report_dates d JOIN (SELECT activity_date, COUNT(DISTINCT user_id) AS daily_users FROM activity GROUP BY activity_date) x ON x.activity_date BETWEEN d.report_date - 6 AND d.report_date GROUP BY d.report_date;
  3. SELECT d.report_date, COUNT(DISTINCT a.user_id) AS active_users FROM report_dates d LEFT JOIN activity a ON a.activity_date > d.report_date - 6 AND a.activity_date <= d.report_date GROUP BY d.report_date;
  4. SELECT activity_date, COUNT(DISTINCT user_id) OVER (ORDER BY activity_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS active_users FROM activity WHERE activity_date IN (SELECT report_date FROM report_dates);
Explanation: When computing rolling window metrics in SQL, you need to ask: what exactly defines the window for each report date, and does my query honor that definition without double-counting? The seven-day window means distinct users active between report_date - 6 and report_date, inclusive. A gets this exactly right. The correlated subquery independently counts DISTINCT user_id values within the full seven-day date range for each report date. Distinct users who were active on multiple days within the window are counted only once, which is precisely the metric definition. This is the correct answer. B is a tempting but flawed approach. It aggregates pre-computed daily distinct counts using SUM(). The problem: a user active on three separate days within the window gets counted three times — once per day. Summing daily distinct counts is not the same as counting distinct users across the combined window. C uses COUNT(DISTINCT a.user_id) with a LEFT JOIN, which sounds correct, but the join condition uses a.activity_date > d.report_date - 6 (strict greater-than) instead of >=. This excludes activity exactly six days before the report date, shrinking the window to only six days instead of seven. D applies a window function over the activity table directly. The ROWS BETWEEN 6 PRECEDING AND CURRENT ROW frame counts six preceding rows, not six preceding calendar days — so if multiple rows share a date or dates are skipped, the window is wrong. Study tip: When you see rolling distinct-count problems, always verify two things: the boundary conditions (strict vs. inclusive) and whether your aggregation avoids double-counting across overlapping windows.