🗃️ SQL

Interviewers use ACID to check that you understand why banks and order systems use transactions. Atomic means a money transfer cannot debit one account and fail to credit the other. Consistent means constraints still hold after the commit. Isolated means two users updating the same row do not silently overwrite each other. Durable means a commit is on disk, not only in memory. Hive and Spark SQL tables are not fully ACID unless you use a transactional table format such as Hive ACID or Delta Lake.

Interviewers want the trade-off: correctness versus lock wait and throughput. Read Uncommitted can see uncommitted data. Read Committed only sees committed data and is the PostgreSQL default. Repeatable Read keeps rows you already read looking the same inside one transaction. Serializable makes the result look as if transactions ran one after another. In production, most OLTP apps use Read Committed. Reporting jobs that must not see mid-load rows often use a snapshot or Repeatable Read. Spark and Hive jobs usually isolate by writing a new snapshot, not by row locks.

These three bugs are why isolation levels exist. Example: you read a salary of 50,000 while another session is updating it to 60,000 and has not committed. If you see 60,000, that is a dirty read. If you see 50,000, then later 60,000 after they commit, that is a non-repeatable read. If you count 10 employees in a department and a new hire appears on the second count, that is a phantom. Interviewers ask this to see if you can debug "my report changed mid-query" bugs. PostgreSQL Repeatable Read uses snapshots, so phantoms are rarer than the textbook SQL Server picture.

SQL uses three-valued logic: true, false, and unknown. WHERE keeps only rows where the condition is true. Unknown is thrown away like false. This is why NULL = NULL filters nothing, and why JOIN on a nullable key drops NULL keys. Interviewers ask this because it silently empties result sets. In Hive and Spark SQL the same rule holds. Use IS NULL, IS NOT NULL, or <=> / IS NOT DISTINCT FROM when you really want NULL-safe equality.

Interviewers want the exact syntax because it is a daily bug. Example: SELECT * FROM orders WHERE shipped_at IS NULL finds open orders. COALESCE(shipped_at, current_date) fills a default for display, but it does not replace IS NULL for filtering. On large Hive and Spark tables, IS NULL on a partition column can still prune partitions. IS NULL on a nested struct field needs the field to exist; a missing field can also be NULL.

This is a favorite trick question. If 100 orders exist and 10 have a NULL coupon_code, COUNT(*) is 100 and COUNT(coupon_code) is 90. Interviewers ask it because dashboards go wrong when people COUNT a nullable foreign key. In production, COUNT(*) on a huge Spark table still needs a full scan unless a stored table statistic or a metadata count is used. For "how many customers used a coupon", COUNT(coupon_code) is correct. For "how many orders", use COUNT(*) or COUNT(1).

1. Example: values 10, NULL,
2. 20AVG is 15, not
3. 10If missing scores should count as 0, write AVG(COALESCE(score, 0)). Interviewers ask this because KPI averages look "too high" when NULLs are dropped. The same rule applies to SUM: NULLs are skipped, and SUM of an all-NULL set is NULL, not
4. 0Use COALESCE(SUM(x), 0) when a missing group should show zero. Spark SQL and PostgreSQL both skip NULLs in AVG.

NOT IN (SELECT manager_id FROM employees) fails when any manager_id is NULL, because id NOT IN (1, NULL) is not true. NOT EXISTS (SELECT 1 FROM employees m WHERE m.manager_id = e.id) treats NULL as "no match" and still returns the non-matching rows. Interviewers ask this because anti-joins are everywhere: customers with no orders, SKUs not in the feed. In Spark, a LEFT ANTI JOIN is the clear production form and avoids the NULL trap. Prefer NOT EXISTS or LEFT ANTI JOIN over NOT IN.

Use IN for a small distinct list. Use EXISTS for a correlated check such as "does this customer have any paid order". Use JOIN when you need columns from both tables. Interviewers ask this because JOIN is not a drop-in for EXISTS. If one customer has three orders, JOIN triples the customer row and can break a COUNT. In Spark, a broadcast IN list or a hash semi-join (LEFT SEMI JOIN) is often the plan behind EXISTS. Duplicate-sensitive answers should use EXISTS or SELECT DISTINCT after a join.

Interviewers want you to know what is left after each command. DELETE fires row triggers, can be logged row by row, and can roll back in a transaction. TRUNCATE is a bulk empty. In PostgreSQL, TRUNCATE can roll back inside a transaction. In some MySQL setups it is DDL and auto-commits. Hive TRUNCATE on a managed table removes data files. DROP is gone until you recreate it. In production, prefer DELETE for a slice of partitions, TRUNCATE for a full reload of a staging table, and never DROP a shared table without a restore plan.

Example: find departments with more than 10 employees. Filter people with WHERE hire_date >= '2020-01-01', then GROUP BY dept_id HAVING COUNT(*) > 10. Interviewers ask this because mixing the two is a syntax error and a logic error. WHERE reduces the scan. HAVING runs after the aggregate, so it is later and often more expensive. In Spark, push filters into WHERE (or a subquery) so partition pruning and file skipping still happen. You may use HAVING without GROUP BY to filter a single aggregate of the whole table.

Interviewers want you to pick the cheaper operator on purpose. If you combine 2023 sales and 2024 sales that cannot overlap, use UNION ALL. If you combine two customer lists and need unique emails, use UNION or UNION ALL plus SELECT DISTINCT. In Spark, UNION is DISTINCT and can shuffle a huge dataset. Production pipelines almost always use UNION ALL for appends. Column count and types must match. Hive and Spark name this UNION / UNION ALL; the same idea exists in PostgreSQL.

