SQL Data Sampling Interview Questions: Random Rows, Stratification, and Reproducibility

SQL data sampling interview questions ask more than how to put a random function in a query. The difficult part is defining what can be selected, which units should have equal inclusion probability, and what another analyst needs to reproduce the result.

Quick answer: define the eligible population and sampling unit first. Choose between an exact-size row sample, probabilistic row selection, block sampling, and stratified allocation. Then record the source snapshot, filters, algorithm, seed or hash rule, and selected identifiers. A small query result is not automatically representative or reproducible.

Evidence boundary: PostgreSQL and BigQuery documentation support the engine-specific mechanics below. Country counts, conversion rates, hash-selection fixtures, and preparation advice are original examples. They are not candidate reports or claims about an employer's interview format.

Start with Implement Stratified Sampling by Country. Explain the population, allocation, and intended estimate before writing the SQL; those choices determine whether a correct query answers the right question.

SQL data sampling interview questions begin with the unit

Ask whether the sample is of users, sessions, transactions, devices, or distinct accounts. A row-uniform transaction sample gives a frequent purchaser more chances to appear than someone with one transaction. That may be correct for estimating a transaction-level quantity and inappropriate for a user-level question.

Suppose user A has ten purchases and user B has one. Selecting one purchase uniformly gives A probability ten-elevenths. Selecting one user uniformly gives each user one-half. Neither rule is inherently wrong; they answer different questions. Deduplicating after sampling purchases does not retroactively make the original design user-uniform.

Define eligibility before discussing randomness. Does the population include inactive users, test accounts, incomplete days, or only records with nonmissing labels? A perfectly randomized sample from yesterday's active users cannot establish behavior for every registered user.

For an interview, write a sentence such as: “I will select 100 distinct eligible customer IDs without replacement from the snapshot at this cutoff, stratified by country.” It names the unit, size, replacement rule, time boundary, and allocation. Each word constrains implementation and testing.

Also separate exploratory inspection from estimation. Five random rows can help inspect formats but cannot support a precise conversion-rate claim. An interviewer may deliberately switch from debugging examples to a population estimate. State which statistical properties the new task requires.

Why LIMIT is not a random sample

Without a specified order, a database may return whichever rows its plan produces first. Adding LIMIT 100 caps output size; it does not equalize inclusion probabilities. Ordering by an increasing ID and taking the first hundred is reproducible, but it systematically selects one end of that ordering.

For an exact-size PostgreSQL row sample from the eligible relation, a simple original pattern is:

SELECT customer_id, country
FROM customers
WHERE eligible = true
ORDER BY random(), customer_id
LIMIT 100;

The tie-breaker makes the output order explicit when scores tie. If fewer than 100 eligible rows exist, the result is smaller. The query may evaluate a random score for many rows and perform substantial ordering work. Do not describe it as a cheap index seek merely because it returns a hundred rows.

PostgreSQL documents its random-number functions and seeding behavior in the mathematical functions reference. Seeding a session's random generator is a different contract from preserving a sample forever across changed data, parallel plans, or different execution environments.

A probability filter such as WHERE random() < 0.1 instead has a variable output size. Under an ideal independent uniform model, each eligible row has ten-percent inclusion probability. It does not promise exactly ten percent of the rows. Do not add LIMIT afterward and assume the resulting design is still the same.

Compare rows, blocks, and the location of filters

Official PostgreSQL behavior: TABLESAMPLE BERNOULLI samples individual rows and scans the table; SYSTEM samples blocks, returning rows within selected blocks. Sampling occurs before other filters such as WHERE. Percentages are approximate, not exact output quotas. See the SELECT reference.

That order creates a useful interview distinction. Sampling ten percent of a whole table and then keeping country B is not an exact-size sample of country B. A rare group can disappear from the realized sample. If the requirement is 50 eligible B users, design selection inside that eligible stratum.

Block sampling can be attractive when avoiding a full scan matters, but physical clustering affects the realized sample. Imagine pages organized by date or tenant. Selecting one page may return many records from one narrow segment. Row counts can look substantial while independent information is limited.

The original comparison below keeps the objectives separate:

Method	What is selected	Size contract	Main question to defend
Random order plus limit	Rows from an eligible relation	Up to k	Can the scan and ordering fit?
Probability filter	Individual rows	Variable	Is that probability appropriate?
Block sample	Storage blocks and their rows	Variable	Does clustering distort coverage?
Per-stratum ranking	Rows within each stratum	Per-group quotas	How will estimates be weighted?

BigQuery also documents block-based sampling and its cost and representativeness boundaries in its table-sampling guide. Syntax and restrictions differ between engines. A PostgreSQL answer should not quietly inherit BigQuery behavior or vice versa.

Derive the stratified estimate instead of averaging blindly

This original example has country A with 900 eligible users and country B with 100. Sample 50 from each country to ensure B has enough observations for a separate analysis. In the realized samples, A has five converters and B has 25.

