Complex Data Integration and Joins Questions

Handling intricate join scenarios: multi-condition joins, conditional joins with complex logic, joining on date ranges or overlapping time periods, complex left joins with multiple filtering conditions, self-joins for hierarchical or relationship data, handling non-standard relationships between tables. Understanding implications of different join types on row counts, NULL values, and duplicate handling. Designing queries that correctly integrate data from multiple sources while maintaining data integrity and avoiding duplicate counting or missing data.

EasyTechnical

41 practiced

You see a query using LEFT JOIN that returns fewer rows than the left table. Describe diagnostic SQL steps to find the cause: check join cardinality, nulls in join keys, misplaced WHERE filters, and unexpected duplicates on the right table. Provide specific queries you would run to debug.

Sample Answer

Start by reproducing the symptom and then isolate causes with focused queries.

1) Confirm counts and basic LEFT JOIN behavior

sql

-- original count
SELECT COUNT(*) FROM left_table lt;

-- count after LEFT JOIN (how many rows remain)
SELECT COUNT(*) FROM left_table lt
LEFT JOIN right_table rt ON lt.key = rt.key;

2) Check for NULLs in join keys on the left (NULLs won't match)

sql

SELECT COUNT(*) AS null_left_keys
FROM left_table
WHERE key IS NULL;
SELECT COUNT(*) AS nonnull_left_keys
FROM left_table
WHERE key IS NOT NULL;

3) Find rows in left with no match on right (helpful to sample)

sql

SELECT lt.*, rt.key AS matched_key
FROM left_table lt
LEFT JOIN right_table rt ON lt.key = rt.key
WHERE rt.key IS NULL
LIMIT 50;

4) Check for NULLs or unexpected values in right join key (e.g., empty strings, casing)

sql

SELECT COUNT(*) FROM right_table WHERE key IS NULL;
SELECT key, COUNT(*) FROM right_table GROUP BY key ORDER BY COUNT(*) DESC LIMIT 10;

5) Detect duplicates on the right causing row multiplication (and possibly filtering by WHERE)

sql

SELECT key, COUNT(*) AS cnt
FROM right_table
GROUP BY key
HAVING COUNT(*) > 1
ORDER BY cnt DESC
LIMIT 20;

6) Verify WHERE filters aren’t turning the LEFT JOIN into an INNER JOIN

sql

-- problematic: filter on right_table in WHERE
SELECT COUNT(*) FROM left_table lt
LEFT JOIN right_table rt ON lt.key = rt.key
WHERE rt.some_col = 'value';  -- this removes rows where rt is NULL

-- correct: move filter into JOIN
SELECT COUNT(*) FROM left_table lt
LEFT JOIN right_table rt ON lt.key = rt.key AND rt.some_col = 'value';

7) Check join cardinality by grouping

sql

SELECT lt.key, COUNT(rt.key) AS matches
FROM left_table lt
LEFT JOIN right_table rt ON lt.key = rt.key
GROUP BY lt.key
ORDER BY matches DESC
LIMIT 20;

8) Sample problematic rows and inspect data quality

sql

SELECT lt.*, rt.*
FROM left_table lt
LEFT JOIN right_table rt ON lt.key = rt.key
WHERE lt.key IN ('example1','example2');

Actions: if NULLs in left keys — clean or filter; if duplicates on right — deduplicate or aggregate (use DISTINCT, MAX, or aggregate join); if WHERE on right — move condition into JOIN; if mismatched formats — normalize keys (TRIM/LOWER/cast).

MediumTechnical

36 practiced

In a schema with country dimension and multiple regional tables, describe how a wrong join key (e.g., joining on country name instead of country_code) could produce missing or duplicate rows. Provide a small SQL example and describe validation checks you would add to CI/CD for queries to detect such problems.

Sample Answer

Joining on the wrong key (country name) can cause missing rows when names are inconsistent, and duplicate rows when multiple countries share the same name or when regional tables contain repeated names. Use a stable surrogate (country_code) to avoid these issues.

