SQL Cheat Sheet for Data Engineer Interviews (2026)

-- 24.5% of SQL interview questions involve GROUP BY.
-- The HAVING/WHERE distinction is the most-tested trap.

-- Basic GROUP BY: every non-aggregated SELECT column must be in GROUP BY
SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

-- HAVING filters AFTER aggregation; WHERE filters BEFORE.
SELECT department, COUNT(*) AS headcount
FROM employees
WHERE active = true              -- filter rows first
GROUP BY department
HAVING COUNT(*) > 10;             -- then filter groups

-- Conditional aggregation: replace multiple subqueries with one pass.
SELECT
  DATE_TRUNC('month', order_date) AS month,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END) AS returns,
  ROUND(100.0 * SUM(CASE WHEN status = 'returned' THEN 1 ELSE 0 END)
    / COUNT(*), 1) AS return_pct
FROM orders
GROUP BY DATE_TRUNC('month', order_date);

-- ROLLUP adds subtotal + grand total rows. CUBE adds all combinations.
SELECT
  COALESCE(region, 'ALL REGIONS') AS region,
  COALESCE(product, 'ALL PRODUCTS') AS product,
  SUM(revenue) AS total_revenue
FROM sales
GROUP BY ROLLUP(region, product);

-- 19.6% of questions. INNER, LEFT, FULL OUTER, CROSS, self-joins.

-- INNER: only matching rows on both sides
SELECT o.order_id, c.name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

-- LEFT JOIN + COALESCE: keep all left rows even when no match
SELECT c.name, COALESCE(COUNT(o.order_id), 0) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- Anti-join: find left rows with NO right match. Asked in nearly every interview.
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_id IS NULL;

-- Self-join: hierarchies (employees → managers)
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- CROSS JOIN: scaffolding (date spine × product list)
SELECT d.date, p.product_id
FROM date_spine d
CROSS JOIN (SELECT DISTINCT product_id FROM products) p;

