Top Data Analyst Interview Questions & Answers

Quick comparison: • INNER JOIN — Returns only rows with matching values in both tables • LEFT JOIN — Returns all rows from the left table + matched rows from right (NULLs where no match) • FULL OUTER JOIN — Returns all rows from both tables (NULLs on either side where no match) In practice: LEFT JOIN is the most common for analysis because you usually want to keep all records from your primary table and enrich with related data. Example use case: SELECT u.name, COUNT(o.id) as order_count FROM users u LEFT JOIN orders o ON u.id = o.user_id GROUP BY u.name This shows all users including those who haven't ordered (order_count = 0).

Approach 1 — Subquery: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) Approach 2 — Window function (more versatile): SELECT salary FROM ( SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) as rnk FROM employees ) ranked WHERE rnk = 2 Why DENSE_RANK over ROW_NUMBER? • DENSE_RANK handles ties correctly — if two people share the top salary, it still identifies the next distinct value as rank 2 • ROW_NUMBER assigns arbitrary ordering to ties • The window function approach generalizes to finding the Nth-highest salary by changing the rank filter

Key distinction: • Correlation = two variables move together • Causation = one variable directly influences the other Classic example: Ice cream sales and drowning incidents are positively correlated — but ice cream doesn't cause drowning. The confounding variable is temperature (hot weather increases both). How to establish causation: 1. Run a controlled experiment (A/B test) 2. Use a natural experiment that isolates the effect 3. Apply regression with controls for confounders Practical rule: Always ask "Is there a confounding variable?" before claiming causality. This is critical when presenting findings to stakeholders who might act on correlations as if they were causal.

Technical definition: The probability of observing your data (or something more extreme) if the null hypothesis were true. For a stakeholder: "If our change had zero real effect, there's only a 3% chance we'd see results this strong by random luck — so we're confident the effect is real." Key nuances: • Common threshold: p < 0.05 (5%) • A low p-value doesn't tell you the effect SIZE (use confidence intervals for that) • Running multiple tests inflates false positives — apply Bonferroni correction or control FDR • Statistical significance ≠ practical significance — a 0.01% conversion lift can be statistically significant but not worth acting on