Example (wrong vs correct):

sql

-- Wrong: joining on country name (may be inconsistent or non-unique)
SELECT r.*, c.population
FROM regional_sales r
LEFT JOIN dim_country c
  ON r.country_name = c.country_name;

-- Correct: join on unique stable key
SELECT r.*, c.population
FROM regional_sales r
LEFT JOIN dim_country c
  ON r.country_code = c.country_code;

Why problems occur:- Missing rows: "United States" vs "USA" mismatches -> unmatched LEFT JOIN becomes NULL.- Duplicate rows: if dim_country has historical rows or name variants, join can multiply regional rows.

CI/CD validation checks to add:- Schema checks: enforce primary key/unique constraint on dim_country.country_code.- Join-referential tests: assert every country_code in regional tables exists in dim_country (COUNT(*) of unmatched keys = 0).- Null-coverage tests: after joins, fail build if >X% of rows have NULLs in critical lookup columns.- Uniqueness tests: verify dim_country.country_code is unique; verify (country_name) duplicates flagged.- Sampling/data-diff tests: compare row counts aggregated by country_code between source and joined outputs to detect unexpected drops/duplicates.- Alerting for sudden changes: monitor daily mismatch rates and trigger CI failure if they exceed threshold.

These guardrails catch both mismatches and multiplicative joins early, keeping reports accurate.

MediumTechnical

66 practiced

Given event logs from two systems with slightly different timestamps, write a query to join events when their timestamps are within +/- 2 minutes for the same user_id. Tables:

sql

app_a(user_id, event_time, event_a_type)
app_b(user_id, event_time, event_b_type)

Provide an efficient SQL pattern to do this and explain potential pitfalls with naive joins.

Sample Answer

Approach: do a time-window (fuzzy) join per user, but avoid naive cross-join by restricting to same user and small time range. Prefer a lateral (or windowed) join that finds the closest matching event in B for each A (or vice‑versa), and ensure indexes on (user_id, event_time).

Postgres example — find closest B within ±2 minutes for each A:

sql

SELECT a.user_id, a.event_time AS a_time, a.event_a_type,
       b.event_time AS b_time, b.event_b_type,
       EXTRACT(EPOCH FROM (a.event_time - b.event_time))/60 AS minutes_diff
FROM app_a a
LEFT JOIN LATERAL (
  SELECT b.*
  FROM app_b b
  WHERE b.user_id = a.user_id
    AND b.event_time BETWEEN a.event_time - INTERVAL '2 minutes' 
                         AND a.event_time + INTERVAL '2 minutes'
  ORDER BY ABS(EXTRACT(EPOCH FROM (b.event_time - a.event_time)))
  LIMIT 1
) b ON true;

BigQuery example (multiple matches allowed):

sql

SELECT a.user_id, a.event_time AS a_time, a.event_a_type,
       b.event_time AS b_time, b.event_b_type
FROM app_a a
JOIN app_b b
  ON a.user_id = b.user_id
 AND ABS(TIMESTAMP_DIFF(a.event_time, b.event_time, SECOND)) <= 120;

Key points / pitfalls:- Naive join without user/time filters causes O(N*M) explosion — very slow.- Multiple matches: decide semantics (closest match, all matches, deduplicate). Use ROW_NUMBER() partitioned by a.id ORDER BY time diff to pick closest.- Indexes: create composite index on (user_id, event_time) to speed range scans.- Time issues: ensure consistent timezones and data types (timestamp vs timestamptz).- Boundary behavior: inclusive/exclusive window choice; handling identical timestamps.- Late/earlier duplicates: if many events cluster, you may need business rules to aggregate or limit matches.

MediumTechnical

62 practiced

You have two tables with many-to-many relationships causing multiplicative rows when joined. Describe a step-by-step approach to produce a de-duplicated report of counts per dimension without over-counting. Include SQL patterns (subqueries/CTEs/aggregation order) and why order matters.

