SQL and analytical reasoning

An order has three items and two payment attempts. How many rows can joining both child tables produce?

Six rows for that order. The join pairs every item with every attempt. Aggregate each child to the intended order-level measures before joining, rather than trying to repair inflated sums afterward.

Why does SELECT DISTINCT not reliably fix a fan-out join?

It removes identical result rows, not duplicate contributions to a business measure. Different child attributes keep inflated rows distinct, while legitimate equal-valued rows may disappear. Correct the join grain and key cardinality.

Why can WHERE payments.status = 'paid' remove customers from a LEFT JOIN?

Unmatched right-side values are null, so the predicate is not true and WHERE removes them. Put the status condition in ON when the intended result must retain customers without qualifying payments.

After LEFT JOIN, why can COUNT(*) report one order for a customer with none?

The join retains a null-extended row for that customer. COUNT(*) counts it; COUNT(orders.order_id) counts only non-null matched IDs when that key is non-null in real orders.

How do COUNT(*), COUNT(x), and COUNT(DISTINCT x) treat nulls?

COUNT(*) counts rows, COUNT(x) counts non-null x values, and COUNT(DISTINCT x) counts distinct non-null values. Choose the expression that matches the population, not whichever produces a plausible total.

Why does WHERE x = NULL return no matching rows?

Ordinary equality with null evaluates to unknown, which WHERE discards. Use IS NULL or IS NOT NULL; use IS NOT DISTINCT FROM when a comparison should treat two nulls as equal.

Why can NOT IN unexpectedly exclude every candidate?

If its comparison set contains null and no equality match exists, the result can be unknown rather than true. Use a correlated NOT EXISTS with an explicit matching rule, and decide how null keys should behave.

When is EXISTS better suited than a join for counting eligible customers?

When the question is whether at least one qualifying event exists. EXISTS preserves one row per customer instead of multiplying customers by their matching events; performance still depends on the optimizer and indexes.

How do you count paid orders without removing unpaid orders from other aggregates?

Use COUNT(*) FILTER (WHERE status = 'paid') alongside the other aggregates, or a suitable SUM(CASE ...) expression. Filtering the whole query in WHERE changes the input population for every measure.

Revenue values are 10, 20, and NULL. What does AVG return, and what is the analytical decision?

AVG returns 15 because it ignores nulls. Treating the missing value as zero produces 10, but is valid only if null means no revenue rather than unavailable data.

How should a conversion query handle a zero denominator?

Use a guarded denominator such as conversions::numeric / NULLIF(eligible_users, 0). Return null or an explicit unavailable state; reporting zero falsely implies an observed rate of no conversions.

Why can 3 / 10 produce zero in PostgreSQL?

Both operands are integers, so integer division discards the fractional part. Cast before dividing, such as 3::numeric / 10; casting the already-computed zero afterward cannot recover the fraction.

One group converts 1/2 users and another 9/98. What is the combined conversion rate?

10/100 = 10%, not the unweighted mean of 50% and about 9.18%. Sum numerators and denominators when the estimand is conversion per eligible user across both groups.

When should an analytical filter use WHERE versus HAVING?

WHERE filters input rows before grouping; HAVING filters computed groups. To find customers with at least five paid orders, filter to paid rows first and apply HAVING COUNT(*) >= 5 after grouping by customer.

How does a window aggregate differ from GROUP BY?

A window calculation attaches a result to each surviving row without collapsing the partition. GROUP BY creates one result per group. A window does not repair duplicates created by an earlier join.

How do you select the latest event per user using ROW_NUMBER?

Compute ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY event_time DESC, event_id DESC) in a subquery, then filter rn = 1 outside it. The stable tie-breaker makes equal timestamps deterministic.

For scores 90, 90, 80, how do RANK and DENSE_RANK differ?

RANK assigns 1, 1, 3; DENSE_RANK assigns 1, 1, 2. ROW_NUMBER assigns distinct positions but needs a tie-breaker for reproducible ordering. Choose whether top-k means k rows or k score levels.

Why specify a ROWS frame for a row-by-row running sum?

With ORDER BY, the default frame commonly includes peers sharing the ordering value. Use ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW plus a unique ordering key when each row should advance the total separately.

Why can LAST_VALUE return the current row rather than the partition's final value?

It uses the window frame, whose default may end at the current row and its peers. For the final value of the full partition, explicitly extend the frame to UNBOUNDED FOLLOWING and define deterministic ordering.

Does LAG(revenue) always mean yesterday's revenue?

No. It means the preceding row under the window ordering. If dates are missing, the preceding observation may be several days old. Build a calendar spine or join the exact prior date when the question requires calendar days.

Why is ROWS BETWEEN 6 PRECEDING AND CURRENT ROW not always a seven-day window?

It selects seven rows, not seven calendar days. First create one row per date including missing dates, or use an appropriate time-based frame; also define whether missing activity is zero or unobserved.

What is the denominator for day-30 retention in a signup cohort?

Users in that cohort who have had the opportunity to reach day 30 under the stated observation cutoff. Counting recent users as failures confounds retention with incomplete follow-up.

How do exact-day retention and rolling retention differ?

Exact-day retention asks whether a user returns on a specified day or interval. Rolling retention asks whether they return on or after a threshold within an observation window. State time zone, qualifying activity, and follow-up horizon.

Why is counting users with both view and purchase events insufficient for a conversion funnel?

The purchase may precede the view or belong to another journey. Require a qualifying view followed by a purchase within the defined window, linked by the intended user or session identity.

