Complex Joins and Set Operations Questions

Focuses on mastering joins and set operations for combining and transforming relational data across multiple tables. Candidates should understand all join types including inner, left, right, full outer, cross joins, self joins, and nested joins, and know when to use each for correctness and performance. This topic also covers set operations such as UNION, INTERSECT, and EXCEPT, differences between joins and set operations, handling duplicates and NULL values correctly, choosing between joins, subqueries, and common table expressions for clarity and efficiency, and reasoning about join order and its performance implications on large tables. Interview questions may include multi table join problems, complex business logic across four or more tables, and scenarios that reveal trade offs between approaches.

MediumTechnical

76 practiced

Given the following schema:

customers(customer_id INT PK, name VARCHAR)orders(order_id INT PK, customer_id INT, order_date DATE)order_items(item_id INT PK, order_id INT, sku VARCHAR, amount DECIMAL)products(sku VARCHAR PK, category VARCHAR)

Write an ANSI SQL query to compute total revenue per customer for March 2024 including customers with zero orders. Explain pitfalls that cause double-counting and how to avoid them.

Sample Answer

Approach: start from customers (to include those with zero orders) and LEFT JOIN only March 2024 orders and their order_items. Avoid joining before filtering because filtering in WHERE on a left-joined table can turn it into an inner join; also avoid joining a table that expands rows (e.g., items) before aggregating — that can double-count if you aggregate at the wrong grain. Best practice: either filter in the JOIN condition or aggregate detail rows first, then join.

SQL (ANSI):

sql

SELECT
  c.customer_id,
  c.name,
  COALESCE(SUM(oi.amount), 0) AS revenue_march_2024
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
  AND o.order_date BETWEEN DATE '2024-03-01' AND DATE '2024-03-31'
LEFT JOIN order_items oi
  ON oi.order_id = o.order_id
GROUP BY c.customer_id, c.name
ORDER BY revenue_march_2024 DESC;

Key pitfalls and how to avoid them:- Filtering in WHERE on an outer-joined table (e.g., WHERE o.order_date BETWEEN ...) converts the LEFT JOIN to an INNER JOIN and drops customers with no March orders. Place the date filter in the ON clause or use WHERE o.order_date IS NULL OR ... to preserve NULLs.- Double-counting: if you join additional detail (order_items) and then join another detail-level table (e.g., shipments) without pre-aggregation, multiplicative row expansion can inflate sums. Fix by aggregating at a stable grain first (e.g., sum amounts per order or per order_item) and then joining those aggregates.- NULL amount values: use COALESCE(amount,0) when aggregating.- Returns/refunds: negative amounts must be modeled appropriately; include them in the sum if they exist.- Performance: for large data, pre-aggregate order_items (SUM by order_id) before joining to customers to reduce shuffle/scan size. Example pre-aggregation:

sql

WITH order_sum AS (
  SELECT order_id, SUM(COALESCE(amount,0)) AS order_amount
  FROM order_items
  GROUP BY order_id
)
SELECT c.customer_id, c.name, COALESCE(SUM(os.order_amount),0) AS revenue_march_2024
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id
  AND o.order_date BETWEEN DATE '2024-03-01' AND DATE '2024-03-31'
LEFT JOIN order_sum os
  ON os.order_id = o.order_id
GROUP BY c.customer_id, c.name;

This preserves customers with zero orders and prevents aggregation errors from row explosion.

HardTechnical

61 practiced

In a distributed engine (Spark/Presto/BigQuery) you need to join a 500M-row fact table with a 5M-row dimension. Explain strategies to reduce shuffle: broadcast the smaller table, repartition on join key, use bloom filters, and leverage partitioning. For each strategy describe cost/benefit and when it's appropriate.

Sample Answer

Brief summary: joining a 500M-row fact to a 5M-row dimension can be done with several shuffle-reduction techniques. I'll cover broadcast, repartition/co-partition, bloom filters (semi-join), and partitioning/clustering — with costs, benefits, and when to use each.

1) Broadcast the smaller table- Benefit: avoids shuffling the large fact; join happens locally on executors — fastest.- Cost: memory/driver overhead to hold the broadcasted table on each worker; network cost to distribute it once.- When appropriate: the dimension is small enough to fit comfortably in executor memory. Rule of thumb: in Spark default autoBroadcastJoinThreshold ~10MB; in practice test whether 5M rows × row-width (e.g., 100 bytes → ~500MB) fits. BigQuery/Presto do replicated joins when the table is small (check their limits).

2) Repartition (hash shuffle) on join key / co-partition- Benefit: deterministic, general approach: both sides partitioned by key; minimizes cross-partition lookups after shuffle.- Cost: full shuffle of large dataset(s) — expensive I/O, network, and CPU; needs good parallelism to distribute load.- When appropriate: dimension too large to broadcast, or joins are many-to-many and broadcast impossible. If you can pre-partition and reuse the partitioning for multiple queries (persistent clustered tables), cost amortizes.

