SQL / Data Analyst Interview Questions

INNER JOIN returns only rows with matches in both tables. LEFT JOIN returns all rows from the left table with NULLs where there's no match on the right. RIGHT JOIN is the mirror. FULL OUTER JOIN returns all rows from both tables, with NULLs on either side where there's no match. In practice LEFT JOIN covers most use cases — RIGHT JOIN can always be rewritten as a LEFT JOIN by swapping table order.

A window function performs a calculation over a set of rows related to the current row, without collapsing them like GROUP BY does. Example: ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) assigns a rank within each department. Other common ones: RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER(), AVG() OVER(). Use them to compute running totals, rankings, or compare a row to its neighbours.

A Common Table Expression (WITH clause) names a temporary result set you can reference in the main query. Use CTEs for readability when the logic is complex or reused multiple times in the same query. They also allow recursion (WITH RECURSIVE) for hierarchical data. Subqueries are fine for simple, one-off filters — CTEs are better when the subquery is long or repeated.

WHERE filters rows before aggregation; HAVING filters groups after GROUP BY. You can't use aggregate functions (SUM, COUNT) in WHERE — that's what HAVING is for. Example: WHERE salary > 50000 filters individual rows; HAVING COUNT(*) > 5 filters groups that have more than 5 members.

An index is a data structure (usually a B-tree) that lets the database find rows without a full table scan. Indexes speed up reads on the indexed column(s) but slow down writes (INSERT/UPDATE/DELETE) because the index must be kept in sync. Add indexes on columns used in WHERE, JOIN, and ORDER BY clauses. Avoid over-indexing — profile with EXPLAIN/EXPLAIN ANALYZE first.

COUNT(*) counts all rows including NULLs. COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT column) counts unique non-NULL values. Use COUNT(*) to count rows, COUNT(column) to count populated values, and COUNT(DISTINCT column) to find cardinality.

NULL means unknown — comparisons with NULL always return NULL, not TRUE or FALSE, so use IS NULL / IS NOT NULL, not = NULL. Use COALESCE(col, default) to substitute a fallback value, NULLIF(a, b) to return NULL when two values are equal. NULLs are excluded from aggregate functions except COUNT(*). Document your NULL semantics in data models so analysts don't get surprised.

UNION combines result sets from two queries and removes duplicate rows (expensive — requires a sort/hash). UNION ALL combines them and keeps all rows including duplicates (faster). Use UNION ALL by default unless you specifically need deduplication, since it avoids the extra sort step.

Read data with pd.read_csv() / pd.read_sql(). Clean with df.dropna(), df.fillna(), df.astype(), and df.str.strip(). Filter with boolean indexing: df[df['col'] > 0]. Aggregate with df.groupby('category')['value'].agg(['sum','mean','count']). Merge DataFrames with pd.merge(df1, df2, on='key', how='left'). Reshape with pivot_table() or melt().

Define a primary metric tied to the product goal (e.g. conversion rate, retention, revenue per user) and guardrail metrics to ensure you don't harm other things (e.g. load time, support tickets). Use a funnel to find where users drop off. Segment by user cohort, platform, and region. Run an A/B test if traffic allows — compare treatment vs. control using a t-test or chi-square test and check for statistical significance before concluding.

Normalisation removes data redundancy by splitting data into related tables (1NF → 2NF → 3NF), reducing update anomalies and storage. Denormalisation intentionally adds redundancy — e.g. pre-joining tables into a wide fact table — to speed up read-heavy analytical queries. Operational OLTP databases are usually normalised; analytical OLAP/data-warehouse schemas (star schema, snowflake) are denormalised for query performance.