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.

HardTechnical

45 practiced

Write an optimized SQL query to compute 'first purchase within 30 days' retention as of each user signup, while preventing double counting when users have multiple purchases on the same day. Schemas: users(user_id, signup_date), purchases(purchase_id, user_id, purchase_ts). Provide a solution that de-duplicates by user-day before calculating the first purchase.

Sample Answer

Approach: first collapse purchases to one row per user per day (take earliest timestamp that day), then for each user find their earliest purchase date within 30 days of signup, then aggregate retention by signup_date. Use window functions and minimal joins for efficiency.

sql

WITH dedup_day AS (
  -- one purchase row per user per day (earliest ts that day)
  SELECT
    user_id,
    (purchase_ts::date) AS purchase_date,
    MIN(purchase_ts) AS first_ts_that_day
  FROM purchases
  GROUP BY user_id, purchase_ts::date
),
first_within_30 AS (
  -- for each user, find earliest purchase_date within 30 days after signup
  SELECT
    u.user_id,
    u.signup_date,
    MIN(d.purchase_date) AS first_purchase_date
  FROM users u
  LEFT JOIN dedup_day d
    ON d.user_id = u.user_id
    AND d.purchase_date BETWEEN u.signup_date AND (u.signup_date + INTERVAL '30 days')::date
  GROUP BY u.user_id, u.signup_date
)
SELECT
  signup_date,
  COUNT(*) FILTER (WHERE first_purchase_date IS NOT NULL) AS retained_count,
  COUNT(*) AS cohort_size,
  ROUND(100.0 * COUNT(*) FILTER (WHERE first_purchase_date IS NOT NULL) / NULLIF(COUNT(*),0), 2) AS retention_pct
FROM first_within_30
GROUP BY signup_date
ORDER BY signup_date;

Key points:- dedup_day prevents multiple same-day purchases from inflating “first” calculations.- Use MIN(...) per user to get true earliest day-level purchase.- Left join preserves users with no purchase.- Add indexes on purchases(user_id, purchase_ts) and users(user_id, signup_date) for performance.

Edge cases:- Timezones: cast to date consistently (use AT TIME ZONE if needed).- Users with signup_date late in day: define inclusive/exclusive boundaries explicitly.Alternatives:- Use ROW_NUMBER() over dedup_day per user ordered by purchase_date then pick row_number=1 per user within 30 days.- If dataset large, prefilter purchases to purchase_ts BETWEEN min(signup_date) AND max(signup_date)+30 to reduce scan.

EasyTechnical

39 practiced

Consider this query (PostgreSQL):

SELECT c.customer_id, o.order_idFROM customers cLEFT JOIN orders o ON c.customer_id = o.customer_idWHERE o.status = 'SHIPPED';

Explain why this LEFT JOIN behaves like an INNER JOIN in practice and rewrite the query to correctly return all customers with shipped orders while preserving other customers with NULLs (i.e., customers with no orders must still appear).

Sample Answer

The LEFT JOIN behaves like an INNER JOIN because the WHERE clause is applied after the join and filters out any rows where o.status is NULL. For customers with no orders the joined o.* values are NULL, so the condition WHERE o.status = 'SHIPPED' evaluates to false (or unknown) and those rows are removed — effectively turning the outer join into an inner join.

Correct approach: move the condition into the JOIN so it only restricts which orders are matched, not whether the customer row survives:

sql

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
  AND o.status = 'SHIPPED';

Key points:- This returns every customer; for customers with one or more SHIPPED orders you'll get matching order_id(s); for customers with no orders or no shipped orders you'll get NULLs for o.*.- Alternative: keep the JOIN as-is and change the WHERE to allow NULLs: WHERE o.status = 'SHIPPED' OR o.customer_id IS NULL — less clear/idiomatic than pushing the filter into the ON clause.

MediumTechnical

60 practiced

Explain how using DISTINCT in combination with joins can hide underlying join errors (e.g., duplicate explosion) and why DISTINCT is often a band-aid. Describe a systematic approach to remove duplicates at the source, verify canonical keys, and rewrite the join to produce correct aggregates without relying on DISTINCT.

Sample Answer

Using DISTINCT after a join can mask the real problem: duplicate explosion from many-to-one or many-to-many relationships. DISTINCT hides symptom (duplicate rows) rather than fixing cause (bad join cardinality, missing keys, or duplicated source records). That’s why DISTINCT is often a band‑aid — it can produce apparently “correct” row counts while hiding wrong aggregates or masking data-quality issues.

Systematic approach to fix it:

1. Reproduce and quantify the problem- Run counts before and after the join to see multiplicative increase:

sql

SELECT COUNT(*) FROM orders; -- 1000
SELECT COUNT(*) FROM order_lines; -- 4000
SELECT COUNT(*) FROM orders o JOIN order_lines l ON o.id = l.order_id; -- 4000 (expected)

2. Verify canonical keys and uniqueness- Check primary/unique constraints and identify duplicates:

sql

SELECT order_id, COUNT(*) FROM order_lines GROUP BY order_id HAVING COUNT(*) > 1;

- If duplicates are unexpected, investigate source ETL.

3. Remove duplicates at the source (preferred)- Fix ingestion/ETL to enforce uniqueness (PKs, dedupe step).- If immediate fix impossible, create canonical deduped tables or views.

4. Rewrite joins to dedupe explicitly (don’t rely on DISTINCT)- Use aggregation or window functions to collapse many-side rows to the intended grain before joining:

