Amazon Data Scientist Interview Preparation Guide - Entry Level

Bucketing with CASE and then counting per bucket is a two-step composition: first classify each row, then treat the classification as an ordinary GROUP BY column.

Structured elaboration

SELECT bucket, COUNT(*) AS product_count FROM (
  SELECT CASE
    WHEN net_revenue < 100 THEN 'low'
    WHEN net_revenue < 2000 THEN 'medium'
    ELSE 'high'
  END AS bucket
  FROM sales
) t
GROUP BY bucket;

Wrapping the CASE expression in a subquery (or a CTE, a Common Table Expression) first, then grouping on its output column, avoids having to repeat the entire CASE expression in the GROUP BY clause; some engines do allow grouping directly on a repeated CASE expression inline, but the subquery form reads more clearly and sidesteps any alias-in-GROUP-BY portability question entirely.

Worked example

Given products with net_revenue 50, 500, 5000, 20: the low bucket (< 100) catches two products (50 and 20), medium (100 to <2000) catches one (500), and high (>= 2000) catches one (5000), giving bucket counts low: 2, medium: 1, high: 1.

Trade-offs and pitfalls

This composition pattern, classify-then-group, generalizes well beyond revenue buckets: cohort labeling, risk tiers, and age brackets all follow the identical shape. Keep the threshold logic in exactly one place (the subquery/CTE) so a later change to the bucket boundaries doesn't need to be hunted down in multiple copies of the same CASE expression.

High-level: k-NN classifies a query by finding the k closest labeled examples and aggregating their labels (majority vote or weighted). It’s simple, non-parametric, and adapts to data without training a global model.
Practical limitations:

• Latency/throughput: brute-force distance to all points is O(n·d), too slow for large n.
• Memory: storing full dataset can be large.
• Curse of dimensionality: distances become less meaningful as d grows; neighbors may be noisy.
  Engineering mitigations:
• Approximate nearest neighbor (ANN) libraries (FAISS, Annoy, HNSW) reduce query time with sublinear lookup and controlled recall/precision trade-offs.
• Dimensionality reduction (PCA, autoencoders) or feature selection to reduce d.
• Vector quantization / product quantization to reduce memory and speed queries (used in FAISS).
• Sharding and caching hot queries; use GPU-accelerated ANN for high throughput.
  Trade-offs: ANN and compression improve latency/scale but can slightly reduce accuracy; dimensionality reduction may lose discriminative signal, so validate end-to-end.

Direct answer

Keep alignment across time zones with three levers: shrink what actually needs real-time overlap by defaulting to async updates on a fixed template, protect a small deliberately scheduled overlap window for anything that truly needs live discussion, and make handoffs explicit in writing so context transfers cleanly across the boundary instead of depending on someone's memory.

Framework

Reduce dependence on overlap. Default to async status updates on a fixed cadence, and use written decision docs rather than requiring a live meeting for every decision. Most updates don't need a room, only genuinely ambiguous or high-stakes calls do.

Protect a deliberate overlap window. Negotiate a recurring block, even a short one, and rotate who takes the inconvenient time so the burden doesn't always fall on the same region.

Make handoffs explicit. When work crosses a time-zone boundary, produce a short written artifact rather than relying on a quick chat message. This matters most in ops-heavy, always-on contexts.

Worked example

Consider an on-call rotation providing 24/7 production coverage across three time zones (for example [Region A], [Region B], and [Region C]), where the two outer regions have little or no live overlap with each other.

• Shadow and overlap periods: the incoming region's on-call shadows the outgoing region's on-call for a short deliberate window at the shift boundary, even 15 to 30 minutes, to ask questions live before the outgoing engineer signs off.
• Written handoff template: a standard document filled at every handoff covering open incidents, any systems in a degraded state, changes deployed in the last shift, and explicit 'known risk' or 'do not touch' notes.
• Escalation expectations: a written policy defining what counts as page-worthy versus a handoff note, who the secondary on-call is in each region, and how long the incoming engineer has to acknowledge before it auto-escalates.
  Result: even with zero live overlap between two of the three regions, the written handoff plus the short shadow window from the middle region means each incoming on-call starts already briefed, instead of reconstructing state from raw logs.
  For non-ops roles the same mechanism applies with a different artifact, for example a design or product handoff might be a written decision log plus a recorded walkthrough rather than an incident handoff, but the principle (explicit written handoff over a live conversation) is the same.