Example: email can be UNIQUE while user_id is the primary key. PostgreSQL unique indexes allow multiple NULLs. SQL Server historically allowed one NULL in a single-column unique index. Hive and Spark SQL do not enforce primary keys unless you add constraints in a catalog that actually validates them. Delta and some warehouses store keys as informational. Interviewers ask this to see if you know keys are for identity and uniqueness, not only for indexes. In production, still declare keys. They document the grain even when the engine does not enforce them.

CHAR(3) for country code IN is fine. A name in CHAR(100) wastes space and makes comparison surprising because of padding. Hive and Spark SQL mostly use STRING or VARCHAR. Both are variable length on disk in Parquet. PostgreSQL CHAR still pads. Interviewers ask this as a classic types question and to see if you think about storage. In production lakes, prefer STRING/VARCHAR and a check on length. Fixed-width files are an ingest format, not a reason to use CHAR in the warehouse.

Interviewers ask this because teams argue "just put it in a view" until the view is twelve joins and every dashboard is slow. A view always sees fresh base data. A materialized view can be stale until refresh. PostgreSQL materialized views need REFRESH MATERIALIZED VIEW. Spark and Databricks have materialized views in some catalogs. Hive has views but classic Hive has no true MV. In production, materialize expensive aggregates that many jobs read. Keep a view when the logic is simple and the tables are small or already clustered.

Interviewers want you to choose based on reuse and optimizer behavior. PostgreSQL often inlines CTEs now, so a CTE is not always an optimization fence. Spark may compute a CTE once or inline it depending on the version and config. A temp table in a warehouse can be costly if you write it to cloud storage. In production Spark, a cached DataFrame or a managed temp view is the usual "temp table". If the intermediate result is huge and used once, keep it as a CTE. If many steps or many sessions need it, persist it.

In SQL Server and MySQL InnoDB, the primary key is usually the clustered index. Lookups by that key are fast. A nonclustered index on email stores emails plus a pointer to the clustered key. Hive and Spark do not use B-tree clustered indexes on files. The close ideas are PARTITIONED BY, CLUSTER BY / DISTRIBUTE BY, and Z-ORDER or clustering keys. Interviewers still ask the OLTP definition. In production lakes, clustering helps skip files. It does not give you a B-tree seek.

Interviewers want judgment, not "indexes are always faster". A boolean is_active on a billion-row table may not be selective enough. A table that is 90% writes, 10% reads can get worse with five extra indexes. In Spark and Hive, "indexing" usually means partition columns, Z-ORDER, bloom filters, or a small broadcast lookup table. Partitioning on a high-cardinality user id creates millions of tiny files. That is also an index gone wrong. Measure with EXPLAIN and file-skipping stats before adding more.

Interviewers ask this because "the query is slow" is not an answer. You should look for table scans, huge shuffles, broadcasts of a large table, and wrong join types. PostgreSQL EXPLAIN ANALYZE is the production habit. Spark has EXPLAIN and the SQL UI with scan size and shuffle bytes. Hive has EXPLAIN and Tez/DAG views. Plans use estimates from statistics. If stats are stale, the plan is a guess. In production, compare estimated rows to actual rows. A 100-row estimate on a 50 million row join is a red flag.

Interviewers use join types to see if you understand CPU and memory, not only SQL text. Nested loop plus an index is perfect for "one order id, fetch the customer". For two big tables on user_id, a hash join or sort-merge join is the usual plan. Spark also has broadcast hash join when one side is small enough to send to every executor. If you broadcast a 20 GB dimension, you can crash the cluster. Sort-merge needs shuffle and sort. Use it when both sides are large and already partitioned on the join key.

Interviewers still ask normalization to see if you can model data. 1NF: do not store phone1, phone2 or a comma list of tags in one cell if you need to query them. 2NF: in a table keyed by (order_id, product_id), do not store customer_name that depends only on order_id. 3NF: do not store dept_name on every employee if dept_id already points to a department table. In warehouses we often stop at 3NF for dimensions, then denormalize for speed. Hive tables can store arrays and maps. That is fine for nested events, but it is not 1NF.

Example: copy product_category onto every sales fact so a dashboard does not join a product table each time. Interviewers ask this because analytics SQL is full of denormalized facts. The cost is consistency. If a category is renamed, you must update many rows or wait for the next ETL. In Spark lakes, denormalized Parquet is normal. Keep a normalized source of truth, then publish wide tables. Do not denormalize OLTP checkout tables the same way, or a price change becomes a nightmare.

PostgreSQL functions can also write data, so the interview answer is about intent. A function like tax(amount) belongs in SELECT. A procedure like close_month(p_month) loads tables and commits. Hive and Spark SQL have user-defined functions (UDFs), not classic stored procedures. Databricks SQL has routines in some catalogs. Interviewers ask this on warehouse teams that came from Oracle or SQL Server. In production Spark, put multi-step logic in a job (notebook, dbt, Airflow), not in a procedure that other engines cannot run.

Interviewers ask triggers because they hide logic. A BEFORE INSERT trigger can fill updated_at. An AFTER UPDATE trigger can write a history row. That is handy and also hard to debug. Hive and Spark tables do not use OLTP triggers. You implement the same idea in the ETL job or with Delta CDF / change feeds. In production OLTP, keep triggers small. A trigger that calls a web service will lock rows and fail in strange ways. Prefer explicit application or pipeline code when many engines write the table.

Classic example: session A updates order 1 then order 2. Session B updates order 2 then order 1. They wait forever until the engine kills one. Interviewers ask this for OLTP jobs and also for warehouse MERGE jobs that hit the same partition. In PostgreSQL you see deadlock detected. In Spark/Delta you see concurrent transaction conflicts, which are optimistic, not row-lock deadlocks. Prevention: lock rows in the same order, keep transactions short, and avoid user waits inside a transaction. On lakes, avoid two writers on the same table without a partition split or a queue.

