SQL JOIN Practice for Data Engineers (2026)

-- INNER JOIN: returns ONLY rows that match in both tables.
-- Default and most common. Interviewers test that you understand
-- unmatched rows are silently dropped.

-- Total spend per customer (matched customers only)
SELECT c.name, SUM(o.amount) AS total_spend
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
GROUP BY c.name
ORDER BY total_spend DESC;

-- Three-way JOIN to find active paying users
SELECT u.id, u.email
FROM users u
INNER JOIN subscriptions s ON s.user_id = u.id
INNER JOIN payments p ON p.subscription_id = s.id
WHERE s.status = 'active'
  AND p.paid_at >= CURRENT_DATE - INTERVAL '30 days';

-- Practice problems (5):
-- 1. Join orders to customers and calculate total spend per customer
-- 2. Find products ordered at least once (filter never-ordered products)
-- 3. Match employees to department names, filter by hire_date
-- 4. Three-way join: users + subscriptions + payments for active payers
-- 5. Find pairs of students enrolled in the same course (self-join via INNER)

-- LEFT JOIN: returns ALL rows from the left table.
-- NULLs fill in where the right table has no match.
-- The most interview-relevant join type.

-- Customers who NEVER ordered (the anti-join pattern)
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.order_id IS NULL;
-- WHERE IS NULL converts LEFT JOIN to "left table only, no match"

-- Revenue per category INCLUDING categories with $0
SELECT cat.name, COALESCE(SUM(o.amount), 0) AS revenue
FROM categories cat
LEFT JOIN products p ON p.category_id = cat.id
LEFT JOIN order_items oi ON oi.product_id = p.id
LEFT JOIN orders o ON o.id = oi.order_id
GROUP BY cat.id, cat.name;
-- COALESCE turns NULL → 0 for zero-revenue categories

-- Practice problems (5):
-- 1. Find customers who have never placed an order
-- 2. List products with most recent review, INCLUDING products with zero reviews
-- 3. Every employee + manager name, including the CEO (no manager)
-- 4. Revenue per category including categories with $0
-- 5. Users who signed up but never completed onboarding

-- SELF-JOIN: a table joined to itself.
-- For hierarchies, pairs, and consecutive events.

-- Employee + manager from a single employees table
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Customers who ordered on CONSECUTIVE days
SELECT DISTINCT o1.customer_id
FROM orders o1
INNER JOIN orders o2
  ON o1.customer_id = o2.customer_id
  AND o2.order_date = o1.order_date + INTERVAL '1 day';

-- Employees who earn MORE than their manager
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
INNER JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- Practice problems (5):
-- 1. Each employee and their direct manager from a single employees table
-- 2. Customers who placed orders on consecutive days
-- 3. All pairs of products in the same category with price diff under $10
-- 4. Flights where arrival city = departure city of another flight (connections)
-- 5. Employees who earn more than their manager

-- INEQUALITY JOIN: condition uses <, >, BETWEEN instead of =.
-- Date ranges, attribution windows, interval matching.

-- Match each sale to the price tier active on that date
SELECT s.sale_id, s.sale_date, pt.tier_name, s.amount
FROM sales s
INNER JOIN price_tiers pt
  ON s.sale_date BETWEEN pt.valid_from AND pt.valid_to;

-- Events within 30 minutes of a user signup
SELECT e.event_id, u.user_id, e.event_time
FROM users u
INNER JOIN events e
  ON e.user_id = u.user_id
  AND e.event_time BETWEEN u.signup_at AND u.signup_at + INTERVAL '30 minutes';

-- Attribute conversions to the most recent marketing touch
SELECT c.conversion_id, m.touch_id, c.conversion_time, m.touch_time
FROM conversions c
LEFT JOIN LATERAL (
  SELECT *
  FROM marketing_touches mt
  WHERE mt.user_id = c.user_id
    AND mt.touch_time <= c.conversion_time
  ORDER BY mt.touch_time DESC
  LIMIT 1
) m ON true;

-- Practice problems (5):
-- 1. Match each sale to the price tier active on that date
-- 2. Events within 30 minutes of a user signup
-- 3. Attribute conversions to the most recent marketing touch
-- 4. Salary history records with overlapping date ranges
-- 5. Orders matched to the exchange rate valid on the order date

-- CROSS JOIN: Cartesian product. Every row × every row.
-- For generating combinations, date spines, gap-filling.

-- Date spine × regions for reporting scaffold
SELECT d.dt, r.region_name, COALESCE(s.revenue, 0) AS revenue
FROM generate_series(DATE '2024-01-01', DATE '2024-12-31', INTERVAL '1 day') d(dt)
CROSS JOIN regions r
LEFT JOIN daily_sales s ON s.day = d.dt AND s.region_id = r.id;

-- All product-store combinations (find missing stocks)
SELECT p.product_id, s.store_id, COALESCE(i.qty, 0) AS qty
FROM products p
CROSS JOIN stores s
LEFT JOIN inventory i
  ON i.product_id = p.product_id
  AND i.store_id = s.store_id
WHERE i.qty IS NULL OR i.qty = 0;

-- Round-robin tournament: all team matchups
SELECT t1.name AS home, t2.name AS away
FROM teams t1
CROSS JOIN teams t2
WHERE t1.id < t2.id;  -- avoid (A,A) and duplicate (A,B)/(B,A)

-- Practice problems (5):
-- 1. All product-store combinations to find missing stocks
-- 2. Date spine × regions reporting scaffold
-- 3. Round-robin tournament matchups
-- 4. Pair every user with every survey question (find unanswered)
-- 5. Multiplication table from two integer sequences

-- FULL OUTER JOIN: keeps all rows from BOTH sides.
-- For reconciliation and data diffing.

-- Compare two source systems for data quality
SELECT
  COALESCE(s.id, w.id) AS id,
  s.amount AS source_amount,
  w.amount AS warehouse_amount,
  CASE
    WHEN s.id IS NULL THEN 'missing in source'
    WHEN w.id IS NULL THEN 'missing in warehouse'
    WHEN s.amount <> w.amount THEN 'amount mismatch'
    ELSE 'match'
  END AS diff_type
FROM source_orders s
FULL OUTER JOIN warehouse_orders w ON s.id = w.id
WHERE s.id IS NULL OR w.id IS NULL OR s.amount <> w.amount;

-- Find IDs in A but not B (and vice versa) in one query
SELECT
  COALESCE(a.id, b.id) AS id,
  CASE WHEN a.id IS NULL THEN 'only in B'
       WHEN b.id IS NULL THEN 'only in A'
       ELSE 'both' END AS membership
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

-- LATERAL JOIN (Postgres): subquery executed per row of the outer table.
-- Advanced pattern for "top N per group" without window functions.
-- Often produces cleaner plans than ROW_NUMBER for small N.

-- Top 3 most recent orders per customer
SELECT c.id, c.name, o.order_id, o.order_date, o.amount
FROM customers c
LEFT JOIN LATERAL (
  SELECT order_id, order_date, amount
  FROM orders
  WHERE customer_id = c.id
  ORDER BY order_date DESC
  LIMIT 3
) o ON true;

-- Most recent failed login per user
SELECT u.id, u.email, lf.attempted_at, lf.ip_address
FROM users u
LEFT JOIN LATERAL (
  SELECT attempted_at, ip_address
  FROM login_failures
  WHERE user_id = u.id
  ORDER BY attempted_at DESC
  LIMIT 1
) lf ON true;