3) Bloom filters / semi-join (push-down)- Benefit: build a compact bloom filter from the dimension, broadcast that to filter the fact table before shuffle — reduces rows that must be shuffled/joined.- Cost: false positives let some unnecessary rows through; bloom filter creation and broadcast still cost resources; effectiveness depends on selectivity.- When appropriate: dimension selective on the fact (i.e., only a small fraction of fact keys exist in dimension or you can pre-filter time ranges). Useful when dimension is moderately sized (too big to broadcast whole but small enough to build a compact filter). In Spark use DataFrame APIs or Hyperspace; BigQuery supports approximate joins via broadcasted filters/filters with EXISTS.

4) Leverage partitioning / clustering at storage level- Benefit: partition/prune the fact table on relevant predicate (date, region) so less data participates in the join; clustering by join key keeps matching keys colocated and reduces shuffle in some engines.- Cost: requires data modeling and storage/maintenance; may increase ingestion complexity; if queries don’t use the partitioning predicates, no benefit.- When appropriate: recurring analytical queries that filter by time/segment — partition by date and cluster by join key (BigQuery clustering, Spark bucketing) so joins become cheaper and can avoid full shuffle with techniques like dynamic partition pruning.

Practical approach: estimate memory footprint of the 5M dim; try broadcast if under safe threshold; else build bloom filter to pre-filter fact; if joins are frequent and predictable, invest in partitioning/clustering or pre-bucket/co-partition tables to amortize shuffle. Monitor shuffle read/write, executor OOMs, and network usage to validate.

HardTechnical

72 practiced

You have an expensive daily aggregation that joins orders and order_items by day. Propose a materialized view or pre-aggregation strategy to support KPIs with near-real-time updates. Describe refresh options (complete vs incremental), handling late-arriving data, and trade-offs in maintenance cost vs query speed in systems like Postgres and Snowflake.

Sample Answer

Approach (what I’d build)- Create a daily-materialized aggregation that joins orders -> order_items grouped by order_date producing KPIs (orders, items_sold, revenue, avg_order_value, returned_count, etc.). Store partitioned by date (date column) and include last_updated_timestamp and source_watermark columns for incremental logic.

Refresh options- Complete refresh: truncate + recompute a date range (cheap to implement). Use for backfills or schema changes. Costly for large ranges and near-real-time needs.- Incremental refresh: upsert only affected dates using CDC/modified_at: - Use change tables (orders_delta, order_items_delta) or CDC stream to identify changed order_ids and affected order_dates. - Recompute aggregates for only those dates and MERGE into materialized table. - Maintain tombstone/deletes handling via soft-delete flags or delete logs.

Handling late-arriving data- Keep a rolling “recompute window” (e.g., last 7 days). Any record arriving after initial load triggers recompute for its order_date.- Expose two KPI columns: kpi_stable (older than window) vs kpi_latest (within window) to signal freshness.- Store source_watermark per partition and surface data quality/time-lag in dashboards.

Trade-offs & cost considerations- Query speed: pre-agg gives millisecond dashboard queries.- Maintenance cost: incremental + CDC complexity increases engineering effort; complete is simpler but costly at scale.- Freshness vs cost: shorter recompute window -> fresher data but higher compute.

Postgres vs Snowflake specifics- Postgres: use matviews (REFRESH MATERIALIZED VIEW), but native incremental refresh limited; implement partitioned tables + triggers or scheduled jobs that upsert deltas. Watch VACUUM and locking during refresh; prefer per-day partitions to minimize locking.- Snowflake: use streams + tasks for near-real-time incremental pipelines; MERGE into clustering-keyed aggregate table; auto-suspend/auto-resume computes cost but scales. Snowflake zero-copy clone useful for safe backfills. Consider micro-batching to balance cost.

Monitoring & validation- Track row counts, watermarks, and reconciliation queries (full vs aggregated) and alert when diffs exceed thresholds. Provide dashboard freshness indicators to consumers.

EasyTechnical

83 practiced

Explain the difference between UNION and UNION ALL. Given two monthly_sales tables (jan_sales, feb_sales) with identical schema (customer_id, amount), write queries to combine them while (a) preserving duplicates and (b) removing duplicates. Discuss when UNION ALL is preferable in BI pipelines and the cost of deduplication.

Sample Answer

To combine result sets, UNION ALL concatenates rows from both sets preserving duplicates; UNION (equivalently UNION DISTINCT) removes duplicate rows by performing deduplication (typically via hashing or sorting).

Approach and queries:

- Preserve duplicates (fast, no dedupe):

sql

SELECT customer_id, amount FROM jan_sales
UNION ALL
SELECT customer_id, amount FROM feb_sales;

- Remove duplicates (dedupe):

sql

SELECT customer_id, amount FROM jan_sales
UNION
SELECT customer_id, amount FROM feb_sales;