Interviewers start here before ACID and isolation. Example: insert an order header and order lines in one transaction so you never store a header with no lines. Auto-commit mode runs each statement as its own transaction, which is dangerous for multi-step loads. Spark SQL jobs often treat one write as one transaction when using Delta or Iceberg. Hive ACID tables also support transactions. In production, keep transactions short. Do not open a transaction, wait for a human, then commit.

Interviewers want to know you can recover from a failed step. Example: you delete from staging, the next insert fails, you roll back, and staging is not left empty. TRUNCATE and DROP may or may not roll back, depending on the engine. PostgreSQL can roll back TRUNCATE. Spark job failures usually do not leave a half-written Delta commit because the table commit is atomic. In production, always decide what happens on failure: rollback the transaction, or write a poison-row table and continue.

Example: each order row plus the customer's total spend. GROUP BY customer_id cannot keep the order id. SUM(amount) OVER (PARTITION BY customer_id) can. Interviewers ask this because it is the jump from junior to mid SQL. Windows can also rank, lag, and compute running totals. They still may shuffle in Spark, just like a group by. A common production pattern is: window to filter (rn = 1), then GROUP BY for the final metric. Do not mix GROUP BY and a window on the same select unless every non-window column is grouped or aggregated.

ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) numbers salaries inside each department. Without PARTITION BY, the window covers the whole result. Interviewers ask this because people confuse it with PARTITIONED BY on a Hive table. Table partitions control files on disk. PARTITION BY in OVER is only the window grouping. In Spark, a window with a high-cardinality partition key still shuffles by that key. Keep the partition expression the real group, such as user_id, not a timestamp at second grain unless you mean it.

1. Scores 100, 100, 90: ROW_NUMBER might be 1, 2,
2. 3RANK is 1, 1,
3. 3DENSE_RANK is 1, 1,
4. 2Interviewers ask this in every data-engineer loop. Production trap: RANK() <= 3 can return more than three rows. ROW_NUMBER() = 1 needs a deterministic ORDER BY or ties flip between runs. In Spark, add a unique id as a last sort key so retries stay stable. Hive and PostgreSQL share the same three functions.

Interviewers want a short, clear split. An orders table in Postgres that updates order status is OLTP. A Spark fact table of three years of order lines is OLAP. The SQL looks similar. The engine, indexes, and grain differ. Do not run a full-table GROUP BY on the OLTP primary. Do not use the lakehouse for single-row UPDATEs every millisecond. Hybrid systems exist, but the design still starts from the workload.

Example: employees whose salary is greater than the average salary of their own department. The inner AVG needs dept_id from the outer row. Interviewers ask this because a correlated subquery can be slow if it is not rewritten as a join. Spark and PostgreSQL often rewrite it to a join or a window. You should still write the join or window yourself when the logic is "compare to the group average". In production, EXPLAIN should not show a nested loop over millions of outer rows.

employees e JOIN employees m ON e.manager_id = m.emp_id is the classic. Interviewers ask it because the idea is simple and the aliases are easy to mess up. You can also self-join events to find the next purchase. For "next row" problems, LEAD is often cleaner than a self join. In Spark, a self join still shuffles. If you only need the parent name, a self join is fine. If you need the whole tree, use a recursive CTE where the engine supports it.

Interviewers ask this because an accidental cross join is a production outage. A missing join condition in old comma syntax is a cross join. A real use is exploding a date spine against a list of stores to fill zeros. In Spark, a cross join of two large tables can blow memory and disk. The engine may require an explicit hint or config for large cross joins. PostgreSQL will just try it. Always estimate COUNT(*) of both sides in your head first.

Interviewers ask this in modeling rounds. Example: customer_sk = 918203 while email may change. Facts store customer_sk so a renamed email does not rewrite history. In Hive and Spark, people often use monotonically_increasing_id, a database sequence, or a hash of the natural key. Hash keys are stable across reloads if the natural key is stable. Integer sequences are smaller in Parquet. Do not use a Spark monotonically_increasing_id as a durable key across jobs. It is not stable.

Interviewers want the grain in one sentence. "One row per order line per day" is a fact. "One row per product" is a dimension. You join them in a star schema. Degenerate dimensions such as invoice_number may live on the fact. In Spark lakes the same idea holds: keep facts append-only and dimensions upserted. If you cannot say the grain, the fact table will double-count.

This is a core Hive/Spark interview topic. Pruning works when the filter is on the partition column in a form the planner can see. Wrapping the column, such as WHERE date_format(dt, 'yyyy') = '2024', often disables pruning. Dynamic pruning can skip partitions using values from a small dimension join. In production, partition by a column people actually filter, usually a date. Too many partitions (user_id) create tiny files. Too few (year only) skip little data. PostgreSQL has partition pruning on declarative partitions too.

Interviewers ask this when a plan suddenly goes bad after a big load. Stale stats can make the engine broadcast a 200 GB table. PostgreSQL ANALYZE samples the table. Hive ANALYZE TABLE ... COMPUTE STATISTICS and column stats matter for CBO. Spark 3+ reads min/max and null counts from Parquet footers even without Hive metastore stats. In production, compute stats after large loads, and use EXPLAIN to see estimated rows. Do not collect column stats on hundreds of unused columns if it makes every ETL slower than the queries you save.

Orders inner join customers drops orders with a missing customer id. Orders left join customers keeps those orders. Interviewers ask this daily. A common bug is LEFT JOIN then WHERE right.id IS NOT NULL, which turns it back into an inner join. Another bug is filtering the right table in WHERE instead of ON. In Spark, both joins shuffle or broadcast the same way. The difference is which rows survive. FULL OUTER JOIN keeps unmatched rows from both sides and is expensive.

