Ten SQL Interview Questions Solved Query by Query for Analyst Placements

A product manager sees a sudden revenue dip on Monday morning, but the dashboard says orders are up. The answer is not in a fancy model - it is usually in one SQL query that separates cancelled orders, duplicate joins, and real paid revenue.

Big Picture: SQL Is a Business Question Translated Into Rows

Good SQL is not written left to right. It is reasoned from the business question backward: what is the metric, what is the grain, which tables carry the required facts, and how will you prove the result is not inflated?

Core Explanation: The Schema We Will Use

Most analyst SQL interviews use a compact e-commerce or marketplace schema. You can adapt these patterns to banking, HR, marketing, product analytics, or operations because the logic is the same.

Dialect note: the queries below use widely readable SQL with PostgreSQL-style date functions such as `DATE_TRUNC`. In MySQL, you may replace them with `DATE_FORMAT`; in BigQuery, use `DATE_TRUNC(date, MONTH)`.

The Ten SQL Interview Questions, Solved Query by Query

1. Find monthly paid revenue

What it tests: joins, filtering, date grouping, and correct revenue grain. Revenue lives at item level, while paid status lives at payment or order level.

SELECT
  DATE_TRUNC('month', o.order_date) AS month,
  SUM(oi.quantity * oi.unit_price) AS paid_revenue
FROM orders o
JOIN order_items oi
  ON o.order_id = oi.order_id
JOIN payments p
  ON o.order_id = p.order_id
WHERE o.status = 'delivered'
  AND p.payment_status = 'paid'
GROUP BY DATE_TRUNC('month', o.order_date)
ORDER BY month;

Say this: “I am using `order_items` because revenue is calculated per item, then aggregating to month after filtering only paid delivered orders.”

2. Find customers who have never placed an order

What it tests: `LEFT JOIN` and null filtering. This is a classic acquisition-to-activation question.

SELECT
  c.customer_id,
  c.email,
  c.city
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Why `LEFT JOIN`: it keeps all customers, then identifies those with no matching order.

3. Calculate average order value by city

What it tests: avoiding double counting after joining `orders` to `order_items`.

WITH order_revenue AS (
  SELECT
    o.order_id,
    o.customer_id,
    SUM(oi.quantity * oi.unit_price) AS order_value
  FROM orders o
  JOIN order_items oi
    ON o.order_id = oi.order_id
  WHERE o.status = 'delivered'
  GROUP BY o.order_id, o.customer_id
)
SELECT
  c.city,
  AVG(orv.order_value) AS avg_order_value
FROM order_revenue orv
JOIN customers c
  ON orv.customer_id = c.customer_id
GROUP BY c.city
ORDER BY avg_order_value DESC;

Key idea: first create one row per order, then average those order values by city.

4. Get the top 3 products by revenue in each category

What it tests: window functions and ranking within groups.

WITH product_revenue AS (
  SELECT
    p.category,
    p.product_name,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM order_items oi
  JOIN products p
    ON oi.product_id = p.product_id
  GROUP BY p.category, p.product_name
),
ranked AS (
  SELECT
    category,
    product_name,
    revenue,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY revenue DESC
    ) AS rn
  FROM product_revenue
)
SELECT
  category,
  product_name,
  revenue
FROM ranked
WHERE rn <= 3
ORDER BY category, revenue DESC;

Interview upgrade: mention that `RANK()` would keep ties, while `ROW_NUMBER()` forces exactly three rows per category.

5. Find duplicate customer emails

What it tests: grouping and `HAVING`. This often appears in data quality or CRM analytics rounds.

SELECT
  email,
  COUNT(*) AS customer_records
FROM customers
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY customer_records DESC;

Say this: “I filter null emails first because multiple nulls do not necessarily mean duplicate customers.”

6. Find the second highest salary

What it tests: ranking and tie handling. Assume an `employees(employee_id, employee_name, salary)` table.

WITH salary_rank AS (
  SELECT
    employee_id,
    employee_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employees
)
SELECT
  employee_id,
  employee_name,
  salary
FROM salary_rank
WHERE salary_rank = 2;

Why `DENSE_RANK`: if two people share the highest salary, the next distinct salary is still ranked second.

7. Calculate daily cumulative revenue

What it tests: CTEs plus `SUM() OVER` for running totals.