A user starts checkout three times and buys once. What must the metric define?

Whether the unit is user, session, or checkout attempt, and which start the purchase is attributed to. Deduplicate at that unit before computing conversion; different units answer different questions.

How can SQL identify sessions separated by more than 30 minutes?

Use LAG(event_time) per user, flag the first event or a gap above the threshold, then cumulatively sum the flag to assign session IDs. Define tie order and whether exactly 30 minutes starts a new session.

How do you group events into local business days without losing the original instant?

Keep the event instant in a timezone-aware representation, convert to the business time zone for grouping, then derive the date. Daylight-saving changes mean a local day need not contain exactly 24 hours.

Why use event_time >= start AND event_time < end for adjacent periods?

Half-open intervals assign a boundary event to exactly one period. Inclusive BETWEEN on both adjacent ranges can double count the shared endpoint, and guessed end-of-day precision can miss events.

Yesterday's count changed today with no query edit. What should you check?

Arrival lag, corrections, deletions, identity merges, and mutable dimension joins. Record the data cutoff and source versions; distinguish an event-time metric from a snapshot of what was known when first reported.

What prevents a historical order from joining to today's customer tier?

Join to the tier version whose validity interval contains the order time, with non-overlapping intervals per customer. If availability matters, also ensure that version was known at the analytical decision time.

Which checks protect an as-of join against duplicated or missing matches?

Test interval overlaps, gaps, null endpoints, and boundary conventions per entity. Verify each fact has at most one valid match, and separately count facts with no match rather than silently dropping them.

When should event consolidation use UNION ALL instead of UNION?

When all rows should be retained and duplicates handled by explicit event identity later. UNION removes identical rows and adds deduplication work, potentially erasing distinct events with the same projected values.

How do you deduplicate retried events without deleting real repeats?

Use a stable event ID and a documented winner rule, such as the latest valid ingestion version. Two purchases with equal amount and timestamp may be real separate events; attribute equality is not event identity.

Why can expanding a JSON array change a customer-level metric?

One source row becomes multiple element rows. Keep the original entity key, define how empty arrays behave, and aggregate elements back to the required grain before counting customers or summing parent measures.

Why can two warehouses report different medians for the same values?

They may use continuous interpolation, discrete selection, or approximate quantile algorithms, with different null handling. State the percentile definition and check a small hand-calculated sample before comparing dashboards.

Can you average regional p95 latencies to obtain global p95?

No. Percentiles are not additive. Combine the underlying observations or compatible mergeable distribution summaries with appropriate weights; regional p95 values alone do not identify the global percentile.

How do you distinguish zero sales from a failed daily load?

Join expected dates and source completion status to observed totals. Only fill missing totals with zero after verifying the source was complete and genuinely had no qualifying transactions.

Conversion rose in every region but fell overall. What query would investigate this?

Compute region-specific numerators, denominators, and traffic shares in each period. A shift toward lower-conversion regions can lower the aggregate despite within-region gains; compare a fixed-mix standardized rate as a diagnostic.

A new revenue query differs from finance by 4%. How do you localize the difference?

Reconcile the same date range and currency by progressively finer keys: day, source, status, then transaction. Check refunds, taxes, recognition dates, duplicates, and cutoff rules before changing the formula to match a total.

What does an anti-join reveal during dataset reconciliation?

Keys present in one dataset but absent from the other. Run it in both directions and inspect missing-key counts and values; matching totals can hide different records or offsetting errors.

What SQL check verifies a claimed unique composite key?

GROUP BY the key columns and use HAVING COUNT(*) > 1. Check null-key policy separately, and run the test at the exact stage where uniqueness is assumed, not only on the raw source.

Which aggregate checks help catch a join that changes the wrong population?

Compare row counts, distinct entity counts, unmatched-key counts, and conserved measures before and after the join. Expected one-to-one enrichment should preserve these quantities unless a documented filter changes the population.

A query plan estimates 100 rows but processes 10 million. Why does that matter?

Bad cardinality estimates can drive unsuitable joins, memory allocation, and scan choices. Inspect statistics, skew, correlated predicates, and join conditions; EXPLAIN ANALYZE executes the query, so use care with costly or modifying statements.

Why can wrapping a partition timestamp in a function increase scan cost?

The engine may fail to derive a usable partition bound from the expression. Filter the raw column with explicit start/end bounds when possible, and verify pruning in the actual plan rather than assuming every function disables it.

Does putting a slow subquery in a CTE make it execute only once?

Not universally. Engines may inline, materialize, or otherwise optimize CTEs, and behavior can depend on version and references. Use CTEs for clear stages and inspect execution when reuse or optimization barriers matter.

Why does randomly sampling events overrepresent active users?

Users with more events have more opportunities to enter the sample. Sample user IDs first when estimating user-level quantities, then include their relevant events; use weights when selection probabilities differ.

What should accompany a reusable SQL metric definition?

The grain, eligible population, numerator and denominator, time zone, event/cutoff rules, exclusions, source versions, owner, and validation examples. A query text alone leaves business semantics open to reinterpretation.

What belongs in a hand-checkable fixture for a retention query?

Users with no return, multiple returns, boundary-time returns, late events, duplicate events, and insufficient follow-up. Write expected cohort counts by hand so the fixture tests semantics rather than reproducing the query logic.

A query returns a precise 12.43% rate. What must be checked before presenting it?

Confirm denominator scope, source completeness, grain, join cardinality, and the metric definition. Numerical precision is not inferential certainty; include sample size and uncertainty when the rate is used to generalize beyond the observed data.