Trade-offs and pitfalls

• Repeatedly scheduling occasional syncs at painful hours burns out whichever time zone draws the short straw. Rotate it deliberately.
• Async-only breaks down for genuinely ambiguous or high-stakes decisions. Some live channel for true emergencies still has to exist.
• A handoff template that's too heavy gets skipped under time pressure. Keep it short enough to fill in within a few minutes.
• Assuming a chat message counts as a handoff is the actual failure mode this whole approach is designed to prevent. The structured artifact is the point, not the tool it's written in.

Direct answer
Allocate a starting resource level, evaluate all configurations at that level, keep the top fraction, double (or otherwise scale) the resource, and repeat until one configuration remains or the max resource is reached; handle evaluation failures by treating a failed configuration as having the worst possible score at that rung rather than crashing the whole scheduler.

Structured elaboration
The resource starting point is chosen so that the total number of halving rungs fits within the max resource given the halving factor; at each rung, sort configurations by their score at the current resource level, keep the top 1/eta fraction (rounding up to at least 1 to always make progress), and double (for eta=2) the resource for survivors before the next rung. A configuration whose evaluation raises an error is assigned the worst possible score for that rung (rather than propagating the exception and killing the whole scheduler), so it's naturally dropped at the next promotion step rather than crashing the run; this is important because at scale, transient failures (a worker OOM, a flaky dependency) are common and shouldn't be allowed to take down an entire tuning job.

Worked example (executed)

import numpy as np