Interviewers pair this with NULL questions. Example: COALESCE(mobile, email, 'unknown') as a contact key. Spark SQL also has NVL(a, b) with two arguments. PostgreSQL has COALESCE and NULLIF. COALESCE stops at the first non-null, so put cheap columns first. For NULL-safe equality, COALESCE is the wrong tool. Use IS NOT DISTINCT FROM or Spark <=>. In aggregates, SUM(COALESCE(amount, 0)) still returns NULL for no rows. Wrap the SUM if you need zero.

If you SELECT email, status FROM users WHERE email = ? and a nonclustered index is (email) INCLUDE (status), the lookup is covered. Interviewers ask this in SQL Server and PostgreSQL index-only scan talks. PostgreSQL needs the visibility map for index-only scans. Hive/Spark covering is different: a column-oriented Parquet file already reads only selected columns. Z-ORDER on the filter columns plus column projection is the lakehouse version. Do not copy every column into every index. Writes get slower and the index becomes the table.

Interviewers ask MERGE because almost every lakehouse pipeline needs it. Spark SQL and Delta: MERGE INTO target t USING source s ON t.id = s.id WHEN MATCHED THEN UPDATE ... WHEN NOT MATCHED THEN INSERT .... PostgreSQL often uses INSERT ... ON CONFLICT. Hive has MERGE on ACID tables. Production rules: the match key should be unique on both sides, or you get "multiple source rows matched" errors. Make the job idempotent so a rerun does not duplicate rows. Prefer merging only the partitions that changed.

This is the classic top-N-per-group question. Interviewers want PARTITION BY plus a rank function, not a global ORDER BY LIMIT. DENSE_RANK keeps ties in the top 2. ROW_NUMBER forces exactly two rows and can drop a tied product. In Spark, the window shuffles by category. If one category is huge, that partition can skew. Pre-aggregate before you rank so the window is small.

1. First we sum spend so each product in a category is one row.
2. Then we rank products inside each category.
3. Then we keep rank 1 and 2.
4. The result is the top-selling products per category, including ties if you use DENSE_RANK.

This is the old "second highest salary" puzzle, generalized. Interviewers care whether ties count as one place. For "the 2nd distinct salary", use DENSE_RANK. For "the 2nd row after sorting", use ROW_NUMBER. Spark SQL has no LIMIT 1 OFFSET n that is safe with ties. PostgreSQL DISTINCT salary ORDER BY salary DESC OFFSET n-1 is another form. In production, wrap it as a parameter and decide the tie rule out loud.

1. First we rank each salary among all employees, highest first.
2. Equal salaries share the same dense rank.
3. Then we keep only rank n (here 2).
4. The result is the nth distinct salary, or no rows if fewer than n salaries exist.

Running totals are the first window aggregate most interviews ask. They power balance sheets, cumulative signups, and stock of inventory. In Spark, ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the safe frame. RANGE treats equal timestamps as one group and can add extra rows. PostgreSQL is the same. On huge event tables, compute this in a batch job and store it, or use a streaming aggregation.

1. First we keep every order row.
2. Then for each user we walk orders in time order.
3. Each row's running_amount is that order plus all earlier orders for the same user.
4. The result is a cumulative spend column next to the detail.

LAG/LEAD questions test "look at the previous row" without a self join. Interviewers often want MoM or YoY percent change. Use a real month key such as date_trunc('month', dt) so missing months do not silently compare February to December. If a month has no sales, join a date spine first. Spark LAG(col, 1) is the previous row in the window. PostgreSQL is the same. In production, store the month grain table, then compute LAG on the small aggregate.

1. First we sum revenue to month grain.
2. Then LAG pulls the prior month's revenue.
3. Then we compute percent change and guard a zero previous month with NULLIF.
4. The result is each month, last month, and MoM percent.

This is the hardest common SQL pattern. Interviewers use it for streaks, uptime, and consecutive winning days. The trick: if dates go 1,2,3 and row numbers go 1,2,3, then date minus row number is constant. A gap changes the constant. Spark SQL: date_sub(login_date, rn). PostgreSQL: login_date - (rn || ' days')::interval or login_date - rn. Do not use this on timestamps with hours unless you first truncate to the grain you mean.

1. First we keep unique login dates per user.
2. Then we number those dates in order.
3. Then we subtract the number from the date to build an island id.
4. Then we group and the result is each consecutive streak with start, end, and length.

Sessionization is how product analytics defines a visit. Interviewers want the inactivity rule stated: 30 minutes since the last event, not 30 minutes since session start. Spark: compare unix_timestamp values. PostgreSQL: EXTRACT(EPOCH FROM (event_ts - prev_ts)). On streaming data, do this with watermarks. In batch Spark, the window shuffle is by user_id. Skewed bots with millions of events need a special path.

1. First we find each user's previous event time.
2. Then we mark a new session when the gap is over 30 minutes, or when it is the first event.
3. Then we cumulative-sum those marks.
4. The result is every event tagged with a session number per user.

This is how you clean CDC dumps, app events, and slowly changing snapshots. Interviewers expect ROW_NUMBER, not SELECT DISTINCT, because DISTINCT does not know which duplicate to keep. Spark: QUALIFY ROW_NUMBER() ... = 1 works in Databricks SQL. PostgreSQL: DISTINCT ON (user_id) ... ORDER BY user_id, updated_at DESC. In production, the order must include a version or ingest id. Two rows with the same timestamp will flip if you only sort by time.

1. First we number each user's rows, newest first.
2. A later ingest_id wins when timestamps match.
3. Then we keep only rn = 1.
4. The result is one current row per user_id.

This is the classic self-join. Interviewers want aliases and a clear join key: e.manager_id = m.emp_id. An inner join drops people with no manager. That is usually what you want here, because there is no manager salary to beat. In Spark, two copies of a large employee table still join on manager_id. For a deep org chart, this query is not enough. Use a recursive CTE.

