Write top-3 orders per country. What breaks if you use RANK() instead of ROW_NUMBER()?

RANK() (and DENSE_RANK()) ties share a rank, so you can return more than three rows when amounts match. ROW_NUMBER() always returns exactly N. Say when you want ties (leaderboard) versus a hard cap (sample / pagination).

A query is correct but scans last year. How do you prove partition pruning?

Read the plan / bytes scanned / partitions assigned. Add the partition column to the filter (or clustering). EXPLAIN or the warehouse profile — not a guess.

EXPLAIN says Hash Join. When do you want Nested Loop instead?

When one side is tiny and indexed (point lookups), nested loop + index can beat building a hash. Warehouse SQL usually hashes; OLTP Postgres often nested-loops. Say “probe side size,” not “hash is always faster.”

Retail / cloud · Interview pack

Amazon data interview pack

28 written questions with answers, the loop shape people report for Amazon, and 5 SQL coding labs to practise. Built from DataLane's original question bank and publicly discussed interview themes, not leaked exams. Last editorial update 2026-06-28.

What is in this pack

Questions by topic

SQL3
Coding2
Data modeling1
Streaming2
Warehouse1
Cloud14
System design2
Data quality1
Behavioral2
SQL coding labs5

8 easy · 15 medium · 5 hard

Rounds (2)

SQL
AWS data

Themes: Scale, S3 layout, Glue vs EMR

Free with a DataLane account

The first 3 questions are below. Sign in (free, no plan needed) to read all 28 and download the pack as a PDF.

Free preview

1. SQL · easy · SQL
Write top-3 orders per country. What breaks if you use RANK() instead of ROW_NUMBER()?
RANK() (and DENSE_RANK()) ties share a rank, so you can return more than three rows when amounts match. ROW_NUMBER() always returns exactly N. Say when you want ties (leaderboard) versus a hard cap (sample / pagination).
2. SQL · medium · SQL
A query is correct but scans last year. How do you prove partition pruning?
Read the plan / bytes scanned / partitions assigned. Add the partition column to the filter (or clustering). EXPLAIN or the warehouse profile — not a guess.
3. SQL · hard · SQL
EXPLAIN says Hash Join. When do you want Nested Loop instead?
When one side is tiny and indexed (point lookups), nested loop + index can beat building a hash. Warehouse SQL usually hashes; OLTP Postgres often nested-loops. Say “probe side size,” not “hash is always faster.”

25 more questions and 5 SQL labs are in the full pack.

The Amazon loop

SQL
Top-N per partition, window vs self-join, and a join that must not explode.
AWS data
Tiny files on S3, Athena bytes scanned, when Glue is enough vs EMR.

SQL (3)

easy · SQL
Write top-3 orders per country. What breaks if you use RANK() instead of ROW_NUMBER()?
RANK() (and DENSE_RANK()) ties share a rank, so you can return more than three rows when amounts match. ROW_NUMBER() always returns exactly N. Say when you want ties (leaderboard) versus a hard cap (sample / pagination).
medium · SQL
A query is correct but scans last year. How do you prove partition pruning?
Read the plan / bytes scanned / partitions assigned. Add the partition column to the filter (or clustering). EXPLAIN or the warehouse profile — not a guess.
hard · SQL
EXPLAIN says Hash Join. When do you want Nested Loop instead?
When one side is tiny and indexed (point lookups), nested loop + index can beat building a hash. Warehouse SQL usually hashes; OLTP Postgres often nested-loops. Say “probe side size,” not “hash is always faster.”

Coding (2)

medium · Spark
repartition vs coalesce. When is each correct?
repartition shuffles to N partitions (increase parallelism, or partition by a key). coalesce reduces partitions without a full shuffle (after a filter). coalesce(1) to write one file is fine for a small report and a disaster for a 2 TB table.
hard · Spark
S3 vs HDFS locality. Why was the same job faster on HDFS?
Object stores have weaker locality and more LIST/GET. File sizing and partition pruning matter more on S3.

Data modeling (1)

hard · Python & modeling
Late-arriving facts and a Type-2 dim. How do you still get the right attribute?
Join on the business key AND fact.event_ts BETWEEN dim.valid_from AND dim.valid_to. If you only join is_current = true, yesterday’s late order picks up today’s address. This is the classic senior modeling question.

Streaming (2)

easy · Kafka
Kafka vs Kinesis — what is the real trade?
Kafka: you own brokers, retention, and ops (or pay for a managed cluster). Kinesis: less ops, shard math, AWS lock-in, 24h–365d retention knobs. Pick from ops budget and existing cloud, not a blog benchmark.
medium · Apache Flink
Checkpoint vs savepoint.
Checkpoints are automatic recovery. Savepoints are operator-triggered, versioned points for deploy and resize. You do not “take a checkpoint” for a migration; you take a savepoint.

Warehouse (1)

medium · PostgreSQL
A dashboard is slow. First tool?
EXPLAIN (ANALYZE, BUFFERS). Name seq scan vs index, then decide. Adding an index without a plan is cargo cult.

Cloud (14)

