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

44 practiced

A reporting requirement asks to join events to user subscription periods where subscriptions can be paused and resumed, resulting in multiple disjoint periods per user. Write SQL to determine whether an event_ts falls into any active subscription period for that user and to compute total active minutes per day per user without double counting overlapping subscription segments.

Sample Answer

Approach:1) For event membership: join each event timestamp to the user's subscription segments and check if event_ts BETWEEN start_ts AND end_ts.2) For daily active minutes without double-counting: merge overlapping/adjacent subscription segments per user into disjoint intervals, then either (A) truncate intervals to each calendar day and sum minutes, or (B) explode per-minute using generate_series (Postgres) — but merging is preferred for performance.

Postgres example that (a) flags events inside any active segment and (b) computes total active minutes per user per day by merging overlaps:

sql

-- Sample tables: events(user_id, event_ts), subs(user_id, start_ts, end_ts)
-- 1) Flag events inside any subscription segment
SELECT
  e.*,
  CASE WHEN s.user_id IS NOT NULL THEN true ELSE false END AS in_active_sub
FROM events e
LEFT JOIN LATERAL (
  SELECT 1
  FROM subs s
  WHERE s.user_id = e.user_id
    AND e.event_ts >= s.start_ts
    AND e.event_ts < s.end_ts  -- treat end exclusive if desired
  LIMIT 1
) s ON true;

-- 2) Merge overlapping segments and compute minutes per user/day
WITH ordered AS (
  SELECT
    user_id,
    start_ts,
    end_ts,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY start_ts, end_ts) rn
  FROM subs
),
with_prev AS (
  SELECT
    o.*,
    LAG(start_ts) OVER (PARTITION BY user_id ORDER BY start_ts) prev_start,
    LAG(end_ts)   OVER (PARTITION BY user_id ORDER BY start_ts) prev_end
  FROM ordered o
),
-- identify group starts where current does not overlap previous merged end
grp AS (
  SELECT
    user_id, start_ts, end_ts,
    SUM(CASE WHEN prev_end IS NULL OR prev_end < start_ts THEN 1 ELSE 0 END)
      OVER (PARTITION BY user_id ORDER BY start_ts ROWS UNBOUNDED PRECEDING) AS grp_id
  FROM with_prev
),
merged AS (
  SELECT
    user_id,
    MIN(start_ts) AS seg_start,
    MAX(end_ts)   AS seg_end
  FROM grp
  GROUP BY user_id, grp_id
),
-- break merged segments into per-day pieces
per_day AS (
  SELECT
    user_id,
    date_trunc('day', gs)::date AS day,
    GREATEST(seg_start, date_trunc('day', gs)) AS piece_start,
    LEAST(seg_end, date_trunc('day', gs) + INTERVAL '1 day') AS piece_end
  FROM merged
  CROSS JOIN LATERAL generate_series(date_trunc('day', seg_start),
                                     date_trunc('day', seg_end),
                                     INTERVAL '1 day') gs
)
SELECT
  user_id,
  day,
  SUM(EXTRACT(EPOCH FROM (piece_end - piece_start)) / 60) AS active_minutes
FROM per_day
GROUP BY user_id, day
ORDER BY user_id, day;

Key points:- We merge per-user intervals by marking group starts where prior merged end < current start, then aggregate to produce disjoint segments.- date_trunc + generate_series splits intervals across days without double-counting.- Use end exclusive or inclusive consistently (above uses end exclusive).Edge cases:- Null end_ts (ongoing subscription): treat end_ts = now() or a sentinel.- Adjacent intervals (end == next start): decide if they should merge — adjust comparison prev_end < start_ts to <= for merging adjacency.- Timezones: ensure timestamps use same timezone / convert to UTC before grouping.- Very large data: consider windowing, partitioning, or materializing merged segments in a summary table for performance.

EasyTechnical

33 practiced

You have an employee hierarchy table Employees(emp_id INT, manager_id INT, name TEXT). As a BI analyst, write a query to list each employee with their top-level manager (the manager with manager_id IS NULL) using a self-join or an alternative approach. Explain when you would use recursive CTEs vs iterative joins and implications for deep hierarchies.

Sample Answer

To find each employee’s top-level manager (manager_id IS NULL), a recursive CTE is the simplest and most robust approach for unknown-depth hierarchies. For shallow, fixed-depth trees you could use iterative self-joins.

Recursive CTE approach (recommended):

sql

WITH RECURSIVE chain AS (
  SELECT emp_id, manager_id, name, emp_id AS root_emp, manager_id AS cur_manager
  FROM Employees
  UNION ALL
  SELECT c.emp_id, e.manager_id, c.name, c.root_emp, e.manager_id
  FROM chain c
  JOIN Employees e ON c.cur_manager = e.emp_id
  WHERE c.cur_manager IS NOT NULL
)
SELECT
  e.emp_id,
  e.name,
  top.name AS top_manager_name,
  top.emp_id AS top_manager_id
