SQL + Data/ETL

Understand three-valued logic and NULL gotchas that break tests.

Asked 'Why does WHERE col = NULL return no rows?' / 'why did my NOT IN query break?'

named in the job descriptions

Write a filtered read against a table live, using the full operator set.

Asked 'Fetch all orders placed in the last 30 days over 500 that are not cancelled' — write it live.

named in the job descriptions

Sort, cap, and dedupe result sets live.

Asked 'Return the 10 most recent signups, newest first' / 'list the unique payment methods used'.

named in the job descriptions

Combine two related tables on a key and return only matched rows.

Asked 'Get every order with its customer name' — the single most common SQL topic.

named in the job descriptions

Use an outer join anti-pattern to surface unmatched/orphan rows — the classic integrity check.

Asked 'Find orders that have no matching customer' / 'find customers who never placed an order'.

named in the job descriptions

Aggregate rows into per-group summaries live.

Asked 'Average salary per department' / 'order count per customer'.

named in the job descriptions

Filter aggregated groups (HAVING) vs individual rows (WHERE) and know the order.

Asked 'What's the difference between WHERE and HAVING?' + 'keep only groups with count > 1'.

named in the job descriptions

Nest a query inside another as a value, a set filter, or an existence test.

Asked 'Employees earning above the company average' / 'customers who have at least one order'.

named in the job descriptions

Detect duplicate keys/rows — a canonical data-quality query.

Asked 'Find duplicate emails / duplicate records in a table.'

named in the job descriptions

Return the Nth-ranked value via subquery, window, and LIMIT/OFFSET; handle ties.

Asked 'Find the 2nd highest salary — now give me two more approaches, and handle duplicate salaries.'

named in the job descriptions

Join a table to itself to relate rows within one table.

Asked 'List each employee with their manager's name from one employees table.'

asked in interviews, not named in any job description

Return matched rows plus unmatched from both sides — the diffing join.

Asked 'Compare two tables and show rows missing on either side' (source-vs-target diff).

named in the job descriptions

A subquery that references the outer row, re-evaluated per row.

Asked 'Employees earning above their own department's average salary.'

named in the job descriptions

Know the constraint types because DB testing = verifying they hold.

Asked 'Difference between primary key and unique key?' / 'how would you test that a foreign key is enforced?'

named in the job descriptions

Classify statements and know the delete-family differences — a common concept check.

Asked 'TRUNCATE vs DELETE vs DROP?' / 'is TRUNCATE DDL or DML?'

named in the job descriptions

Seed test data by literal rows or by copying from a query.

Asked 'How do you set up the data your automated test needs?' / practical seeding task.

named in the job descriptions

Mutate specific rows to reach a test state — always scoped by WHERE.

Asked 'Move a test order into 'refunded' state so you can test the refund flow.'

named in the job descriptions

Remove specific rows for teardown/clean state, scoped by WHERE.

Asked 'How do you clean up data your test created?'

named in the job descriptions

Group statements atomically so tests can roll back and never corrupt shared DBs.

Asked 'How do you keep your DB tests from polluting the shared test database?'

named in the job descriptions

Mutate rows selected by another table/query — incl. de-dup delete.

Asked 'Delete duplicate rows but keep one' — a very common follow-up to the find-duplicates question.

named in the job descriptions

Read what a proc/view does and invoke it; NOT author production procs.

Asked 'How would you test a stored procedure?' / 'have you validated data through stored procedures?'

named in the job descriptions

Rank/number rows within partitions without collapsing them — the intermediate ceiling.

Asked 'Top-paid employee per department' / 'rank vs dense_rank difference' / 'top-N per group with a join'.

named in the job descriptions

Name intermediate result sets for readable multi-step queries.

Asked 'Rewrite this nested query using a CTE' / any multi-step query where clean structure is judged.

named in the job descriptions

Inline conditional logic; pivot counts via SUM(CASE WHEN ...).

Asked 'Count paid vs failed orders in a single query' / bucket values into categories.

named in the job descriptions

Combine/diff two result sets — the direct tool for source-vs-target reconciliation.

Asked 'UNION vs UNION ALL?' / 'find rows present in source but missing in target.'

named in the job descriptions

Know what an index is and why a query is slow — talk-level only, no tuning.

Asked 'Why might this query be slow?' — a light conceptual probe.

asked in interviews, not named in any job description

Access a previous/next row's value for row-to-row comparisons.

Asked 'Compare each day's value to the previous day' / 'find gaps in a sequence.'

asked in interviews, not named in any job description

After an API/UI action, query the DB to assert the backend persisted correctly — the core SDET SQL use.

Asked 'Your API test creates an order — how do you confirm it in the database?'

named in the job descriptions

Recognize document vs relational stores and do basic MongoDB find queries; know MySQL dialect quirks.

Asked 'We use MongoDB — how would you validate a document was written?' / dialect follow-ups.

named in the job descriptions

Verify one tenant's queries cannot read another tenant's rows at the DB layer.

Asked 'How would you test that tenant data is isolated at the database layer / that RLS policies work?'

named in the job descriptions

Name-level awareness of warehouse dialects and data-QA tooling so you don't freeze when a JD names them.

Asked 'Have you worked with BigQuery / Airflow / dbt / Great Expectations?' — recognition + honest framing.

named in the job descriptions

Trace a data element across source -> pipeline -> target -> report and assert consistency at each hop.

Asked 'A number in the dashboard is wrong — how do you trace where the data broke?'

named in the job descriptions

Verify column names, types, lengths, and constraints match the mapping/contract.

Asked 'How do you validate the target table's schema matches the contract?'

named in the job descriptions

Narrate the standard end-to-end ETL testing process on demand.

Asked 'Walk me through your ETL testing process end to end.'

named in the job descriptions

Compare values field-by-field between source and target for the same key.

Asked 'Counts match but is the data correct? How do you compare field values source vs target?'

named in the job descriptions

Verify business/transformation rules were applied correctly during load.

Asked 'How do you test that the transformation logic in the pipeline is correct?'

named in the job descriptions

Assert the data-quality dimensions with SQL and (for data shops) automated monitoring.

Asked 'What data-quality dimensions do you check and how?' / 'how do you detect a stale or anomalous data load?'

named in the job descriptions

Confirm row counts match between source and target after a load; report the delta.

Asked 'How do you validate that an ETL load moved all the records?'

named in the job descriptions

A Pytest suite using sqlalchemy/psycopg2 that sets up data, acts, asserts DB state, and reconciles source-vs-target.

Asked 'Show/describe how you'd automate database and data-pipeline validation in a framework.'

named in the job descriptions