1. First we join each employee to the row of their manager.
2. Then we keep rows where the employee salary is higher.
3. The result is the people who earn more than the person they report to.

Interviewers use this for "customers also bought". The < on product ids stops (milk, bread) and (bread, milk) from both appearing. Do not join on user only, or you mix different orders. In Spark this join can explode: an order with 20 items becomes 190 pairs. Filter to the top items first. PostgreSQL is the same SQL. For three-item sets the pattern grows fast. That is when people move to Spark MLlib FP-Growth.

1. First we pair items that appear in the same order.
2. The < keeps each pair in one direction.
3. Then we count how many orders contain that pair.
4. The result is a co-occurrence list you can sort for "bought together".

Conditional aggregation is how you build report grids. Interviewers want SUM(CASE...), not a mystery PIVOT keyword that differs by dialect. Spark and Hive: CASE. PostgreSQL: CASE or crosstab in tablefunc. Databricks has PIVOT. In production, a static list of months is fine for a 12-month report. For a dynamic set of columns, generate the SQL or keep the data long and pivot in the BI tool.

1. First we keep one output row per region.
2. Then each CASE picks the amount for one month and zero otherwise.
3. SUM folds those into a column.
4. The result is a wide report with January, February, and March amounts.

Cohort retention is a senior analytics question. Interviewers want the grain: one user has one cohort month. Use MIN(activity_date) per user, not the event month of every event as a new cohort. Spark months_between or datediff / 30 is approximate. Better: date_trunc('month', ...) and then month index. PostgreSQL: AGE or (year*12+month) subtraction. In production, freeze the cohort on first paid order, not first page view, or marketing and finance will fight.

1. First we compute each user's first month.
2. Then we list months when the user was active.
3. Then we group by cohort and month offset and count distinct users.
4. Then we divide by the month-0 size so the result is a retention grid.

Interviewers ask a date spine when charts drop days with no sales. Without the spine, a line chart jumps from Friday to Monday. Spark SQL: explode(sequence(start, end, interval 1 day)). Hive: a calendar table, or posexplode on a range. PostgreSQL: generate_series(start, end, interval '1 day'). datediff(end, start) in Spark is end - start in days. In Hive, datediff is the same argument order as Spark. Store a real calendar table in production so weekends and holidays are flags, not formulas.

1. First we generate every date in January 2024.
2. Then we sum revenue by day from orders.
3. Then we left join so quiet days stay in the result with 0.
4. datediff adds an integer day offset. In PostgreSQL use c.dt - DATE '2024-01-01' and generate_series.

Recursive CTEs walk trees and graphs. Interviewers use org charts, bill of materials, and folder paths. PostgreSQL: WITH RECURSIVE. Spark SQL supports recursive CTEs from 3.4. Older Hive needs a self-join loop in the job. Always set a depth cap. Real HR data has cycles from bad manager ids. In production, materialize the closure table (every ancestor pair) if many queries need "all reports of X".

1. First we pick employees with no manager as level 1.
2. Then we repeatedly add people who report to a person already in org.
3. lvl < 20 stops a cycle.
4. The result is each employee, their depth, and a manager path.

Interviewers ask median because AVG is pulled by outliers. Finance and marketplace interviews expect this. percentile_approx is fast and slightly inexact. Exact median on a huge Spark table is expensive. The row-number method shows you understand the definition. For even counts, the usual continuous median averages the two middle values. Discrete median picks one of them. Say which one you mean.

1. The first query asks Spark for the 50th percentile.
2. The second query numbers salaries from low to high.
3. It keeps the middle row, or the two middle rows when n is even.
4. AVG of those rows is the median. PostgreSQL: percentile_cont(0.5) WITHIN GROUP (ORDER BY salary).

Funnels test event order, not only counts. Interviewers care whether a user who paid without viewing still counts. Strict funnels require timestamps in order: view < cart < pay. Loose funnels only check that the events exist. In Spark, aggregate to user-step first so you do not count extra page views. In production, define the window (same session vs 7 days) or marketing and product will quote different rates.

1. First we take each user's first time at view, cart, and purchase.
2. Then we keep users who viewed, and left join later steps.
3. Then we count only steps that happened in time order.
4. The result is funnel counts and conversion rates.

"Consecutive days" is not COUNT(*) >= 3 in a week. Tuesday, Thursday, Saturday is three logins with gaps. Interviewers want the island length. You can also use LEAD(login_date, 1) and LEAD(login_date, 2) and check they equal date plus 1 and plus 2. That form is easy to read for a fixed 3. The island form generalizes to any n. Use SELECT DISTINCT dates first so two logins on Monday do not break the math.

1. First we unique the login dates.
2. Then we tag consecutive dates with an island id.
3. Then we count days in each island.
4. The result is users who had at least one 3-day streak.

This is a "time to second order" question. Interviewers use it for activation. LEAD on the first row, or a filter on ROW_NUMBER IN (1,2) plus a self join, both work. Spark: unix_timestamp(second) - unix_timestamp(first) <= 48 * 3600. datediff is whole days and is too coarse. PostgreSQL: second_ts <= first_ts + INTERVAL '48 hours'. Decide if a refunded order counts before you write SQL.

1. First we number each user's orders from earliest to latest.
2. Then we pivot the first and second timestamps onto one row.
3. Then we keep users whose second order exists and is within 48 hours.
4. The result is the fast-repeat cohort.

This appears in hotel bookings, shift planning, and IP allocations. Interviewers want the overlap predicate, not BETWEEN on one timestamp. Touching endpoints (one ends at 10:00, the next starts at 10:00) may or may not count. Use < if they do not overlap, <= if they do. In Spark, a self join of a large booking table is heavy. Bucket by room_id first. PostgreSQL also has range types and &&.