def successive_halving(configs, max_resource, eval_fn, eta=2):
    n = len(configs)
    resource = max(1, max_resource // (eta ** int(np.log(n) / np.log(eta))))
    remaining = list(configs)
    history = []
    while len(remaining) > 1 and resource <= max_resource:
        scored = []
        for c in remaining:
            try:
                score = eval_fn(c, resource)
            except Exception:
                score = -np.inf
            scored.append((score, c))
        scored.sort(key=lambda x: -x[0])
        keep = max(1, len(scored) // eta)
        remaining = [c for _, c in scored[:keep]]
        history.append((resource, len(remaining)))
        resource *= eta
    best = max(remaining, key=lambda c: eval_fn(c, max_resource))
    return best, history

Run against 16 synthetic configurations with known (but hidden-from-the-optimizer) true quality scores, a max resource of 16, eta=2, and one configuration (id 3) deliberately raising an exception at low resource to simulate a transient failure: the scheduler correctly progressed through rungs (16 -> 8 -> 4 -> 2 -> 1 configurations at resources 1, 2, 4, 8), never crashed on the injected failure, and selected the actual best-quality configuration (true quality 0.878, matching the maximum across all 16 candidates) as its final answer.

Trade-offs & pitfalls
This implementation re-evaluates the final survivor at max_resource even though it may have already been evaluated there during the last rung; in a real system where evaluations are expensive, you'd cache and reuse that last rung's score rather than re-running it, a detail worth calling out even though it doesn't affect correctness here.

Direct answer. A standard B-tree index stores values in sorted order, which lets it efficiently find a matching PREFIX of a string; a wildcard at the START of a LIKE pattern means there's no fixed prefix to search for, so the index can't narrow the search at all and the engine falls back to checking every row's text against the pattern directly.

Structured elaboration. LIKE 'foo%' (wildcard only at the end) can use a standard B-tree index efficiently, because every string matching that pattern shares the same prefix, 'foo', which the index's sorted order can jump straight to. LIKE '%foo%' or LIKE '%foo' (wildcard at the start) has no such fixed prefix: a match could be any string CONTAINING or ENDING WITH 'foo' anywhere, which the sorted order of a standard index gives no leverage on at all, so the engine has no better option than scanning and checking every row.

Realistic options: a specialized full-text search index (built for token or substring matching rather than prefix matching) is the most direct fix if your engine supports one and the matching semantics fit; a trigram or n-gram index (where supported) can accelerate substring matches specifically, including leading-wildcard patterns, by indexing small overlapping fragments of the text rather than the whole string; and, if the real requirement is closer to "search," moving that specific workload to a dedicated search engine designed for it is often the more durable long-term answer once a standard relational index genuinely can't help.

Worked example. A dashboard filter doing WHERE comments LIKE '%refund%' against a large text column is the exact shape that defeats a plain B-tree index; if the underlying database supports a trigram index, adding one on that column can make this specific kind of substring search dramatically faster without changing the query at all, since the trigram structure is built specifically to handle "the pattern could start anywhere" searches that a standard index can't.

Trade-offs and pitfalls. Specialized indexes for this kind of search (trigram or full-text) cost more storage and more write-time maintenance than a standard B-tree, and aren't a good fit for every use case (very short strings, or a genuinely rare query pattern that doesn't justify the ongoing cost); weigh how often this kind of search actually runs, and how latency-sensitive it is, against that ongoing cost before reaching for a specialized index.

Direct answer

Ranking by requester revenue measures who has leverage, not who is hurt or what matters to the strategy. It is a useful input (revenue at risk is real) but a poor ranking rule. I would rank requests on how many customers are affected, how severe the problem is, how well it fits the strategy and how much it costs to build, and handle documented churn risk in a separate lane.

Critique of the revenue rule

1. Leverage, not need. Revenue shows who can threaten to leave, not who needs the capability most.
2. Segment skew. Leverage here means bargaining power. The roadmap drifts toward your largest accounts and under-serves the segment you want to grow.
3. One voice, one data point. A large account's request is one account, however large its invoice.
4. Not comparable. Dollars on one side and problems on the other cannot be traded without a common scale.
5. Easy to game. Whoever reports the request can inflate the account's importance (gaming a score means feeding it inputs that push a favoured item up).

What to rank on instead

• Reach: share of active customers affected.
• Severity: 1 to 3. Anchors: 1 = annoyance, 2 = slows a task but a workaround exists, 3 = blocks work or loses data.
• Strategic fit: 1 to 3 (does it move where the company is going). Anchors: 1 = unrelated to the stated strategy, 2 = supports it, 3 = directly advances a named top priority such as moving up-market.
• Effort: engineer-weeks.
• Revenue at risk: its own churn-risk lane (a separate queue for requests tied to a customer likely to cancel, so it does not get mixed into the score), requiring evidence (renewal date, stated blocker).

Reach is how many, revenue is how much value, severity is how bad, fit is how much it matters to the strategy. They can disagree, which is why none should stand in for the others.

Normalising unlike inputs

Put them on defined scales: percentages for reach, 1 to 3 scales with written anchors for severity and fit, one unit for effort. Revenue is not a term in the score. It appears as a band in each brief and decides whether the churn-risk lane applies: band 1 under $100k, band 2 $100k to $500k, band 3 over $500k, so one large number does not drown the rest.

Variants of the same request

Group requests by the underlying problem, not the wording. "Export to CSV", "Excel download" and "weekly emailed report" all describe getting data out regularly. Count distinct accounts once and keep the variants as solution ideas.

Worked example (illustrative, 400 active customers)

score = reach % x severity x fit / effort weeks

Ranked by revenue: R1, R2, R3. Ranked by score: R2, R3, R1. R1 goes to the churn-risk lane only if there is evidence (renewal date, written blocker). Suppose R1's account renews in 90 days, with a written statement that custom SSO (single sign-on: one company login for many tools) attribute mapping, meaning passing the customer's own user fields such as department into our product, is a blocker. R1 is band 3 ($900k at risk) and costs 6 engineer-weeks. R2 costs 4. With 10 weeks of capacity, both fit, so build both, R2 first. With only 6 weeks, leadership compares $900k of retained revenue against R2's benefit to 12 accounts, and should first ask whether a 2-week configuration workaround retains the account.

Packaging the evidence for product

A one-page brief per request: the problem in one sentence, distinct accounts and users with the denominator, segment, severity evidence with a quote, revenue and renewal dates, variants merged, an engineering effort estimate, and what is still unknown.

Pitfalls

• The score sorts; it does not decide. Weights are choices, so write them down.
• Scores that depend on self-reported inputs get gamed. Require evidence for each number.

Direct answer: Stability selection picks a robust feature subset by repeatedly subsampling the data, running a base selector (typically Lasso) on each subsample, and keeping only the features selected consistently across a large fraction of the resamples, which directly controls false discoveries by requiring repeated, not one-off, evidence that a feature matters.

Structured elaboration:
The procedure: draw many random subsamples of the training data (each, say, half the full dataset), run the base selector (Lasso across a range of regularization strengths) on each subsample, and record which features get selected. A feature's final "selection frequency" is the fraction of subsamples in which it was chosen; only features exceeding a chosen frequency threshold (a common choice being a majority or higher) are kept in the final selected set. This controls false discoveries because a feature that only appears predictive due to noise in one particular sample is unlikely to be repeatedly selected across MANY independent subsamples, while a genuinely robust signal will be.

Hyperparameters that matter: the subsample size (typically around half the data, balancing enough variation between subsamples against each subsample still being large enough to fit reliably), the number of subsampling repetitions (more gives a more stable frequency estimate, at proportionally more compute cost), the range of regularization strengths swept for the base Lasso selector, and the final selection-frequency threshold (a stricter threshold gives fewer, more conservative false-discovery-controlled features; a looser one keeps more candidates at the cost of weaker false-discovery guarantees).

Evaluating robustness on future time-slices: stability selection controls for sampling noise WITHIN one time period, but a feature can still be an artifact of that period specifically (a seasonal effect, a since-fixed data issue) rather than a genuinely durable signal. To check this, re-run the full stability-selection procedure independently on separate, non-overlapping time windows (for example, month by month, or a rolling/expanding window over the available history) and compare the resulting selected sets across windows: a feature's selection frequency should stay reasonably consistent across windows, not swing from consistently-selected in one period to consistently-excluded in the next. A simple way to quantify this is the overlap (or Jaccard similarity) between the selected feature sets from consecutive or held-out time windows; a feature that is selected in some windows and not others is a candidate to either drop or flag as period-dependent rather than trusted as a durable, production-worthy feature.

Worked example: Running stability selection with 100 subsamples on a dataset with genuine and spurious candidate features, a genuinely predictive feature might be selected in 95 of 100 subsamples (a 0.95 selection frequency), while a feature that only looked predictive due to a coincidental correlation in the full dataset gets selected in only, say, 30 of 100 subsamples, since that coincidental pattern doesn't consistently reproduce across different random halves of the data; setting the threshold at, say, 0.8 would correctly keep the first feature and correctly exclude the second. Repeating this across three separate quarters of historical data, a durable feature clears the 0.8 threshold in all three quarters, while a feature that only cleared it in one quarter (and fell well below 0.8 in the other two) is flagged as period-dependent rather than promoted to production, even though it passed the single-period stability-selection check that quarter.

Trade-offs and pitfalls: Stability selection's compute cost scales with the number of subsampling repetitions times the cost of fitting the base selector on each one, which can be substantial for a slow-to-fit base model; running it again per time-slice for the robustness check multiplies that cost further, so in practice the time-slice check is often done at a coarser cadence (quarterly, say) rather than repeating it on every retraining cycle.

Direct answer
Put the ask, the essential context, and the next step in the first two sentences, so a busy reader can act on the email even if they only read the opening before deciding whether to reply now or later.

Structured elaboration

1. State the ask as the first sentence, not buried after several paragraphs of context: "I'd like to request temporary access to X" or "Could you approve Y by Thursday?"
2. Give only the essential context, one or two sentences of why this ask exists, not the full backstory. Include it because it makes the ask easier to say yes to quickly, not because it's interesting.
3. State the next step explicitly: what you need them to do, and by when, so they don't have to infer the deadline or the required action.
4. Use the subject line to state the ask, not just the topic: "Approval needed by Thursday: Q3 budget line" tells the reader more than "Budget question."
5. Keep the whole email short. If the request genuinely needs more context, put the essential ask up top and the detail below it, rather than making the reader wade through detail to find the ask.

Worked example
Subject: "Access request: prod DB read access, needed by Wednesday"

Body: "Could you grant me temporary read access to the orders table in prod? I'm investigating a customer-reported data discrepancy (ticket #4821) and need to check actual row values, which I can't do in staging since the issue only reproduces with real production data. Happy to have this access time-boxed to a few days and revoked afterward if that's easier to approve."

The ask (temporary read access) and the deadline context (needed by Wednesday) are in the subject line alone; the body confirms the specific ask, gives the minimum context needed to approve it, and proactively offers a constraint (time-boxed) that makes approval easier.

Trade-offs and pitfalls

• Leading with a long justification before the ask is the single most common failure; a reader has to hold the whole paragraph in their head waiting to find out what you actually want.
• Too little context can also fail: an ask with zero justification can force the reader to ask a clarifying question back, which is slower than including the one sentence of context that would have let them approve it immediately.
• For sensitive or high-stakes asks (a large budget approval, access to something risky), a slightly longer, more carefully justified email is worth the extra length; the "front-load the ask" principle still applies, it just means front-loading a well-justified ask rather than skipping justification entirely.

Direct answer. Write the join condition so a NULL on the left side's key part is treated as an automatic match against ANY value on the right (l.key2 = r.key2 OR l.key2 IS NULL), and keep that wildcard logic in the join predicate itself rather than trying to fake it by coalescing NULL to a literal placeholder string, which risks colliding with a genuinely real value.

Structured elaboration. The two halves of this question are really two sides of the same judgment call about what NULL means in a composite key. Sometimes NULL genuinely means "matches anything" (a wildcard, a business rule saying "this row applies regardless of the second key part"), and an explicit OR-with-IS-NULL clause in the join condition expresses that intent directly. Other times NULL means "unknown, do not guess," and coalescing it to a literal sentinel like the string 'NULL' to force a match is dangerous specifically because that sentinel could ALSO be a legitimate value already present in the data (a SKU literally named "NULL" as a placeholder product code, say), silently merging two things that were never meant to be the same.

Worked example. left_table(id, key1, key2, value): (1, 'US', NULL, 'v1') (a wildcard row for all of key2 under 'US'), (2, 'US', 'east', 'v2'). right_table(key1, key2, price): ('US', 'east', 100), ('US', 'west', 120).

SELECT l.id, r.key2, r.price
FROM left_table l
JOIN right_table r
  ON l.key1 = r.key1
 AND (l.key2 = r.key2 OR l.key2 IS NULL)
ORDER BY l.id, r.key2;

Result: (1, 'east', 100), (1, 'west', 120), (2, 'east', 100). Row 1 (the wildcard, key2=NULL) correctly matches BOTH right-side rows since its key2 is NULL, while row 2 (key2='east') matches only the one exact right-side row it specifies.

Trade-offs and pitfalls. The danger case for the counterpoint: imagine a real SKU is legitimately coded as the literal string 'NULL' by an upstream system's bug or convention. If you'd instead written COALESCE(l.sku, 'NULL') = COALESCE(r.sku, 'NULL') to make actual NULLs match each other, that real SKU would now be silently treated as identical to every genuinely-missing SKU, merging unrelated rows. The wildcard pattern above avoids this entirely because it never manufactures a fake value to compare against; it just tests IS NULL directly. Before implementing either pattern, confirm with whoever owns the business rule which meaning NULL is actually supposed to carry here, since the two interpretations produce very different (and both silently plausible) results.

Generate one row per column with its key profiling statistics, using conditional aggregation so a single pass over the table produces the whole report, and design it so the same shape can be templated across many tables rather than hand-written per table.

Approach

```sql
SELECT 'sale_amount' AS column_name,
COUNT() AS total_rows,
COUNT() - COUNT(sale_amount) AS null_count,
ROUND(100.0 * (COUNT() - COUNT(sale_amount)) / COUNT(), 2) AS null_pct,
COUNT(DISTINCT sale_amount) AS distinct_count,
MIN(sale_amount) AS min_val, MAX(sale_amount) AS max_val,
COUNT(*) FILTER (WHERE sale_amount < 0) AS invalid_count
FROM sales;
```
Extending this to every column in the table is a mechanical template expansion (one `UNION ALL` block per column, or generated programmatically from the catalog's column list), not a fundamentally different query per column.

Worked example

For a `sales` table with 10,000 rows, 50 NULL amounts, 9,800 distinct amounts, and 3 negative amounts, the report reads: `total_rows=10000, null_count=50, null_pct=0.50, distinct_count=9800, invalid_count=3`, giving a single-glance health summary a reviewer can act on immediately.

Trade-offs and pitfalls

Rolling several of these per-column signals (null rate, duplicate rate, distinct-count stability) into ONE normalized 0-100 score per table is a natural next step, but the weighting is a genuine judgment call, not a mechanical derivation, since a 5% null rate on a rarely-used optional field shouldn't count the same as a 5% null rate on a required key column; document the weighting rationale explicitly rather than treating the resulting number as objectively derived. The same profiling shape adapts directly to ranking ingestion batch FILES by error rate (rather than columns within one table) to quickly spot which specific files need reprocessing, and to measuring inter-annotator disagreement on a labeled dataset as a distinct but related data-quality signal, when the thing you're profiling is label consistency rather than column-level statistics.