SQL Interview Questions for Data Analysts

Beginner SQL questions with answer patterns

Beginner SQL questions test whether you can retrieve the right rows, select the right columns, filter accurately, sort results, and explain the grain of the output before calculating anything. Interviewers often care as much about your explanation as the final syntax.

When answering, say what the table represents, what one row means, what filter you are applying, and how you would verify the row count. This makes a simple SELECT answer sound professional.

• Question: Return all completed orders from 2026. Answer pattern: filter status first, use a date range instead of only year extraction when possible, and explain whether order_date includes time.
• Question: Count customers by country. Answer pattern: group by country, count distinct customer IDs if one customer can appear multiple times, and mention null/blank country handling.
• Question: Find the top 10 products by revenue. Answer pattern: calculate revenue as quantity times price, group by product, order descending, and clarify whether discounts or returns should be included.
• Question: Show duplicate email addresses. Answer pattern: group by normalized email, use HAVING COUNT(*) > 1, and explain whether case/space cleaning is needed.

Intermediate SQL questions with business context

Intermediate prompts combine joins, grouped metrics, date filters, CASE expressions, subqueries, and CTEs. Interviewers are usually checking your reasoning more than your ability to memorize syntax.

A good intermediate answer separates the logic into steps. Use one CTE for clean source rows, one for the metric grain, and one final SELECT for presentation.

• Revenue by month with completed transaction status and return exclusions.
• Top customers by trailing 90-day spend, including ties and minimum order-count threshold.
• Orders that are missing a customer record after a LEFT JOIN to the customer table.
• Products with declining month-over-month sales using LAG and percent change.
• Users who signed up but did not activate within seven days.
• Customers whose first purchase month differs from their signup month.

Joins and relationship questions

Before joining tables, identify the key, the expected relationship, and whether each key is unique. This prevents inflated totals from many-to-many joins.

In an interview, say the expected table grain before writing the join. For example: one row per order in orders, multiple rows per order in order_items, one row per product in products.

• INNER JOIN returns matching records and can drop unmatched rows.
• LEFT JOIN keeps every record from the left table and helps find missing matches.
• Use row-count checks before and after joins.
• Avoid SELECT * in final interview answers; choose columns deliberately.
• If a join increases order-level rows, explain whether that is expected because of item-level detail.

GROUP BY, HAVING, and metric definitions

GROUP BY changes the output grain. WHERE filters rows before aggregation; HAVING filters grouped results after aggregation. A common interview mistake is calculating a metric before defining the eligible rows.

Always define the metric in words. Revenue might exclude cancelled orders, include discounts, exclude tax, include shipping, or subtract returns depending on the business rule.

• Use WHERE for order_status = completed before summing revenue.
• Use HAVING SUM(revenue) > 10000 to keep only high-value groups.
• Use COUNT(DISTINCT customer_id) when counting unique customers from event or order tables.
• Name the business definition of each metric before presenting the query.
• Add a validation total that compares grouped revenue to source-level revenue.

Window functions

Window functions are common in analyst interviews because they support ranking, deduplication, running totals, and previous-period comparisons without collapsing every row.

Explain the PARTITION BY and ORDER BY parts in plain English. PARTITION BY defines the group being analyzed; ORDER BY defines the sequence inside that group.

• ROW_NUMBER for latest record per customer.
• RANK or DENSE_RANK for top products by month.
• LAG for month-over-month comparison.
• SUM OVER for running totals.
• AVG OVER for customer average spend compared with each order.
• MIN(order_date) OVER (PARTITION BY customer_id) for first purchase logic.

Sample SQL interview case study

Use this sample case to practice explaining your thinking. The fictional company wants to understand whether repeat purchasers generate more revenue than one-time purchasers. You have customers, orders, and order_items tables.

A strong answer would clarify order status, date range, returns, discounts, and whether repeat purchase means two completed orders or two order dates. Then it would build customer-level purchase counts before comparing revenue groups.

• Step 1: Filter to completed orders in the analysis period.
• Step 2: Join order_items to calculate order revenue, checking that each order_id exists in orders.
• Step 3: Aggregate to customer level: order_count, total_revenue, first_order_date, last_order_date.
• Step 4: Classify customers with CASE WHEN order_count >= 2 THEN repeat ELSE one_time.
• Step 5: Compare customer count, total revenue, average revenue per customer, and average order value by customer type.
• Validation: compare total revenue from the final grouped result to total completed-order revenue before classification.

Practice exercises

Practice writing SQL queries for realistic business questions. Each exercise should include a final query and a validation query so you can explain both the result and why you trust it.

• Monthly active users: count distinct users by calendar month and validate total event rows.
• Repeat purchase rate: identify customers with two or more completed orders and compare against all purchasers.
• Revenue by segment: join customers to orders and check that order totals do not change after the join.
• Duplicate detection: group by business keys and return rows where COUNT(*) is greater than one.
• Missing values audit: count NULL or blank values by important fields before analysis.
• Cohort retention: group customers by first purchase month and calculate returning purchasers by later months.

How to answer SQL interview prompts

A strong answer starts with assumptions, defines the metric, writes the query in steps, and describes validation checks. If the problem is ambiguous, ask a clarifying question before writing code.

Close your answer by explaining what could make the result misleading: duplicate joins, missing rows, timezone issues, cancelled orders, test data, or unclear metric ownership.

• Clarify table grain and date range.
• Write a small CTE for each logical step.
• Check duplicates and NULLs.
• Explain limitations of the result.
• Offer one follow-up analysis after answering the core prompt.

References for SQL interview preparation

External references for learning only.

Use official SQL documentation to strengthen fundamentals and public/sample datasets for practice. DataCareerHub is not affiliated with these sources.

Practice Exercise Cards