Data Analyst Jobs

1. Ask if the array is sorted and whether indices or values are needed.
2. Brute force is O(n²) — say it, then improve.
3. Walk once with a hash set: for each x, check if (target − x) is already seen.
4. If seen, return the pair; else add x to the set.
5. O(n) time, O(n) space. If sorted, use two pointers for O(1) space.

Watch out: Forgetting duplicates or the x + x = target case.

At senior level: Discuss the space/time trade-off and which you'd pick given memory limits.

1. An index is a sorted structure (usually a B-tree) mapping values to rows.
2. It turns a full scan into a logarithmic lookup.
3. Cost: extra storage and slower writes, since every write updates indexes.
4. Composite index order matters — it serves prefixes left to right.
5. Verify with EXPLAIN rather than guessing.

Watch out: Saying 'add an index' to every slow query without reading the plan.

At senior level: Discuss covering indexes, cardinality, partial indexes and index bloat.

1. Atomicity: all of a transaction happens, or none of it.
2. Consistency: it moves the DB from one valid state to another.
3. Isolation: concurrent transactions don't see each other's partial work.
4. Durability: once committed, it survives a crash.
5. Give the bank-transfer example for atomicity.

Watch out: Reciting the acronym with no example.

At senior level: Discuss isolation levels and the anomalies each one permits.

1. SQL: relational, strong schema, joins, transactions — great when data is related and correctness matters.
2. NoSQL: flexible schema, horizontal scale, shaped for a known access pattern.
3. Choose by access pattern and consistency needs, not popularity.
4. Many systems use both — relational core plus a document/cache store.
5. Modern SQL scales far further than people assume.

Watch out: Saying 'NoSQL scales, SQL doesn't'.

At senior level: Discuss modelling for access patterns and the migration cost of getting it wrong.

1. INNER: only rows matching in both tables.
2. LEFT: all rows from the left, NULLs where the right has no match.
3. RIGHT: the mirror image; FULL OUTER: everything from both sides.
4. CROSS: every combination — usually a mistake if unintended.
5. Use a LEFT JOIN with a NULL check to find rows missing from the other table.

Watch out: Filtering the right table in WHERE after a LEFT JOIN — it silently becomes an INNER JOIN.

At senior level: Discuss join order, row multiplication on one-to-many, and anti-joins.

1. State assumptions about the schema and what counts as revenue.
2. Aggregate first: SUM(revenue) GROUP BY month, product.
3. Wrap it in a CTE and add RANK() OVER (PARTITION BY month ORDER BY revenue DESC).
4. Filter the outer query to rank <= 3.
5. Mention RANK vs DENSE_RANK vs ROW_NUMBER for tie handling.

Watch out: Trying to filter a window function in WHERE — it must go in an outer query or QUALIFY.

At senior level: Discuss index/partition strategy and cost on large fact tables.