Question 1

Implement a CASE WHEN condition - medium difficulty

Accepted Answer

CASE WHEN implements conditional logic. Medium example—categorize sales: SELECT customer_id, amount, CASE WHEN amount >= 1000 THEN 'High' WHEN amount >= 100 THEN 'Medium' WHEN amount > 0 THEN 'Low' ELSE 'Zero' END AS tier FROM orders. Nested CASE: CASE WHEN region = 'US' THEN CASE WHEN state IN ('CA','NY') THEN 'Tier1' ELSE 'Tier2' END ELSE 'International' END. Use for bucketing, NULL handling, and derived columns....

Question 2

Predict the output of SQL joins (INNER, LEFT, RIGHT) on dataset containing 0, NULL, and 1

Accepted Answer

NULL in joins: NULL = NULL is UNKNOWN, so INNER JOIN excludes null keys. LEFT JOIN: left rows with null key get null for right side. With 0, NULL, 1: INNER on 0 and 1 only; NULL never matches. LEFT: all left rows; right null for no match. Be explicit with COALESCE in join keys if nulls should match. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead. **Scalability trade-offs**: Profile before optimizing; validate on sample then full....

Question 3

SQL Cross Join - difficult question

Accepted Answer

CROSS JOIN: cartesian product, every row of A with every row of B. No ON clause. Use for: combinations (all pairs), expanding dimensions. Dangerous on large tables—N*M rows. Example: SELECT * FROM sizes CROSS JOIN colors. For limited cross: add WHERE to filter. Or use for generating sequences. **Why it matters**: Design choices compound at scale—wrong approach can cause 100× overhead. **Scalability trade-offs**: Profile before optimizing; validate on sample then full....

Question 4

Window Functions: ROW_NUMBER(), RANK(), PARTITION BY - produce output using dataset

Accepted Answer

Example: employees (id, name, dept, salary). **ROW_NUMBER**: SELECT *, ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM employees. **RANK**: Same but RANK()—ties get same rank, gap after. **DENSE_RANK**: Same rank, no gap. Output: dept, name, salary, rn, rank, dense_rank. **Why PARTITION BY**: Defines groups; ORDER BY defines order within group. Use ROW_NUMBER for unique ordering; RANK for Olympics-style; DENSE_RANK for top-N without gaps....

Wolters Kluwer Data Engineer Interview Questions

Other Companies