1. First we pair two different bookings on the same room.
2. booking_id < reports each pair once.
3. Then we keep pairs whose time ranges overlap.
4. The result is the conflicting bookings.

Interviewers distinguish a 7-row window from 7 calendar days. If Sunday has no sales, a 7-row window quietly includes an older day. Production metrics almost always want calendar days. Build the spine, left join, COALESCE to 0, then the frame. Spark also has RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW on timestamp columns in newer versions. PostgreSQL supports RANGE with intervals. Say which one you are computing.

1. First we build every date in the month and every user.
2. Then we fill missing spend with 0.
3. Then we average the current day and the six days before it.
4. The result is a true 7-calendar-day average per user.

This is the LeetCode-style warmup. Interviewers still use it to see NULL handling. A popular follow-up: return NULL when there is no second salary. Wrap the rank query in a scalar subquery, or use MAX(salary) WHERE salary < (SELECT MAX(salary) FROM employees). The MAX form does not extend to the 5th salary. Prefer rank.

1. First the subquery finds the highest salary.
2. Then we keep only salaries below that.
3. Then MAX of what remains is the second highest.
4. The result is NULL when there is no second distinct salary.

This is a data-quality classic. Interviewers want GROUP BY ... HAVING, not a self join of the whole table. Decide whether emails are case-insensitive. In PostgreSQL you may GROUP BY LOWER(email). Hive and Spark string compares are case-sensitive by default. In production, duplicates on a "unique" column mean the load path is not idempotent.

1. First we group rows that share an email.
2. Then HAVING keeps only emails that appear more than once.
3. The result is the duplicate emails and how many rows they have.

Interviewers check that you do not inner join and then wonder where the zero-order customers went. On large Spark tables, LEFT ANTI JOIN is the clean plan. PostgreSQL NOT EXISTS usually plans well. NOT IN (SELECT customer_id FROM orders) is wrong if any order has a NULL customer_id, because the whole predicate becomes unknown.

1. First we keep every customer and matching orders.
2. Customers with no order have NULL order columns.
3. Then we keep those NULL matches.
4. The result is customers who never ordered.

This tests group plus rank, not a nested MAX(AVG()) mess. Interviewers also accept ORDER BY avg_salary DESC LIMIT 1, but that hides ties. Spark: same SQL. If the department table has empty teams, decide whether AVG of no employees should appear. AVG ignores NULL salaries.

1. First we compute average salary per department.
2. Then we rank those averages, highest first.
3. Then we keep the top rank.
4. The result is the winning department, or more than one if they tie.

This is LeetCode Consecutive Numbers, and it still shows up. Interviewers want you to define "in a row" on a given order key, usually id. Gaps in id may or may not break the streak. Ask. Spark and PostgreSQL both have LAG/LEAD. For longer streaks, gaps-and-islands is less error-prone than many LEADs.

1. First we look at each row's neighbor values in id order.
2. Then we keep rows whose neighbors equal the current number.
3. DISTINCT returns the number once even if the streak is longer than three.
4. The result is values that appear three times in a row.

LeetCode Rising Temperature. Interviewers use it to see LAG or a date self join. The self join fails if dates are missing, unless you only want "previous calendar day exists". LAG compares the previous row, which might be two calendar days ago. Say which rule you want. Spark date_sub(record_date, 1) is the previous calendar day.

1. First we join today to the row from yesterday's calendar date.
2. Then we keep days that are warmer than that yesterday row.
3. The result is the ids of rising-temperature days. Missing calendar days produce no match.

Interviewers name this "rank scores". They want the difference among the three rank functions in one query. Spark, Hive, and PostgreSQL all have DENSE_RANK. Do not implement it with a correlated COUNT of distinct higher scores unless they forbid windows.

1. First we sort scores high to low inside the window.
2. Equal scores share a dense rank.
3. The next different score gets the next number with no gap.
4. The result is each score with a competition rank.

This is the department top-N follow-up to company-wide nth salary. Interviewers watch the PARTITION BY dept_id. Join the department name after the rank if the name lives in another table. In Spark, ranking after a filter on active employees is cheaper than ranking everyone.

1. First we rank employees inside their department by salary.
2. Then we keep the top three ranks.
3. Then we join department names.
4. The result is each department's top earners, including salary ties.

Interviewers check that you do not recurse. manager_id is one level. NULL manager ids are the CEO and should not form a group you care about. Spark: same group by. If they later ask for all descendants, that is the recursive CTE question.

1. First we join each employee to their manager.
2. Then we count rows per manager.
3. Then HAVING keeps managers with five or more directs.
4. The result is those managers and the report count.

Game-play analysis interviews start here, then add "logged in the next day" as a retention follow-up. MIN is enough for the first date. Use a window if you also need the whole first-day row. Spark and PostgreSQL are the same.

1. First we group events by player.
2. Then we take the earliest event date.
3. The result is one first-login date per player.

This is a simple tree question without recursion. Interviewers want CASE. Spark: id IN (SELECT p_id FROM tree WHERE p_id IS NOT NULL). Watch NULLs in the IN list. Filter p_id IS NOT NULL first. PostgreSQL is the same.

1. First we tag rows with no parent as Root.
2. Then we tag ids that never appear as a parent as Leaf.
3. Everyone else is Inner.
4. The result is one label per node.

Share-of-total is a daily dashboard question. Interviewers want a window, not a cross join to a one-row total, though both work. Spark needs * 1.0 or CAST or you get integer zero. PostgreSQL SUM(amount)::NUMERIC is safer. In production, define whether returns are negative amounts or a separate table.

1. First we sum revenue per product.
2. The window sum adds those product totals across the whole result.
3. Then we divide to get a percent.
4. The result is each product's share of company revenue.