Sample Answer

Situation: Many-to-many joins multiply rows and over-count aggregates. Goal: count unique entities per dimension without double-counting.

Step-by-step approach:1. Clarify grain and keys — decide the entity you want to count uniquely (e.g., users, orders, products).2. Pre-aggregate at the correct grain before joining other tables. Use CTEs/subqueries to deduplicate first.3. Join only aggregated results or use distinct counts on stable keys after controlled joins.4. Validate by comparing counts from raw vs. deduped pipelines.

SQL patterns (examples):

-- 1) Pre-aggregate each many‑to‑many side

sql

WITH user_product AS (
  SELECT user_id, product_id
  FROM user_product_map
  WHERE active = 1
  GROUP BY user_id, product_id
),
product_category AS (
  SELECT product_id, category_id
  FROM product_category_map
  GROUP BY product_id, category_id
),
-- 2) compute unique users per category by joining deduped CTEs and aggregating at target grain
users_per_category AS (
  SELECT pc.category_id, up.user_id
  FROM product_category pc
  JOIN user_product up USING (product_id)
  GROUP BY pc.category_id, up.user_id
)
SELECT category_id, COUNT(*) AS unique_users
FROM users_per_category
GROUP BY category_id;

Why order matters:- Aggregating early reduces row explosion and ensures each logical relationship (user→product, product→category) is represented once. If you join raw many-to-many tables first, duplicates propagate making COUNT(*) or SUM(...) incorrect.- Use GROUP BY in CTEs to enforce the intended grain. If you need metrics like revenue, aggregate revenue at its natural grain (e.g., order_line) before joining to avoid inflating sums.

Alternatives & tips:- Use COUNT(DISTINCT key) after joining if supported and performant, but beware of performance on big datasets.- For complex metrics, compute numerator and denominator at same deduped grain and then join.- Add explain/analyze and sanity checks (counts at each CTE) to confirm deduplication.

MediumTechnical

35 practiced

Explain lateral joins (CROSS APPLY / LATERAL) and give a practical data-analyst example where a lateral join simplifies queries compared to correlated subqueries or joins. Provide a SQL example in PostgreSQL finding the top-selling product per category.

Sample Answer

A LATERAL join (Postgres: LATERAL, SQL Server: CROSS APPLY) lets a subquery reference columns from the current row of the FROM item to its left. Unlike a plain subquery, LATERAL is evaluated per row and can return a set — useful for top-N-per-group, nearest-neighbor, or calling set-returning functions.

Why it simplifies queries: it avoids correlated subqueries with repeated aggregation or awkward windowing + DISTINCT ON hacks, and it expresses per-group logic clearly.

Practical example — find top-selling product per category using sales table (sales(product_id, qty, sale_date)), products(product_id, name, category_id), categories(category_id, name). We compute total qty per product, then for each category select top product:

sql

WITH prod_sales AS (
  SELECT p.product_id, p.name, p.category_id, SUM(s.qty) AS total_qty
  FROM products p
  LEFT JOIN sales s ON s.product_id = p.product_id
  GROUP BY p.product_id, p.name, p.category_id
)
SELECT c.category_id, c.name AS category_name, t.product_id, t.name AS top_product, t.total_qty
FROM categories c
CROSS JOIN LATERAL (
  SELECT ps.product_id, ps.name, ps.total_qty
  FROM prod_sales ps
  WHERE ps.category_id = c.category_id
  ORDER BY ps.total_qty DESC NULLS LAST
  LIMIT 1
) t;

Key points:- LATERAL runs the inner query per category, naturally expressing top-1-per-group.- Cleaner and often more efficient than correlated subqueries or complex window functions.

Unlock Full Question Bank

Get access to hundreds of Complex Data Integration and Joins interview questions and detailed answers.

Join thousands of developers preparing for their dream job.