FROM (
  SELECT emp_id, MAX(cur_manager) FILTER (WHERE cur_manager IS NULL) AS top_manager_id
  FROM chain
  GROUP BY emp_id
) t
JOIN Employees e ON e.emp_id = t.emp_id
LEFT JOIN Employees top ON top.emp_id = t.top_manager_id;

Cleaner variant: walk up until manager_id IS NULL and select that row in the CTE final step.

Iterative self-joins (when depth is known and small, e.g., 3 levels):

sql

SELECT e.emp_id, e.name,
       COALESCE(m3.name, m2.name, m1.name, e.name) AS top_manager_name
FROM Employees e
LEFT JOIN Employees m1 ON e.manager_id = m1.emp_id
LEFT JOIN Employees m2 ON m1.manager_id = m2.emp_id
LEFT JOIN Employees m3 ON m2.manager_id = m3.emp_id;

When to use which:- Use recursive CTEs for arbitrary/deep hierarchies or when depth varies; they are clearer and maintainable.- Use iterative joins only for small, fixed maximum depths (slightly faster in some DBs) or when recursive queries are unsupported.

Implications for deep hierarchies:- Recursive CTEs can hit recursion limits (configurable) and may be slower on very deep trees; ensure proper indexing on emp_id/manager_id.- For very large or extremely deep hierarchies, consider materialized paths, ancestor lookup tables, or precomputed top-manager columns updated via ETL for reporting performance.

MediumTechnical

46 practiced

A BI metric requires joining multiple event types into a single timeline per user and ensuring events from different tables are merged without duplication. Describe a SQL approach to union event types, assign a global_event_id, and join to users while preserving source type and preventing duplicates caused by overlapping ingestion windows.

Sample Answer

Approach (brief): Combine all event tables using UNION ALL into a single timeline per user, add a source column, compute a deterministic global_event_id (e.g., hash of user_id + event_time + source + source_event_id) to deduplicate overlapping ingestion windows, then LEFT JOIN to users. Use row_number() to pick one canonical event per unique key when duplicates occur.

SQL example (Postgres / BigQuery compatible):

sql

WITH raw_events AS (
  SELECT user_id, event_ts, 'pageview'   AS src, pageview_id   AS src_id, payload FROM pageviews
  UNION ALL
  SELECT user_id, event_ts, 'click'      AS src, click_id      AS src_id, payload FROM clicks
  UNION ALL
  SELECT user_id, event_ts, 'purchase'   AS src, purchase_id   AS src_id, payload FROM purchases
),
with_global_id AS (
  SELECT
    *,
    -- deterministic global id: hex(hash) or concat
    md5(CONCAT(user_id::text, '|', TO_CHAR(event_ts, 'YYYY-MM-DD"T"HH24:MI:SS.US'), '|', src, '|', src_id::text)) AS global_event_id
  FROM raw_events
),
deduped AS (
  SELECT
    user_id, event_ts, src, src_id, payload, global_event_id,
    ROW_NUMBER() OVER (PARTITION BY global_event_id ORDER BY event_ts DESC) AS rn
  FROM with_global_id
)
SELECT u.*, e.user_id AS event_user, e.event_ts, e.src, e.src_id, e.payload, e.global_event_id
FROM users u
LEFT JOIN deduped e
  ON u.id = e.user_id AND e.rn = 1;

Key points:- UNION ALL preserves source; add src/src_id to trace origin.- global_event_id must be deterministic and include source and source primary key to avoid accidental collisions.- Use ROW_NUMBER() over global_event_id to dedupe overlapping ingestion (same logical event arriving twice).- Consider ingestion_time vs event_ts: if ingestion window causes duplicates, include event_ts and src_id in hash; or use LEAST/ROW with ingestion_time to keep earliest.- For scale, create a staging table with the unioned rows and index global_event_id; use incremental dedupe (MERGE) for production pipelines.

Edge cases:- Clock skew: normalize timestamps or include ingestion_time.- Late-arriving events: decide upsert window.- NULLs in fields: coalesce before hashing.

Alternatives:- Use surrogate sequence (bigserial) if ordering only matters.- Use fingerprint functions (farm_fingerprint, xxhash) for speed on large datasets.

EasyTechnical

43 practiced

You need to join two datasets from different sources with slightly different key definitions: Orders(source_a_order_id INT, customer_email TEXT) and CRM(customer_id INT, email_address TEXT). Describe and write SQL to perform a robust join on email, considering case sensitivity and leading/trailing whitespace, and explain how you would detect and handle mismatches caused by typos or multiple accounts per email.

