Data Analyst Interview Guide: SQL, Excel, Power BI, Tableau & Business Case Questions

Joins, Aggregation and the WHERE vs HAVING Rule

INNER JOIN returns matching rows only; LEFT JOIN keeps all rows from the left table and fills NULLs where no match exists. WHERE filters rows before aggregation while HAVING filters groups after GROUP BY. Remember that COUNT(*) counts rows while COUNT(column) ignores NULLs, and that NULL comparisons need IS NULL rather than equals.

SELECT c.city, COUNT(DISTINCT o.customer_id) AS buyers, SUM(o.amount) AS revenue
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.status = 'delivered'
GROUP BY c.city
HAVING SUM(o.amount) > 100000
ORDER BY revenue DESC;

Window Functions: Ranking, Running Totals and Retention

Window functions compute across related rows without collapsing them. ROW_NUMBER, RANK and DENSE_RANK order rows within a partition; LAG and LEAD compare to previous or next rows; SUM() OVER gives running totals. A classic question is "top 3 products per category" or "second highest salary per department".

SELECT * FROM (
  SELECT category, product, revenue,
         DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
  FROM product_sales
) t
WHERE rnk <= 3;

A/B Testing Essentials

Define one primary metric and a minimum detectable effect, compute the required sample size before launching, randomize at the right unit (user rather than session), and run for full weekly cycles to avoid day-of-week bias. A p-value below your threshold means the observed difference would be unlikely under no true effect; it does not tell you the size of the effect, so report confidence intervals and practical significance. Avoid peeking and stopping early, and check for sample ratio mismatch.

• Correlation is not causation: use experiments or careful quasi-experimental methods before claiming impact
• Mean can mislead with skewed data; report median and percentiles for order values and latencies

Presenting an Insight in 60 Seconds

Use the headline-evidence-action pattern: state the conclusion first ("Checkout conversion fell 2.1 points because Android payment failures doubled"), show one supporting chart, then give the recommended action with expected impact and owner. Executives remember a decision, not a query.