The stratum estimates are ten percent for A and fifty percent for B. Pooling the selected users gives 30 conversions among 100 sampled users: thirty percent. But the population is ninety percent A and ten percent B, not fifty-fifty.

The population-weighted estimate is:

0.9 × 0.10 + 0.1 × 0.50 = 0.14

Fourteen percent is the weighted estimate for this fixture. It is not an exact known population truth: the two within-country rates were estimated from samples. Precision still depends on the design and observed variation.

Another way to see the arithmetic is through inclusion probabilities. A user in A is selected with probability 50/900; a user in B with 50/100. Equal-sized strata deliberately oversample B relative to its population share. For a population-level estimator, those differing probabilities matter.

If the task only asks to compare countries separately, equal sample sizes may be a sensible allocation. If the task asks for overall conversion, carry the population counts or appropriate weights into estimation. State the estimand before deciding whether reweighting is needed.

Write per-country quotas with an explicit shortfall policy

A window-function pattern can rank eligible users within country:

WITH ranked AS (
  SELECT customer_id, country,
         row_number() OVER (
           PARTITION BY country
           ORDER BY random(), customer_id
         ) AS rn
  FROM customers
  WHERE eligible = true
)
SELECT customer_id, country
FROM ranked
WHERE rn <= 50;

This assumes one row per customer. If the source contains repeated customer rows, deduplicate according to a stated business rule before assigning ranks. Otherwise a user with multiple rows can occupy several slots and the declared unit is violated.

If one country has only 12 eligible users, this query returns 12 for that country. Decide whether to accept the shortfall, reduce all quotas, redistribute unused slots, or sample with replacement. Each alternative changes the design. A redistribution rule should be written down rather than inferred from the final row count.

For proportional allocation, calculate group quotas from population sizes and a total budget. Rounding can make quotas sum to a number other than the budget; use a documented correction such as assigning remaining slots by fractional remainder. Minimum quotas for rare groups require a separate weighting explanation.

Reproducibility needs more than a seed

For a stable cohort, assign each immutable sampling-unit ID a deterministic hash score with a versioned salt and canonical encoding. Rank by that score and the ID. PostgreSQL's string-function documentation describes md5(text); a hash-ranking design still needs explicit assumptions about score distribution and input identity.

For a simple illustration, md5('cohort-v1:' || customer_id::text) can define a deterministic ordering for fixed identifiers. MD5 here is an ordering example, not a security or anonymization recommendation. Anyone who knows the inputs can recompute it; hashing IDs does not make sampled personal data anonymous.

Top-k and threshold selection behave differently when rows are added. With top-k, a newly added lower-scoring ID can displace an existing member. With a fixed threshold, existing IDs retain their inclusion decision, but sample size changes as the population grows. Choose the behavior your cohort requires.

Save a manifest containing snapshot or extract ID, eligibility predicate, unit key, stratification fields, algorithm version, seed or salt, quotas, and selected IDs. If the same customer ID later refers to corrected or updated attributes, preserving the ID list alone does not preserve the original analytical inputs.

The PostgreSQL REPEATABLE guarantee is conditioned on the table not changing. Record that boundary at first use. A seed is evidence about random selection; a snapshot is evidence about which population and values existed.

Verify the design with adversarial small cases

Build a tiny fixture with duplicate users, two highly unequal countries, missing strata, and one country below its quota. Check unique selected IDs, membership in the eligible population, quota shortfalls, and absence of replacement when the contract forbids it.

For deterministic hash selection, reorder the input rows and require the same result. Add one new low-scoring ID to demonstrate top-k displacement; add one high-scoring ID and show no displacement. For threshold selection, verify that old IDs keep their inclusion decision.

Simulations can detect large bias, but one plausible histogram does not prove uniformity. Separate branch and invariant tests from repeated-trial statistical checks. When estimating a population quantity, verify the weighting arithmetic independently of the selection code.

In your answer, finish with the design's limitation: block sampling trades access cost against clustering risk; disproportionate strata need estimation weights; deterministic cohort selection needs stable identity and a data-version boundary. That is the reasoning SQL data sampling interview questions are designed to expose.

Practice sampling and estimation contracts

These linked records are adjacent practice prompts. They do not establish a company's SQL sampling format. Explain the unit and target estimate for each, then identify which parts of the design change when the objective changes.

Question	Practice focus
Implement Stratified Sampling by Country	Allocate country quotas and retain weights
Estimate Population Mean and Conversion Rate Accurately	Separate sample summaries from population estimates
Choose 200 of a Million Records to Label, Improving on Uniform Random Sampling	Compare fixed-budget selection designs
Analyze User Comment Distribution and Sampling Effects	Distinguish comments from users as sampling units
Implement Clustered Sampling to Mitigate Network Effects in Testing	Explain correlated units and sampling uncertainty

Start with Implement Stratified Sampling by Country. Explain your assumptions and expected result before implementing the solution.

Sources and further reading

• PostgreSQL SELECT and TABLESAMPLE
• PostgreSQL random() and setseed()
• PostgreSQL string functions and md5()
• BigQuery table sampling