Sample Answer

Approach (brief): normalize email strings on both sides (trim, lowercase, remove accidental internal whitespace, optionally remove unicode accents and provider-specific quirks like Gmail dots), perform a deterministic join on the normalized email, then detect probable mismatches using fuzzy matching (Levenshtein) and reconcile multiple accounts per email by aggregation or business rules.

SQL (Postgres example):

sql

-- deterministic normalized join
WITH norm_a AS (
  SELECT source_a_order_id,
         lower(trim(both from regexp_replace(customer_email, '\s+', '', 'g'))) AS email_norm,
         customer_email
  FROM Orders
),
norm_c AS (
  SELECT customer_id,
         lower(trim(both from regexp_replace(email_address, '\s+', '', 'g'))) AS email_norm,
         email_address
  FROM CRM
)
SELECT a.source_a_order_id, a.customer_email, c.customer_id, c.email_address
FROM norm_a a
LEFT JOIN norm_c c
  ON a.email_norm = c.email_norm;

Detecting mismatches / typos:- Find unmatched orders: LEFT JOIN where c.customer_id IS NULL and inspect counts.- Use fuzzy match to suggest candidates (requires pg_trgm or fuzzystrmatch):

sql

-- example using levenshtein for suggestions
SELECT a.source_a_order_id, a.customer_email, c.customer_id, c.email_address,
       levenshtein(a.email_norm, c.email_norm) AS dist
FROM norm_a a
CROSS JOIN LATERAL (
  SELECT customer_id, email_norm, email_address
  FROM norm_c
  ORDER BY levenshtein(a.email_norm, email_norm)
  LIMIT 3
) c
WHERE a.email_norm NOT IN (SELECT email_norm FROM norm_c);

Handling multiple accounts per email:- If one-to-many in CRM, decide rule: prefer most recent/active account, aggregate orders to email-level, or flag for manual review.- Example aggregation before join:

sql

SELECT email_norm, max(customer_id) FILTER (WHERE active) AS preferred_customer_id
FROM norm_c
GROUP BY email_norm;

Notes / best practices:- Consider provider-specific rules (Gmail: ignore dots and +tags).- Use unaccent(...) for unicode normalization.- Track and log ambiguous matches; manually resolve high-impact cases and feed corrections back to master data/ETL to improve future joins.

MediumTechnical

33 practiced

Given two tables Events(user_id INT, start_ts TIMESTAMP, end_ts TIMESTAMP) representing user sessions, write SQL to find overlapping sessions per user (pairs of session_ids that overlap in time). Provide a query that avoids comparing a session to itself and handles nullable end_ts (treat null as ongoing). Explain performance implications.

Sample Answer

Approach: do a self-join per user to find pairs of sessions whose time intervals intersect. Avoid self-comparison and duplicate pairs by assigning a stable session_id (if table doesn't have one) and only keeping pairs where left.id < right.id. Treat NULL end_ts as ongoing by using COALESCE(end_ts, '9999-12-31') or current_timestamp depending on requirements.

SQL (adds session_id if missing via row_number):

sql

WITH events_with_id AS (
  SELECT
    COALESCE(session_id, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY start_ts, end_ts)) AS session_id,
    user_id,
    start_ts,
    COALESCE(end_ts, TIMESTAMP '9999-12-31') AS end_ts
  FROM events
)
SELECT
  e1.user_id,
  e1.session_id AS session_a,
  e2.session_id AS session_b,
  GREATEST(e1.start_ts, e2.start_ts) AS overlap_start,
  LEAST(e1.end_ts, e2.end_ts) AS overlap_end
FROM events_with_id e1
JOIN events_with_id e2
  ON e1.user_id = e2.user_id
  AND e1.session_id < e2.session_id        -- avoid self and duplicate pairs
  AND e1.start_ts <= e2.end_ts             -- potential overlap
  AND e2.start_ts <= e1.end_ts             -- actual overlap
WHERE LEAST(e1.end_ts, e2.end_ts) >= GREATEST(e1.start_ts, e2.start_ts);

Notes and performance:- Self-joins are O(n^2) per user worst-case; large users with many sessions are the bottleneck.- Add indexes: (user_id, start_ts), (user_id, end_ts) to help the join filter. Partition data by user_id or by date ranges if applicable.- Pre-filter by time windows (e.g., only recent N months) to limit rows.- For very large datasets consider: interval indexes (if DB supports), range types (Postgres tstzrange + && operator), or a sweep-line/windowing algorithm implemented in SQL or ETL to produce overlaps more efficiently.

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.