SQL interview questions · Data engineer
17 SQL interview questions for data engineers
Data engineering interviews go past reporting queries. You'll be asked to clean and deduplicate data, keep tables correct with upserts and constraints, reconcile two systems, and handle sequences like sessions and streaks, often with an eye on correctness at scale.
What data engineer SQL interviews test
- Deduplicating and keeping the right row per key
- Upserts (INSERT ... ON CONFLICT) and safe UPDATE/DELETE
- Reconciling two sources and finding orphaned rows
- Recursive CTEs for hierarchies
- Gaps and islands, sessionization and filling missing dates
Data quality and deduplication
- Missing values per sourceFor each source in crm_contacts, return source, contacts (all rows), missing_email, missing_phone and missing_country (rows where that column is NULL). Sort by source.Pattern: COUNT(*) - COUNT(col) · data qualityPro
- Duplicates hiding behind case and spacesTreating emails as equal after trimming spaces and lowercasing, return email (the normalized value), copies (how many contacts share it) and first_id (the lowest id among them) for every email used by 2 or more contacts. Ignore contacts with no email. Sort by email.Pattern: LOWER · TRIM · GROUP BY · HAVINGPro
- Delete duplicate registrationsDelete duplicate rows from event_registrations, where a duplicate is the same event_code and attendee_email, keeping the row with the lowest id in each group. Then return id, event_code and attendee_email of the remaining rows, sorted by id.Pattern: DELETE … USING · keep the lowest idPro
- Orphaned invoicesReturn id, account_code and amount of every legacy_invoices row whose account_code matches no account_code in legacy_accounts (exact match). Invoices with no account_code at all are a different problem; leave them out. Sort by id.Pattern: NOT EXISTS · orphan rows · NOT IN and NULLPro
- Payments that break the rulesA payment is valid when its amount is greater than 0, paid_on is on or before 2024-08-31 (the export date), and payer_email is present and contains an @ with a dot somewhere after it (LIKE '%@%.%'). Return id, payer_email, amount and paid_on of every raw_payments row that breaks at least one rule, once per row. Sort by id.Pattern: Validation rules · OR · NULL-safe checksPro
- Rows that would block a constraintReturn id, account_code, credit_limit and problem for every legacy_accounts row that would block one of the two constraints: problem 'duplicate code' for each row whose account_code exactly equals another row's, and 'negative limit' for each row with credit_limit < 0 (a row breaking both would appear twice). Sort by id, then problem.Pattern: UNIQUE · CHECK · NULL semantics · UNIONPro
Changing data safely
- Parse a text-only importReturn id, vendor (with leading and trailing spaces trimmed), amount (amount_text as numeric, with $, commas and spaces removed; 'N/A', empty or spaces-only becomes NULL) and invoice_date (invoice_date_text as a date; empty or spaces-only becomes NULL) for every row of invoice_import_raw. Sort by id.Pattern: TRIM · NULLIF · regexp_replace · ::numeric · ::datePro
- Standardize country namesUPDATE crm_contacts so every country is trimmed and title-cased (INITCAP), leaving NULLs as NULL. Then return country and contacts (the number of contacts) for each country, sorted by country (NULL last).Pattern: UPDATE · INITCAP · TRIMPro
- Upsert a supplier price feedUpsert every price_feed row into product_catalog. Match on sku after trimming and upper-casing the feed's sku. New SKUs are inserted; existing ones get the feed's name and price, with updated_on set to feed_date. Products not in the feed stay as they are. Then return sku, name, price and updated_on from product_catalog, sorted by sku.Pattern: INSERT … ON CONFLICT DO UPDATE · EXCLUDEDPro
- Reconcile billing and CRMCompare billing_accounts with crm_accounts by account_id. Plans match if they're equal after trimming and lowercasing; mrr must be identical, and NULL on one side only is a mismatch. For every account that is missing from either side or doesn't match, return account_id, billing_plan, crm_plan, billing_mrr, crm_mrr (values as stored, NULL when the side is missing) and issue: 'missing_in_crm', 'missing_in_billing' or 'mismatch'. Sort by account_id.Pattern: FULL OUTER JOIN · IS DISTINCT FROMPro
Latest row per key and as-of joins
- Current plan per userReturn user_id, plan and changed_at of each user's most recent plan change, sorted by user_id.Pattern: DISTINCT ON · latest row per keyPro
- Each customer's first orderReturn customer_id, order_id, order_date, amount and status of each customer's earliest order (any status). Sort by customer_id.Pattern: DISTINCT ON · first row per groupPro
- Plan at time of purchaseFor every purchase event, return user_id, purchase_date (the event_date) and plan (the user's most recent plan change on or before that date; a change on the purchase day counts). Sort by user_id.Pattern: As-of join · JOIN LATERALPro
Sequences and hierarchies
- Org chart with recursive CTEReturn name, level (0 for employees with no manager, 1 for their direct reports, and so on) and chain (names from the top manager down to the employee, joined with ' > ', e.g. 'Omar > Tomás'). Sort by chain.Pattern: WITH RECURSIVE · hierarchiesPro
- Activity streaks as date rangesReturn user_id, start_date, end_date and days (number of distinct days in the streak) for every streak of at least 3 consecutive days in app_opens. Sort by user_id, start_date.Pattern: Gaps and islands · duplicate daysPro
- Sessions from raw pageviewsSplit pageviews into sessions per user: a gap of more than 30 minutes since the user's previous view starts a new session (exactly 30 minutes stays in the same session). Return user_id, session_start, session_end and pageviews (views in the session). Sort by user_id, session_start.Pattern: LAG · running SUM · sessionizationPro
- Daily active users, zero days includedReturn day (a DATE) and active_users (distinct users who logged in that day) for every day from 2024-08-01 to 2024-08-14, including days with 0. Oldest first.Pattern: generate_series · LEFT JOIN · filling date gapsPro
Interview tip: Before writing a DELETE or UPDATE, run the same WHERE as a SELECT and check the rows. Saying that out loud in an interview signals you've worked with production data.