hard · AWS
S3 + Glue + Athena vs Redshift — how do you choose in sixty seconds?
Ad-hoc and cheap scans: Athena on partitioned Parquet with a Glue catalog. Concurrent BI and predictable SLAs: Redshift (or Redshift Serverless). Glue is the job runner / catalog, not the warehouse. “Everything in Athena” dies on 40 concurrent dashboards.
medium · AWS
Glue job vs Lambda vs EMR. Who owns a 40-minute Spark transform?
Lambda is for minutes and megabytes, not Spark. Glue is managed Spark for batch ETL you do not want to babysit. EMR (or EKS + Spark) when you need custom images, long-running clusters, or cheaper reserved capacity. Say the timeout and the data size first.
medium · AWS
Kinesis vs MSK vs SQS. One sentence each.
SQS: decouple workers, no replay of a stream, competing consumers. Kinesis: managed stream, shards, AWS-native. MSK: Kafka protocol on AWS when you already think in topics and consumer groups. Do not put a 7-day clickstream on SQS.
medium · AWS
Redshift DISTKEY / SORTKEY (or AUTO). What mistake shows up as a broadcast of a 2 TB table?
A join on a column that is not the dist key, so every slice shuffles everything. Even in AUTO, a bad mental model (SELECT * fact JOIN huge dim ON a non-selective key) will not be saved by a wizard. Explain slice-local joins in one sentence.
easy · AWS
S3 committers — why did a failed Spark write leave a partial prefix?
The default committer can list/rename badly on S3. Use a magic committer and an idempotent overwrite of a partition.
medium · AWS
Cost tags on Glue/Athena/Redshift. Why do interviewers ask?
Untagged shared resources become a mystery bill. You should be able to say which DAG spent the credits.
medium · AWS
CDC from RDS to the lake. Sketch the path.
DMS or Debezium to Kinesis/MSK, then MERGE into Iceberg/Delta/Redshift. A nightly dump is not CDC.
easy · AWS
Public bucket plus “we will add Lake Formation later.”
Close public access first. LF does not excuse a public prefix. Encryption and logging are next.
medium · AWS
Athena query billed 1.2 TB. The table is partitioned by day. First three checks?
Did the query filter the partition column (not DATE(ts) wrapping)? Are there thousands of tiny files? Is SELECT * pulling 400 columns? Then look at partition projection vs Glue partitions that never got added.
easy · AWS
S3 Inventory vs listing 40 million keys in a Lambda. Which is the pipeline?
Inventory (or S3 Batch) for discovery. LIST in a loop throttles and times out. Object creation events (SQS) for incremental ingest, not a nightly LIST.
medium · AWS
Glue job bookmarks vs an explicit watermark table. When do bookmarks lie?
When files are overwritten in place, clocks go backward, or you change the source path. A watermark you own in the lake is debuggable. Bookmarks are fine for append-only dumps you control.
medium · AWS
Redshift Spectrum over the same Iceberg as Athena. What do you refuse to do?
Two copies of the fact “for convenience.” One lake table, engines as clients. Spectrum and Athena both scan S3; duplicating into a local Redshift table is a choice with a vacuum cost, not a default.
easy · AWS
Kinesis Data Firehose into S3 as many 2 KB files. How do you fix gold?
Buffer size/interval on Firehose, then a compaction job (Glue/Spark) to 128 MB+ objects. Query engines hate tiny files more than they hate a 60s buffer delay.
hard · AWS
Lake Formation LF-Tags vs bucket policy. When is LF worth the ticket?
When many accounts/roles need column- and table-level grants on a shared catalog. A single team with two buckets can stay on IAM + bucket policy. LF is not a substitute for not putting PII in the key name.

System design (2)

medium · System design
Cost doubled and nobody knows which team. What was missing in the design?
Tags / chargeback on compute and storage, query tags from dbt/jobs, and an owner on every gold table. Architecture without FinOps is a slide.
easy · System design
Disaster recovery for the lake. What is the actual runbook?
Cross-region replica or object-lock, a metastore backup, and a restore drill. “S3 is durable” is not a runbook. Know RPO/RTO in hours, not adjectives.

Data quality (1)

medium · Data quality
A check is red every day and nobody cares. What did you do wrong?
The threshold is wrong or the owner is missing. Fix or delete. A permanently red test is worse than no test — it trains the team to ship on red.

Behavioral (2)

medium · Behavioral
A project you led was cancelled after two months. What was still valuable?
The decision record, the prototype that showed cost, and the people you unblocked. Do not pretend it shipped. Interviewers want learning and shutdown hygiene, not a fairy tale.
easy · Behavioral
Tell me about a time you reduced warehouse cost without a hero rewrite.
Concrete: auto-suspend, a clustering key on a real filter, killing a 5-minute dashboard tick, or a warehouse per team. Percent and dollars. “We migrated everything to X” is not the only story — small levers count.

SQL coding labs

Problem statements only. Run them against tests on DataLane; solutions live in the pad.

medium · about 10 min · tables: order_items, products
Top products by units sold
Merchandising wants units sold per product. The quantity lives on the line items, not the order header — summing orders.amount here is the classic wrong answer because an order can carry several products.
https://www.datalane-data.blog/coding/problems/top-products-by-qty/
medium · about 12 min · tables: orders
Revenue share by country
Each country needs its percentage of total completed revenue on the same row as its own revenue. A second query for the grand total works but scans the table twice — do it with one window instead.
https://www.datalane-data.blog/coding/problems/revenue-share-by-country/
hard · about 14 min · tables: order_items, products
Top two products per category
Top-N per group is asked in almost every SQL screen. Return the two best-selling products inside each category by units, with a deterministic tie-break.
https://www.datalane-data.blog/coding/problems/top-2-products-per-category/
hard · about 18 min · tables: orders, order_items, products
Customers who bought every category
Marketing wants customers who have purchased at least one product in every product category. Count distinct categories from completed orders only. Return customer_id ascending.
https://www.datalane-data.blog/coding/problems/bought-every-category/
hard · about 16 min · tables: orders
Customers above average order volume
CRM wants customers whose completed order count is strictly above the average completed-order count across customers who have at least one completed order. Return customer_id and completed_orders, highest volume first.
https://www.datalane-data.blog/coding/problems/above-average-order-count/