Data Analyst interview questions

A basic SELECT statement retrieves data from one or more tables.

• FROM chooses the source table, and WHERE filters individual rows.
• GROUP BY forms groups, and HAVING filters those groups after aggregation.
• ORDER BY sorts the result, and LIMIT caps the number of returned rows.
• The logical clause order helps explain query results and filter placement.

WHERE filters rows before grouping, while HAVING filters aggregated groups.

• WHERE country = 'DE' keeps only rows for Germany before aggregation.
• HAVING SUM(revenue) > 1000 keeps groups whose total exceeds 1000.
• Aggregate values do not yet exist when the WHERE stage runs.
• Put row conditions in WHERE and aggregate conditions in HAVING.

GROUP BY combines rows with shared values so aggregates can be calculated per group.

• Grouping orders by customer_id creates one result group per customer.
• SUM(amount) can then calculate each customer's total spend.
• Without GROUP BY, an aggregate is calculated over the full result set.
• Selected columns must be grouped or placed inside an aggregate function.

INNER JOIN keeps matching rows only, while LEFT JOIN keeps every row from the left table.

• INNER JOIN removes left-side rows that have no match on the right.
• LEFT JOIN fills right-side columns with NULL when no match exists.
• A customer without orders disappears from an INNER JOIN result.
• Choosing the wrong join can silently change counts and totals.

To find the top 10 customers, aggregate revenue by customer and rank it from highest to lowest.

• Select customer_id and SUM(amount) from the orders table.
• Group by customer_id so each customer has one revenue total.
• Sort the revenue alias descending and apply LIMIT 10.
• Clarify gross versus net-of-refunds revenue before finalizing the ranking.

A count of all rows includes every row, while COUNT(column) excludes NULL values in that column.

• The two counts are equal only when the chosen column has no NULLs.
• After a LEFT JOIN, a customer without orders still produces one joined row.
• COUNT(orders.id) correctly returns zero for that customer's orders.
• Counting every joined row instead would quietly inflate the result.

The main aggregate functions summarize many rows into useful statistics.

• COUNT measures rows or non-NULL values, and SUM adds numeric values.
• AVG calculates the arithmetic mean, while MIN and MAX return extremes.
• GROUP BY applies these summaries separately to categories such as countries.
• Every common aggregate except a count of all rows skips NULL values.

DISTINCT removes duplicate result rows and keeps each selected value combination once.

• It applies to the full combination of selected columns, not only the first.
• Selecting one column with DISTINCT returns its unique values.
• COUNT(DISTINCT customer_id) counts unique buyers rather than orders.
• Use it deliberately because it can hide duplication caused earlier in a query.

ORDER BY sorts by each listed column in sequence.

• The first column defines the main order, and later columns break ties.
• Sorting is ascending by default, while DESC reverses a chosen column.
• ORDER BY country, revenue DESC ranks revenue within each country.
• Without ORDER BY, a database does not guarantee row order.

NULL represents a missing or unknown SQL value, not zero or an empty string.

• Equality comparisons with NULL do not evaluate to true.
• Use IS NULL to find missing values and IS NOT NULL to exclude them.
• NULL values can change join matches and aggregate results.
• Check NULL rates in key columns before trusting an analysis.

Duplicate records can be found by grouping on the columns that should be unique.

• Group by a candidate key such as email and count its rows.
• Use HAVING with a count greater than one to keep duplicated values.
• The grouped result shows each duplicate key and its frequency.
• Join it back or use ROW_NUMBER to inspect the complete duplicate rows.

A subquery is a query nested inside another SQL query.

• It can appear in FROM, WHERE, or SELECT depending on the task.
• One use is comparing each customer's spend with an average calculated inside it.
• Subqueries work well for short, one-off pieces of logic.
• Replace deeply nested subqueries with CTEs when readability suffers.

A CTE is a named temporary result declared with WITH for use in the main query.

• It lets a complex query read as a sequence of clear steps.
• One CTE can clean data and a later CTE can aggregate it.
• The main query references each CTE much like a table.
• This top-to-bottom structure simplifies debugging and review.

UNION and UNION ALL both stack compatible query results vertically.

• Each query must return matching numbers and compatible types of columns.
• UNION removes duplicate rows from the combined result.
• UNION ALL preserves every row and avoids the deduplication cost.
• Prefer UNION ALL when overlap is impossible or duplicates are meaningful.

LIKE filters text by matching it against a pattern.

• A percent sign matches any sequence of characters.
• An underscore matches exactly one character.
• A pattern ending in @gmail.com can find Gmail addresses.
• For case-insensitive matching, use the database's ILIKE or normalized lowercase text.

CASE WHEN adds conditional logic to an SQL expression.

• It can label amounts as small, medium, or large.
• It supports conditional totals by returning an amount only for paid rows.
• An ELSE value handles rows that match no listed condition.
• CASE expressions can be used in SELECT, WHERE, GROUP BY, and ORDER BY.

A primary key identifies each row, while a foreign key links rows through a referenced key.

• A primary key must be unique and non-NULL for every row.
• A foreign key references a primary or UNIQUE key, often in another table.
• Foreign-key values can repeat and may be NULL unless additional constraints apply; orders.customer_id can reference customers.id.
• Analysts use these constraints to understand join paths, relationship cardinality, and table granularity.

Duplicate join keys can make a JOIN return more rows than either input table.

• SQL returns every matching pair rather than one row per original record.
• If a key appears twice on the left and three times on the right, the join returns six matching rows.
• Aggregating repeated amounts after such a join can inflate revenue or other totals.
• Check key uniqueness and expected relationship cardinality before joining, then compare row counts afterward.

Filter the date column against the current date minus a 30-day interval.

• PostgreSQL can use order_date >= CURRENT_DATE - INTERVAL '30 days'.
• MySQL and BigQuery use different date subtraction syntax.
• Confirm whether the field is a date or a timestamp before filtering.
• Check time-zone and boundary rules so the intended days are included.

IN tests membership in a list, while BETWEEN tests an inclusive range.

• IN is clearer than several OR conditions for values such as country codes.
• BETWEEN 10 AND 100 includes both 10 and 100.
• Inclusive endpoints are useful for simple numeric ranges.
• For timestamp ranges, prefer an explicit inclusive start and exclusive end.