WITH daily_revenue AS (
  SELECT
    DATE(o.order_date) AS order_day,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi
    ON o.order_id = oi.order_id
  WHERE o.status = 'delivered'
  GROUP BY DATE(o.order_date)
)
SELECT
  order_day,
  revenue,
  SUM(revenue) OVER (
    ORDER BY order_day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS cumulative_revenue
FROM daily_revenue
ORDER BY order_day;

Interview upgrade: explain that you first aggregate to day, then calculate the running total across days.

8. Build a simple purchase funnel from events

What it tests: conditional aggregation. This is common in product analytics and marketing analytics roles.

SELECT
  COUNT(DISTINCT CASE WHEN event_name = 'app_open' THEN user_id END) AS app_open_users,
  COUNT(DISTINCT CASE WHEN event_name = 'product_view' THEN user_id END) AS product_view_users,
  COUNT(DISTINCT CASE WHEN event_name = 'add_to_cart' THEN user_id END) AS cart_users,
  COUNT(DISTINCT CASE WHEN event_name = 'purchase' THEN user_id END) AS purchase_users
FROM events
WHERE event_time >= DATE '2026-01-01'
  AND event_time < DATE '2026-02-01';

Important caveat: this counts users who performed each event, not necessarily in strict sequence. For strict funnel order, use timestamps and compare first event times per user.

9. Find customers who made a second purchase within 30 days

What it tests: retention logic, `ROW_NUMBER`, and date difference thinking.

WITH customer_orders AS (
  SELECT
    customer_id,
    order_id,
    order_date,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id
      ORDER BY order_date
    ) AS order_no
  FROM orders
  WHERE status = 'delivered'
),
first_second AS (
  SELECT
    customer_id,
    MIN(CASE WHEN order_no = 1 THEN order_date END) AS first_order_date,
    MIN(CASE WHEN order_no = 2 THEN order_date END) AS second_order_date
  FROM customer_orders
  WHERE order_no IN (1, 2)
  GROUP BY customer_id
)
SELECT
  customer_id,
  first_order_date,
  second_order_date
FROM first_second
WHERE second_order_date <= first_order_date + INTERVAL '30 days';

Business interpretation: these are early repeat customers, useful for retention campaigns and cohort quality analysis.

10. Calculate cancellation rate by city

What it tests: business metric construction and safe division.

SELECT
  c.city,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled_orders,
  1.0 * SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END)
      / NULLIF(COUNT(*), 0) AS cancellation_rate
FROM orders o
JOIN customers c
  ON o.customer_id = c.customer_id
GROUP BY c.city
ORDER BY cancellation_rate DESC;

Why `NULLIF`: it prevents division by zero. In this query every grouped city has orders, but using `NULLIF` signals good metric hygiene.

SQL Execution Order: The Part That Makes Queries Click

SQL is typed in one order, but logically evaluated in another. This is why you cannot usually use a `SELECT` alias inside `WHERE`, and why `HAVING` filters groups after aggregation.

Query Sanity Checks Interviewers Love

If you show how you would validate a query, you sound like someone who can work with real messy business data. Use these checks after any revenue, funnel, retention, or cohort query.

Definitions You Should Say Cleanly

Case Study: Meesho - SQL Thinking in a Value Marketplace

Meesho operates in India’s value e-commerce market, where many users are price-sensitive, sellers are highly fragmented, and product discovery happens across a massive catalogue. The analytical challenge is not just “How many orders did we get?” It is: which cohorts repeat, which categories drive trust, which sellers create cancellations, and where logistics or returns damage contribution.

The strategic move was a data-led marketplace operating model: connect demand signals from buyers with seller-side assortment, pricing, serviceability, and fulfilment quality. The primary driver is the marketplace data loop - learning what users want and matching it with supply. Supporting drivers include an asset-light seller ecosystem, mobile-first user experience, logistics partnerships, seller enablement, and category-level experimentation.

Lesson: SQL is not merely a reporting skill. In a marketplace, it is the operating language for balancing buyer growth, seller quality, logistics reliability, and category economics.

How AI Changes SQL Interview Questions

AI does not remove SQL from analyst roles; it raises the bar. Recruiters increasingly expect you to use AI for speed while still understanding grain, joins, and validation.

1. Natural-language-to-SQL is becoming normal: tools can draft queries from prompts, but they often miss business definitions such as “paid revenue,” “active user,” or “eligible order.” Your advantage is knowing what to correct.
2. AI query debugging is now a workflow: ChatGPT, Claude, and database copilots can explain errors, rewrite nested queries as CTEs, and suggest window functions. Still verify the output against row counts and sample records.
3. Semantic layers matter more: companies are standardising metric definitions so AI assistants do not generate five versions of revenue. Analysts who can define metrics precisely become more valuable.

Interview Relevance

Common Mistake

What to Revise Next

Once these SQL patterns feel comfortable, move from data extraction to data interpretation. Revise Statistics & Probability Interview Questions With Answers next so you can explain significance, distributions, and uncertainty; then revise Machine Learning Interview Questions for Analyst Roles to connect SQL datasets with predictive modelling.