sql

-- keep only latest order_line per order_line_id
WITH dedup_lines AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY order_line_id ORDER BY updated_at DESC) rn
  FROM order_lines
)
SELECT o.id, o.customer, l.amount
FROM orders o
LEFT JOIN dedup_lines l ON o.id = l.order_id AND l.rn = 1;

- For aggregated metrics, pre-aggregate the child table:

sql

WITH agg_lines AS (
  SELECT order_id, SUM(amount) total_amount
  FROM order_lines
  GROUP BY order_id
)
SELECT o.id, o.customer, COALESCE(a.total_amount,0)
FROM orders o
LEFT JOIN agg_lines a ON o.id = a.order_id;

5. Verify results with targeted tests- Row-level checks: compare counts, sums with and without dedupe.- Edge-case tests: orders without lines, multiples, null keys.

Why this is better:- Preserves correct cardinality and aggregate semantics.- Makes data-quality issues visible and fixable.- More efficient than DISTINCT over large joins.

Best practices:- Enforce canonical keys in DB/schema.- Deduplicate as close to source as possible.- Use explicit dedupe (ROW_NUMBER or GROUP BY) and pre-aggregation.- Add unit tests and monitoring to catch regression of duplicate injection.

EasyTechnical

46 practiced

NULLs in join keys and duplicate rows often surprise analysts. Given two tables A(id, val) and B(id, val): explain how SQL equality predicates treat NULL keys during joins, how NULL keys can lead to missing matches, and provide two strategies to include NULL-keyed rows in join logic when appropriate (e.g., grouping NULLs together or using surrogate keys).

Sample Answer

SQL equality predicates treat NULL as "unknown", not equal to anything — including other NULLs. So a join like A.id = B.id will exclude rows where either id is NULL because NULL = NULL evaluates to NULL (false in a WHERE/JOIN filter). That causes surprising missing matches and apparent loss of NULL-keyed rows.

Consequences:- Rows with NULL join keys won’t match each other, producing fewer rows than expected.- Duplicate rows combined with NULLs can hide true groupings or inflate counts if you try to deduplicate without handling NULLs.

Two strategies to include NULL-keyed rows when appropriate:

1) Group NULLs explicitly (coalesce to a sentinel)- Replace NULL with a sentinel value that cannot appear in real data (or use a special tag) so NULLs compare equal.Example:

sql

SELECT *
FROM A
JOIN B
  ON COALESCE(A.id, -1) = COALESCE(B.id, -1);

Use caution: choose a sentinel that won't collide with real ids.

2) Use surrogate keys or surrogate grouping identifier- Create a generated key (e.g., row_uuid or hashed composite) or assign the same surrogate id to logically related NULL records during ETL, then join on that surrogate.Example ETL idea:- Assign group_id = CASE WHEN id IS NULL THEN generate_group() ELSE id END- Join on group_id.

Best practices:- Prefer fixing upstream data (avoid NULLs in join keys) if possible.- Document sentinel choice and ensure consistency across tables.- Consider the semantics — only group NULLs when they truly represent the same logical key.

MediumTechnical

37 practiced

Write a PostgreSQL query using LATERAL (or CROSS APPLY) to join each customer to their single most recent address (addresses(customer_id, address_id, effective_from)). Make sure the query scales when addresses are large by pushing filters early.

Sample Answer

Approach: use a LATERAL subquery that, for each customer, immediately restricts addresses to that customer and returns the single row with the greatest effective_from (ORDER BY ... DESC LIMIT 1). This pushes the customer filter into the index seek for addresses so the planner can do an indexed lookup per customer rather than scanning the whole addresses table.

Query (Postgres):

sql

SELECT
  c.customer_id,
  c.name,
  a.address_id,
  a.effective_from,
  a.address_line
FROM customers c
LEFT JOIN LATERAL (
  SELECT address_id, effective_from, address_line
  FROM addresses
  WHERE customer_id = c.customer_id
  ORDER BY effective_from DESC
  LIMIT 1
) a ON true;

Key points / reasoning:- LATERAL runs the subquery for each row of customers; because the subquery filters by customer_id, the planner can use an index on addresses(customer_id, effective_from DESC) to perform an efficient index-only/ index-backed order+limit lookup.- LEFT JOIN LATERAL preserves customers without addresses; change to JOIN LATERAL if every customer must have an address.- Pushing WHERE customer_id = c.customer_id into the lateral subquery is essential for scaling; it avoids a global sort or window function over all addresses.

Index recommendation:

sql

CREATE INDEX idx_addr_cust_eff DESC ON addresses (customer_id, effective_from DESC);

This makes ORDER BY ... DESC LIMIT 1 efficient per customer.

Alternatives and trade-offs:- DISTINCT ON approach:

sql

SELECT DISTINCT ON (customer_id) *
FROM addresses
ORDER BY customer_id, effective_from DESC;

This can be simpler but typically requires sorting or grouping over the whole addresses set — less scalable on very large tables unless combined with additional filters or partitioning.- Window functions (ROW_NUMBER()) over partition may be heavier because they compute row_number for all rows before filtering.

Edge cases:- Ties on effective_from: if multiple addresses share the same timestamp, add a deterministic tiebreaker in ORDER BY (e.g., address_id).- Very large customer sets: consider batching customers or using partitioned addresses table if writes/archival patterns justify it.

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.