(Or explicitly: UNION DISTINCT)

Key points:- UNION ALL is cheaper: it streams rows without extra memory/CPU for dedupe.- UNION incurs extra cost: database must identify and eliminate identical rows (hash aggregate or sort+unique), using CPU, memory, and possibly disk spill for large data.- NULL handling: NULLs compare equal for deduplication, so rows differing only by NULL positions may be collapsed.

When to prefer UNION ALL in BI pipelines:- Use UNION ALL when source partitions (months) are disjoint by design (e.g., primary keys include month) or when you want to preserve duplicate transactions (raw ingestion).- Prefer UNION (dedupe) when true uniqueness across sources is required for reporting (e.g., avoid double-counting invoices) and upstream systems might produce duplicates.

Cost of deduplication:- CPU/RAM proportional to rows to compare; can cause sorting/hash aggregation which may spill to disk and increase query latency and cost.- For large BI workloads, dedupe is often done incrementally (e.g., de-dup during ETL using window functions, keys, change data capture) rather than on-the-fly with UNION to minimize repeated expensive dedupe operations.

MediumTechnical

62 practiced

Compare Common Table Expressions (CTEs) and subqueries for readability and performance. Given a complex query joining four tables to produce cohort metrics, rewrite it using a WITH CTE for clarity and discuss whether CTEs are materialized inline or not in Postgres, SQL Server and BigQuery and how that affects performance.

Sample Answer

Readable vs performant:- Readability: CTEs (WITH ...) make complex logic modular — break a big JOIN into named steps (e.g., cohort_base, events_30d, purchases) which BI analysts and stakeholders can read, test, and comment independently. Subqueries (inline or correlated) can be more compact but harder to scan and debug when there are many nested levels.- Performance: Not guaranteed by form. Whether a CTE is materialized (forced temp result) or inlined into the main query determines the optimizer's ability to push filters/join reordering. Materialized CTEs can increase I/O if large and only used once; they can help if reuse or if you need stable snapshot semantics.

Example — cohort metrics (4 tables: users u, user_attributes a, events e, purchases p). This calculates each cohort’s new users and conversions in 30 days:

WITH cohort_base AS ( -- users grouped by signup week SELECT u.user_id, date_trunc('week', u.signup_ts) AS cohort_week FROM users u JOIN user_attributes a ON a.user_id = u.user_id WHERE a.active = TRUE),events_30d AS ( -- events in 30 days after signup SELECT c.user_id, c.cohort_week, e.event_type FROM cohort_base c JOIN events e ON e.user_id = c.user_id AND e.event_ts BETWEEN c.signup_ts AND c.signup_ts + interval '30 days'),purchases_30d AS ( SELECT c.user_id, c.cohort_week, SUM(p.amount) AS revenue FROM cohort_base c JOIN purchases p ON p.user_id = c.user_id AND p.purchase_ts BETWEEN c.signup_ts AND c.signup_ts + interval '30 days' GROUP BY c.user_id, c.cohort_week)SELECT cb.cohort_week, COUNT(DISTINCT cb.user_id) AS new_users, COUNT(DISTINCT e.user_id) FILTER (WHERE e.event_type = 'activate') AS activated, COUNT(DISTINCT p.user_id) AS purchasers, SUM(p.revenue) AS total_revenueFROM cohort_base cbLEFT JOIN events_30d e ON e.user_id = cb.user_idLEFT JOIN purchases_30d p ON p.user_id = cb.user_idGROUP BY cb.cohort_weekORDER BY cb.cohort_week;

Materialization behavior by engine (implications):- PostgreSQL: versions < 12 treat plain CTEs as optimization fences (materialized) — safe semantics but can hurt performance because planner cannot inline and push down predicates. PostgreSQL 12+ will inline CTEs when safe (improves performance) unless the CTE is referenced multiple times or explicitly MATERIALIZED. Use MATERIALIZED / NOT MATERIALIZED hints (Postgres 12+) to control behavior when needed.- SQL Server: CTEs are syntactic sugar; they are effectively inlined into the main query (not materialized), so the optimizer can push predicates and reorder joins. For large intermediate reuse, consider temp tables or table variables if you need to avoid recomputation.- BigQuery: By default CTEs are inlined and the optimizer may rewrite/reuse common subexpressions; BigQuery also supports a MATERIALIZED hint to force materialization when beneficial. For heavy reuse or expensive subexpressions, explicitly MATERIALIZED can reduce repeated computation.

Practical advice for BI:- Use CTEs for readability and incremental testing.- Benchmark on representative data: compare inlined subquery vs CTE; check query plans.- If you need snapshot semantics or want to avoid recomputation of expensive steps referenced multiple times, force materialization (engine-specific) or write to a temp table.

Unlock Full Question Bank

Get access to hundreds of Complex Joins and Set Operations interview questions and detailed answers.

Join thousands of developers preparing for their dream job.