YoY is the yearly cousin of MoM. Interviewers may want same-month last year at month grain: join on month_num and year = year + 1, or LAG(..., 12) on a monthly spine. Missing months break LAG(..., 12). Use a spine. Spark date_trunc('year', dt) is the year key. PostgreSQL is the same.

1. First we sum revenue by calendar year.
2. Then LAG fetches the previous year in the result.
3. Then we compute percent growth with a zero guard.
4. The result is YoY change. If a year is missing, join a year spine first.

DAU/WAU/MAU are standard product metrics. Interviewers want a parameter for "as of" date so the job is deterministic. Spark: event_date BETWEEN date_sub(report_date, 6) AND report_date for 7 inclusive days. Off-by-one errors are common. In production, pre-aggregate unique users per day, then union the last 7 day keys. Distinct on raw events every hour is expensive.

1. First we keep events on 24 Jun through 30 Jun inclusive.
2. Then we count unique users.
3. The result is 7-day active users as of 30 Jun 2024.

Churn definitions differ: last order, last login, or subscription cancel. Interviewers want you to pick one and write it. Spark datediff(report_date, last_order) > 90. Inclusive vs exclusive matters for finance. In production, churn is often a state table you merge daily, not a one-off filter, because users can reactivate.

1. First we find each user's latest order time.
2. Then we measure whole days from that order to the report date.
3. Then we keep users idle more than 90 days.
4. The result is the churned-by-inactivity list.

Interviewers repeat this because it shows up inside rolling averages, retention, and inventory. Spark sequence plus explode builds the calendar. PostgreSQL generate_series. Hive teams usually keep a dim_date table, which is the production answer. Do not generate 20 years of dates in every query if a table already exists.

1. First we list days and stores.
2. Then we cross them so every store-day exists.
3. Then we left join sales and sum, filling NULL with 0.
4. The result is a dense store-by-day series.

The reverse of CASE pivot. Interviewers may give jan_amt, feb_amt, mar_amt and want a month column. Spark SQL: STACK(3, '2024-01', jan_amt, '2024-02', feb_amt, '2024-03', mar_amt). PostgreSQL: UNION ALL or jsonb tricks. UNION ALL is the portable answer. In production, stop storing months as columns.

1. First we take each month column as its own SELECT.
2. Then UNION ALL stacks them into a long table.
3. Then we drop NULL amounts if that month had no value.
4. The result is one row per store per month.

Reconciliation is a data-engineer interview favorite. Interviewers want a checksum story for billions of rows: hash the payload, compare hashes, then drill into diffs. Spark: a.col <=> b.col is NULL-safe. PostgreSQL: IS NOT DISTINCT FROM. EXCEPT / MINUS is fine for small dimension dumps. In production, compare partition by partition.

1. First we full-join on the business key.
2. Then we label missing keys versus changed amounts.
3. <=> treats NULL amount as equal to NULL.
4. The result is the diff set. PostgreSQL: use IS NOT DISTINCT FROM instead of <=>.

Interviewers want a plan that is safe to rerun. OLTP: DELETE FROM t WHERE id IN (SELECT id FROM ranked WHERE rn > 1). Lakehouse: filter rn = 1 and replace the partition. Never SELECT DISTINCT * if two rows differ only on a junk column you still need to choose. Pick an order: latest updated_at, then largest id.

1. First we number duplicate emails, latest row first.
2. Then we keep rn = 1.
3. Then we overwrite the table with the clean set.
4. The result is unique emails. PostgreSQL would DELETE using ctid or a unique id instead of overwrite.

SCD Type 2 interviews always end here. Interviewers want the grain: one row per entity at that date. Overlapping valid_from/valid_to ranges are a data bug. Spark as-of joins exist in some APIs; in SQL the window method is portable. In production, constrain the scan to partitions near that date.

1. First we keep history rows that cover 15 Mar 2024.
2. Then we rank them latest valid_from first in case of bad overlaps.
3. Then we keep one row per customer.
4. The result is the customer status as of that date.

Attribution is a growth-analytics favorite. Last click is the simple rule. First click reverses the order. Linear split is a later follow-up. Interviewers want the time filter touch_ts <= convert_ts and often a lookback such as 7 days. Spark: join touches to conversions on user_id, then rank. In production, define whether paid and organic compete, and whether a touch is session-level or event-level.

1. First we find touches in the 7 days before each conversion.
2. Then we rank those touches latest first.
3. Then we keep the last touch.
4. The result is one channel per conversion. Conversions with no touch need a left join if you want a 'direct' bucket.

NTILE is how interviews ask Pareto and VIP segments. Interviewers know NTILE does not guarantee equal sums, only equal counts (almost). For true percentile ranks use PERCENT_RANK or CUME_DIST. Spark and PostgreSQL both have NTILE. In production, compute deciles on a fixed snapshot date so a customer does not jump buckets every hour.

1. First we total revenue per customer.
2. Then we split the ranked list into 10 buckets.
3. Decile 1 is the highest spenders.
4. The result is a VIP ranking you can filter as decile = 1.

Simple metric, easy to mess up the grain. Interviewers want users, not orders, in the denominator. A user with 10 orders is one repeat user. Time-box it: "repeat within 30 days of first order" is a different question (see second purchase within 48 hours). Spark COUNT(*) on a user-level flag table is cheap after the aggregate.

1. First we count orders per user.
2. Then we count how many users bought at least once and at least twice.
3. Then we divide.
4. The result is repeat purchase rate among buyers.

Interviewers use this for "distribution of order value". Fixed-width buckets are easy to explain. Percentile buckets (NTILE) are better when the tail is long. Spark: CASE or width_bucket is not always present; CASE is portable. PostgreSQL has width_bucket. In production, cap the last bucket as "500+".