-- FULL OUTER: reconciliation (what's in A but not B and vice versa)
SELECT
  COALESCE(a.id, b.id) AS id,
  a.value AS source_a, b.value AS source_b
FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

-- 15.1% of questions. The senior-vs-mid signal.

-- ROW_NUMBER / RANK / DENSE_RANK — different tie behavior
ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn  -- (1,2,3,4)
RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rk  -- (1,2,2,4)
DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS dr  -- (1,2,2,3)

-- LAG / LEAD: look backward / forward within a partition
SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS mom_change
FROM monthly_revenue;

-- Running total / moving average — frame clause matters
SUM(amount) OVER (
  ORDER BY date
  ROWS UNBOUNDED PRECEDING
) AS running_total

AVG(amount) OVER (
  ORDER BY date
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS avg_7d

-- NTILE: divide rows into N buckets (quartiles, percentiles)
NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile

-- LAST_VALUE classic trap: default frame ends at CURRENT ROW.
-- Must be set to UNBOUNDED FOLLOWING for "last" to actually mean last.
LAST_VALUE(event_type) OVER (
  PARTITION BY user_id ORDER BY event_time
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_action

-- 4.9% of questions as standalone, but used in MOST complex queries.
-- Interviewers expect CTEs for readability whenever logic stacks.

-- Basic CTE
WITH active_users AS (
  SELECT user_id, COUNT(*) AS sessions
  FROM sessions
  WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT u.name, a.sessions
FROM users u
JOIN active_users a ON u.id = a.user_id;

-- Multiple CTEs in one WITH (commas, not multiple WITH keywords)
WITH
  recent AS (SELECT * FROM orders WHERE order_date >= '2025-01-01'),
  totals AS (SELECT customer_id, SUM(amount) AS total FROM recent GROUP BY customer_id)
SELECT * FROM totals WHERE total > 1000;

-- Recursive CTE: date spine, org chart, graph traversal
WITH RECURSIVE date_spine AS (
  SELECT DATE '2024-01-01' AS dt        -- anchor
  UNION ALL
  SELECT dt + INTERVAL '1 day'           -- recursive member
  FROM date_spine
  WHERE dt < '2024-12-31'                -- termination
)
SELECT ds.dt, COALESCE(r.revenue, 0) AS revenue
FROM date_spine ds
LEFT JOIN daily_revenue r ON ds.dt = r.day;

-- 1.8% standalone, but embedded in 40%+ of complex queries.

-- Searched CASE: top-to-bottom evaluation, first TRUE wins
CASE
  WHEN salary >= 150000 THEN 'Executive'
  WHEN salary >= 100000 THEN 'Senior'
  WHEN salary >= 60000  THEN 'Mid'
  ELSE 'Junior'
END AS level

-- Pivoting with CASE — works in every engine
SELECT user_id,
  MAX(CASE WHEN key = 'email' THEN value END) AS email,
  MAX(CASE WHEN key = 'phone' THEN value END) AS phone
FROM user_attributes
GROUP BY user_id;

-- CASE inside COUNT for conditional counts
SELECT
  COUNT(*) AS total_orders,
  COUNT(CASE WHEN status = 'returned' THEN 1 END) AS returned_orders,
  COUNT(CASE WHEN status = 'shipped'  THEN 1 END) AS shipped_orders
FROM orders;

-- EXISTS / NOT EXISTS — NULL-safe, unlike NOT IN
SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.id
);
-- Prefer NOT EXISTS over NOT IN whenever the subquery column may be NULL.
-- NOT IN returns NO rows if ANY value in the subquery is NULL. Classic trap.

-- Scalar subquery: returns ONE value
SELECT name, salary,
  salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;
-- Errors if the subquery returns >1 row.

-- IN with a subquery: usually rewriteable as a JOIN for better plans
SELECT * FROM orders
WHERE customer_id IN (SELECT id FROM customers WHERE country = 'US');

-- MERGE: standard upsert. Postgres got it in v15.

MERGE INTO dim_customer AS target
USING staging AS source ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET
    name = source.name,
    updated_at = CURRENT_TIMESTAMP
WHEN NOT MATCHED THEN
  INSERT (id, name, created_at)
  VALUES (source.id, source.name, CURRENT_TIMESTAMP);

-- Postgres alternative (pre-15 and still common): ON CONFLICT
INSERT INTO dim_customer (id, name, updated_at)
VALUES (1, 'Alice', NOW())
ON CONFLICT (id) DO UPDATE SET
  name = EXCLUDED.name,
  updated_at = EXCLUDED.updated_at;
-- EXCLUDED refers to the row that WOULD have been inserted.

-- MySQL: INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO dim_customer (id, name) VALUES (1, 'Alice')
ON DUPLICATE KEY UPDATE name = VALUES(name);

-- Almost every interview involves a time dimension.
-- Syntax varies HEAVILY across engines; always name your target.

-- DATE_TRUNC (Postgres / Snowflake)
DATE_TRUNC('month', order_date)

-- DATE_TRUNC (BigQuery — argument order reversed)
DATE_TRUNC(order_date, MONTH)

-- Date arithmetic (Postgres uses INTERVAL)
CURRENT_DATE - INTERVAL '30 days'

-- Date arithmetic (SQL Server / Snowflake use DATEADD)
DATEADD(day, -30, GETDATE())

-- Day difference
DATEDIFF(day, start_date, end_date)         -- SQL Server
DATE_DIFF(end_date, start_date, DAY)        -- BigQuery
end_date - start_date                       -- Postgres returns days

-- EXTRACT works in most engines
EXTRACT(YEAR FROM order_date)
EXTRACT(DOW FROM order_date)   -- day of week (numbering varies)
EXTRACT(EPOCH FROM ts_col)     -- unix timestamp

-- ISO week (consistent across engines unlike "week")
EXTRACT(ISOYEAR FROM date_col)
EXTRACT(WEEK FROM date_col)

-- UNION vs UNION ALL — most-tested set-operation trap

-- UNION removes duplicates (slower; sorts and dedupes)
SELECT id FROM table_a
UNION
SELECT id FROM table_b;

-- UNION ALL keeps duplicates (faster; use unless you need dedup)
SELECT id FROM table_a
UNION ALL
SELECT id FROM table_b;

-- INTERSECT: rows in BOTH
SELECT id FROM table_a
INTERSECT
SELECT id FROM table_b;

-- EXCEPT: rows in A but not B (called MINUS in Oracle)
SELECT id FROM table_a
EXCEPT
SELECT id FROM table_b;

-- String functions appear in data cleaning questions.

-- Concatenation
first_name || ' ' || last_name              -- ANSI / Postgres
CONCAT(first_name, ' ', last_name)          -- MySQL / SQL Server / BigQuery
-- || propagates NULL; CONCAT treats NULL as empty string

-- TRIM / LOWER / REPLACE for join key cleanup
TRIM(BOTH ' ' FROM raw_input)
LOWER(email)
REPLACE(phone, '-', '')
-- Always TRIM+LOWER before joining on string keys. Untrimmed
-- whitespace causes silent join failures (the worst kind).

-- Split by delimiter
SPLIT_PART('a.b.c', '.', 2)                          -- Postgres → 'b'
SPLIT('a.b.c', '.')[OFFSET(1)]                       -- BigQuery → 'b'
STRING_TO_ARRAY('a,b,c', ',')                        -- Postgres → array

-- Regex (engine-specific)
REGEXP_REPLACE(phone, '[^0-9]', '', 'g')             -- Postgres / Snowflake
REGEXP_REPLACE(phone, r'[^0-9]', '')                 -- BigQuery
phone REGEXP '^[0-9]+$'                              -- MySQL boolean

-- Pattern matching
LIKE 'abc%'                                          -- prefix match
ILIKE 'abc%'                                         -- case-insensitive (Postgres)
SIMILAR TO 'pattern'                                 -- regex-lite