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

Reading isn't practice. Get AI feedback on your answers.

Other Companies

Wolters Kluwer Data Engineer Interview Questions

Reading isn't practice. Get AI feedback on your answers.

Other Companies