1. First we map each order to a named amount bucket.
2. Then we count orders and sum revenue per bucket.
3. The prefix on the name keeps the sort order.
4. The result is a histogram table.

This is a favorite "gotcha" aggregate. Interviewers want to see the formula, not AVG(price). NULL qty should be excluded or coalesced. Spark and PostgreSQL: same. If qty can be zero, NULLIF(SUM(qty), 0) avoids divide by zero. In production this is VWAP for trades and average selling price for retail.

1. First we multiply price by quantity on each line.
2. Then we add those extensions and add quantities.
3. Then we divide to get a quantity-weighted price.
4. The result is one ASP per product.

Nested data is a Hive/Spark interview topic. Interviewers give ARRAY of tags on an event. LATERAL VIEW EXPLODE is the Hive form. Spark also allows SELECT explode(tags) AS tag. Do not explode before you filter the parent table if you can avoid it. In production, exploding a huge array of unique ids can blow up shuffle size.

1. First we skip rows with a NULL array.
2. Then we turn each array into one row per tag.
3. Then we count events per tag.
4. The result is a tag frequency table. PostgreSQL: FROM events, unnest(tags) AS tag.

This is the production upsert. Delta Lake, Iceberg, Hive ACID, and Snowflake all have MERGE. PostgreSQL: INSERT ... ON CONFLICT. Interviewers want the ON clause to match the grain. Duplicate keys in the source fail or duplicate updates. In Spark, merge only the partitions that changed (dt = ...) so you do not rewrite the whole table.

1. First we pick a unique staging set.
2. Then we match existing customers on customer_id.
3. We update only if the staging row is newer.
4. We insert customers that are not in the target. PostgreSQL would use ON CONFLICT (customer_id) DO UPDATE.

Interviewers ask this when there is no dim_date. It is the engine for spines, histograms, and filling ids. Recursive generation must cap the depth. Spark sequence is the right tool. Hive 2 often needs a dummy table of numbers. In production, materialize a calendar table once.

1. sequence builds an array from start to end.
2. explode turns that array into rows.
3. The date form steps by one day.
4. The result is a small tally or calendar you can join.

Interviewers catch the LAST_VALUE frame bug. Default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, so LAST_VALUE is often the current row, not the last in the partition. Fix: ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, or rank twice. Spark and PostgreSQL share this trap. For only timestamps, MIN/MAX is cleaner.

1. First we number events from the start and from the end.
2. Then we pick the names where those numbers are 1.
3. MIN/MAX give the times.
4. The result is each user's first and last event.

Interviewers like this because JOIN plus COUNT DISTINCT is cleaner than a double NOT EXISTS. It assumes the catalog table is the full set. If products can be inactive, count only active products. Spark: same SQL. Watch NULLs in product_id. Distinct will skip them.

1. First we list products each customer bought.
2. Then we count distinct product ids per customer.
3. Then we compare to the size of the product table.
4. The result is customers who covered the whole catalog.

Winsorizing is a data-science SQL question that now appears in DE loops. Interviewers want a window or a cross join of the percentile, not a manual WHERE amount < 10000 with a guessed number. Spark percentile_approx(amount, 0.99) is the scalable form. PostgreSQL percentile_cont(0.99). In production, compute the cap on a prior window so today's fraud spike does not set its own cap.

1. First we estimate the 99th percentile of amount.
2. Then we cap each order at that value with LEAST.
3. Then we average both capped and raw amounts.
4. The result shows how much the tail moved the mean. PostgreSQL: percentile_cont(0.99) WITHIN GROUP (ORDER BY amount).

Interviewers check grain again. Page-view events in the denominator make conversion look tiny. Use the eligible population: visitors, signups, or trial starts. Spark: COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END). In production, one user with many purchases still counts once.

1. First we count unique users in the event stream.
2. Then we count unique users who purchased.
3. Then we divide.
4. The result is user conversion rate.

This is a lighter version of the 48-hour second-purchase question. Interviewers want you to ask: calendar day or 24-hour window? Time zones change the date. Spark to_date(order_ts, 'UTC') should be explicit. In production, store a local_date from the user's zone if the metric is "same day".

1. First we count orders for each user on each calendar date.
2. Then we keep user-days with two or more orders.
3. The result is same-day repeat activity.

Interviewers at senior level ask this to see if you know it is not SUM(COUNT DISTINCT) OVER. You cannot add daily distincts. A user active two days would double-count. The exact method: tag each user's first seen date, then count users whose first date is on or before today. That is cumulative new users, which equals cumulative unique if you start from the beginning. Spark approx_count_distinct is not additive either unless you use HLL sketches (Databricks hll_sketch, Presto/Trino approx_set).

1. First we find each user's first seen date.
2. Then we build every date in the range.
3. Then we count users whose first date is on or before that day.
4. The result is exact cumulative unique users from the start of the data.

Interviews use mode for "most common status" and "most common error code". AVG and median do not apply to categories. PostgreSQL has MODE() WITHIN GROUP (ORDER BY col) but it picks one value on a tie. The rank query makes the tie visible. In production, ignore NULL unless NULL is a real category you care about.

1. First we count rows per status.
2. Then we rank those frequencies.
3. Then we keep the highest frequency.
4. The result is the mode, or more than one status if they tie.

Interviewers working on Databricks like QUALIFY because it is short. It is the same plan as a subquery. You still choose ROW_NUMBER vs DENSE_RANK. Do not put QUALIFY in a dialect-agnostic homework unless you mention the fallback. Production Spark jobs on open-source Spark 3 may not parse QUALIFY unless you are on Databricks SQL.

1. First we aggregate spend per product.
2. Then QUALIFY computes a row number inside each category.
3. Then it keeps the first two rows per category.
4. The result is top 2 products. In PostgreSQL wrap the window in a subquery and filter WHERE rn <= 2.