SQL Aggregations SQL Topic exerciseHardVerified guidanceGuided engine comparison

Exact vs Approximate Customer Reach

Return one exact-versus-approximate distinct-customer comparison design for first-half 2025 events.

  • Subqueries
  • Aggregation
  • Filtering
  • Distinct values

Exercise brief

Understand the request

Data platform architect A high-volume event dashboard may trade exactness for bounded latency and memory, but billing and compliance decisions cannot.

Choose an exact or approximate distinct-count strategy for a declared reporting population and decision context.

Return

  • Return exact_customers, approximate_customers in this exact left-to-right order.

Constraints

  • Use non-NULL user_id values from events on or after 2025-01-01 and before 2025-07-01 for both methods.
  • Treat COUNT(DISTINCT user_id) as the exact baseline and name the selected engine’s approximate algorithm or extension.
  • Declare a maximum 2 percent relative-error tolerance and calculate measured error against the exact result on a representative large dataset.
  • Use exact counting for billing, compliance, or other decisions where estimation error is unacceptable.
  • Do not treat one exact result on this tiny teaching fixture as proof of approximate accuracy or performance.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

events

  • event_row_idINTEGER
  • user_idINTEGER
  • occurred_atDATETIME

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

An approximation is meaningful only when its key, filters, NULL policy, and time boundary match the exact baseline.

Hint 2

Separate the SQL method from the decision rule: measure relative error on representative scale, then decide whether the use case permits it.

Hint 3

WITH scoped_events AS ( SELECT /* distinct key */ FROM events WHERE /* shared half-open reporting boundary */ AND /* shared NULL policy */ ) SELECT COUNT(DISTINCT /* key */) AS exact_customers, /* engine-specific approximate aggregate */ AS approximate_customers FROM scoped_events; -- Then calculate /* measured relative error */ and apply /* decision threshold */.

Verified SQL design guidance

Compare the governed engine patterns, trade-offs, and validation criteria.

Reveal solution and explanation
WITH scoped_events AS (SELECT user_id FROM events WHERE occurred_at >= '2025-01-01 00:00:00' AND occurred_at < '2025-07-01 00:00:00' AND user_id IS NOT NULL) SELECT COUNT(DISTINCT user_id) AS exact_customers, APPROX_COUNT_DISTINCT(user_id) AS approximate_customers FROM scoped_events;

Why this works

Correctness: exact and approximate methods are comparable only when they use the same scoped customer population and NULL policy. Edge case: duplicate ingestion, unresolved users, or mismatched time boundaries can create more error than the sketch algorithm itself. Portability: approximate-distinct syntax, algorithms, precision controls, and guarantees are engine-specific; this is a guided design lab until a representative-scale warehouse runtime and error harness exist.

Success check

The design keeps both methods on the same population and explains accuracy, operational benefit, tolerance, and when approximation must not be used.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.