Blog›Data Analytics Series›Chapter 24
SERIES · CHAPTER 24Interview Prep

SQL Interview Questions for Data Analysts — 50 Practice Problems

20 fully solved problems (Easy → Hard) plus a catalogue of 30 more — JOINs, GROUP BY, window functions, CTEs, de-duplication, cohort retention, consecutive events, and pivot queries. Every solution includes what the interviewer is testing. Indian e-commerce context throughout.

5 Easy10 Medium5 Hard
DATA ANALYTICS SERIES:← Ch 23: BI StrategyCh 24: SQL Interview Prep ←Ch 25: Python Interview Prep →
Tables Used in All Questions
orders
order_id, customer_id, product_id, amount_inr, discount_inr, status, payment_method, city, state, order_date, updated_at
customers
customer_id, name, email, phone, city, registration_date
products
product_id, name, category, price_inr, brand
order_items
item_id, order_id, product_id, quantity, unit_price
Q1EasySELECT / WHEREFind all orders placed in Delhi with order value above ₹2,000 in September 2026.
SELECT order_id, customer_id, amount_inr, order_date
FROM orders
WHERE city = 'Delhi'
  AND amount_inr > 2000
  AND order_date BETWEEN '2026-09-01' AND '2026-09-30'
ORDER BY amount_inr DESC;
WHAT THE INTERVIEWER TESTS: Basic filter. Use BETWEEN for inclusive date range. Always ORDER BY to show your thought process.
Q2EasyGROUP BY / COUNTHow many orders were placed per city last month? Show cities with more than 500 orders.
SELECT city, COUNT(*) AS order_count
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY city
HAVING COUNT(*) > 500
ORDER BY order_count DESC;
WHAT THE INTERVIEWER TESTS: HAVING filters after aggregation (WHERE filters before). This distinction is a very common interview question.
Q3EasySUM / AVGWhat is the total revenue and average order value per product category?
SELECT
  category,
  COUNT(*)                           AS orders,
  SUM(amount_inr)                    AS total_revenue,
  ROUND(AVG(amount_inr), 0)          AS avg_order_value,
  ROUND(SUM(amount_inr) / 1e5, 2)   AS revenue_lakhs
FROM orders
GROUP BY category
ORDER BY total_revenue DESC;
WHAT THE INTERVIEWER TESTS: Use column aliases (AS) for readability. Divide by 1e5 to convert to lakhs — interviewers appreciate business-friendly output.
Q4EasyCASE WHENClassify each order as Small (<₹500), Medium (₹500–₹2000), or Large (>₹2000).
SELECT
  order_id,
  amount_inr,
  CASE
    WHEN amount_inr < 500  THEN 'Small'
    WHEN amount_inr <= 2000 THEN 'Medium'
    ELSE 'Large'
  END AS order_size
FROM orders;
WHAT THE INTERVIEWER TESTS: CASE WHEN evaluates conditions in order — first match wins. No need for BETWEEN since the earlier condition already filters the lower bound.
Q5EasyNULL handlingFind customers who have never placed a second order (only 1 order total). Handle NULLs in the discount column — treat them as 0.
SELECT customer_id, COUNT(*) AS order_count,
       SUM(COALESCE(discount_inr, 0)) AS total_discount
FROM orders
GROUP BY customer_id
HAVING COUNT(*) = 1;
WHAT THE INTERVIEWER TESTS: COALESCE returns the first non-NULL value. HAVING COUNT(*) = 1 finds single-order customers. Classic retention/churn setup question.
Q6MediumINNER JOINFind the names and email addresses of customers who placed orders in September 2026.
SELECT DISTINCT c.customer_id, c.name, c.email
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_date BETWEEN '2026-09-01' AND '2026-09-30';
WHAT THE INTERVIEWER TESTS: DISTINCT prevents duplicate rows when a customer placed multiple September orders. INNER JOIN returns only matched rows in both tables.
Q7MediumLEFT JOIN / Anti-joinFind customers who registered but have NEVER placed any order.
SELECT c.customer_id, c.name, c.email, c.registration_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL
ORDER BY c.registration_date DESC;
WHAT THE INTERVIEWER TESTS: Anti-join pattern: LEFT JOIN + WHERE right side IS NULL. Returns all customers with no matching orders. Very common interview question.
Q8MediumSelf-JOINFind pairs of products that were ordered together in the same order.
SELECT
  a.product_id  AS product_1,
  b.product_id  AS product_2,
  COUNT(*)      AS times_co_purchased
FROM order_items a
JOIN order_items b
  ON a.order_id = b.order_id
 AND a.product_id < b.product_id   -- avoids (A,B) and (B,A) duplicates
GROUP BY a.product_id, b.product_id
HAVING COUNT(*) >= 10
ORDER BY times_co_purchased DESC;
WHAT THE INTERVIEWER TESTS: Self-join joins a table to itself. a.product_id < b.product_id ensures each pair appears once (not twice). Market basket analysis pattern.
Q9MediumSubqueryFind all orders whose value is above the average order value.
SELECT order_id, customer_id, amount_inr
FROM orders
WHERE amount_inr > (SELECT AVG(amount_inr) FROM orders)
ORDER BY amount_inr DESC;
WHAT THE INTERVIEWER TESTS: Scalar subquery in WHERE — returns one value used as a filter. The inner query runs once. Equivalent but less readable than a CTE for simple cases.
Q10MediumSubquery / EXISTSFind categories where at least one product has been returned in the last 30 days.
SELECT DISTINCT category
FROM products p
WHERE EXISTS (
  SELECT 1
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  WHERE oi.product_id = p.product_id
    AND o.status = 'returned'
    AND o.order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
);
WHAT THE INTERVIEWER TESTS: EXISTS is more efficient than IN for large datasets — stops scanning as soon as one match is found. SELECT 1 is a convention; the value does not matter.
Q11MediumCTEFind the top 3 cities by total GMV in each state.
WITH city_revenue AS (
  SELECT state, city, SUM(amount_inr) AS gmv
  FROM orders
  GROUP BY state, city
),
ranked AS (
  SELECT *,
    DENSE_RANK() OVER (PARTITION BY state ORDER BY gmv DESC) AS rank_in_state
  FROM city_revenue
)
SELECT state, city, gmv, rank_in_state
FROM ranked
WHERE rank_in_state <= 3
ORDER BY state, rank_in_state;
WHAT THE INTERVIEWER TESTS: Classic CTE + window function pattern. Use DENSE_RANK so tied 3rd cities both appear. Interviewers test both CTEs and window functions in one problem.
Q12MediumDate functionsCalculate the number of days between each customer's first and most recent order.
SELECT
  customer_id,
  MIN(order_date)                                   AS first_order,
  MAX(order_date)                                   AS last_order,
  DATEDIFF(MAX(order_date), MIN(order_date))        AS days_active,
  COUNT(*)                                          AS total_orders
FROM orders
GROUP BY customer_id
ORDER BY days_active DESC;
WHAT THE INTERVIEWER TESTS: DATEDIFF(end, start) returns days between dates. Combine with MIN/MAX in GROUP BY for customer-level date spans. Signals understanding of temporal data.
Q13MediumHAVINGFind customers who placed more than 3 orders in a single month.
SELECT
  customer_id,
  DATE_FORMAT(order_date, '%Y-%m')  AS order_month,
  COUNT(*)                           AS monthly_orders
FROM orders
GROUP BY customer_id, DATE_FORMAT(order_date, '%Y-%m')
HAVING COUNT(*) > 3
ORDER BY monthly_orders DESC;
WHAT THE INTERVIEWER TESTS: GROUP BY both customer_id AND month to get per-customer-per-month counts. This is a common loyalty/power-user identification pattern.
Q14MediumRunning totalShow daily orders and the running total of orders for the year-to-date.
WITH daily AS (
  SELECT DATE(order_date) AS day, COUNT(*) AS orders
  FROM orders
  WHERE YEAR(order_date) = 2026
  GROUP BY DATE(order_date)
)
SELECT
  day,
  orders,
  SUM(orders) OVER (ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS ytd_orders
FROM daily
ORDER BY day;
WHAT THE INTERVIEWER TESTS: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is the running total window frame. Every window function question tests OVER() clause understanding.
Q15MediumLAGCalculate month-over-month revenue growth for each month in 2026.
WITH monthly AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS month,
         SUM(amount_inr)                  AS revenue
  FROM orders WHERE YEAR(order_date) = 2026
  GROUP BY month
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month)       AS prev_month,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month))
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100
  , 1)                                     AS mom_growth_pct
FROM monthly
ORDER BY month;
WHAT THE INTERVIEWER TESTS: LAG(col, 1) accesses the previous row. NULLIF prevents division-by-zero for the first month (prev is NULL). MoM growth is tested in almost every analyst interview.
Q16HardROW_NUMBER de-dupThe orders table has duplicates. Keep only the most recent record per order_id.
WITH deduped AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY updated_at DESC
    ) AS rn
  FROM orders
)
SELECT * FROM deduped WHERE rn = 1;
WHAT THE INTERVIEWER TESTS: ROW_NUMBER with PARTITION BY order_id assigns 1 to the latest row per order. The outer WHERE rn = 1 keeps only that row. The gold standard de-duplication pattern.
Q17HardCohort retentionWhat percentage of customers who placed their first order in each month returned the next month?
WITH first_orders AS (
  SELECT customer_id,
         DATE_FORMAT(MIN(order_date), '%Y-%m') AS cohort_month
  FROM orders GROUP BY customer_id
),
retained AS (
  SELECT fo.cohort_month,
         COUNT(DISTINCT fo.customer_id)  AS cohort_size,
         COUNT(DISTINCT o2.customer_id)  AS returned_next_month
  FROM first_orders fo
  LEFT JOIN orders o2
    ON fo.customer_id = o2.customer_id
   AND DATE_FORMAT(o2.order_date, '%Y-%m')
       = DATE_FORMAT(
           DATE_ADD(STR_TO_DATE(CONCAT(fo.cohort_month,'-01'),'%Y-%m-%d'),
           INTERVAL 1 MONTH), '%Y-%m')
  GROUP BY fo.cohort_month
)
SELECT cohort_month, cohort_size, returned_next_month,
  ROUND(returned_next_month / cohort_size * 100, 1) AS retention_pct
FROM retained ORDER BY cohort_month;
WHAT THE INTERVIEWER TESTS: Cohort retention is the hardest standard interview question. Break it into: (1) find first order month per customer; (2) left join orders 1 month later; (3) compute retention rate. Draw the logic before writing SQL.
Q18HardConsecutive daysFind customers who placed orders on 3 or more consecutive days.
WITH ordered AS (
  SELECT customer_id, DATE(order_date) AS order_day
  FROM orders
  GROUP BY customer_id, DATE(order_date)
),
lagged AS (
  SELECT customer_id, order_day,
    LAG(order_day, 1) OVER (PARTITION BY customer_id ORDER BY order_day) AS prev1,
    LAG(order_day, 2) OVER (PARTITION BY customer_id ORDER BY order_day) AS prev2
  FROM ordered
)
SELECT DISTINCT customer_id
FROM lagged
WHERE DATEDIFF(order_day, prev1) = 1
  AND DATEDIFF(prev1,    prev2)  = 1;
WHAT THE INTERVIEWER TESTS: LAG(col, 2) accesses two rows back. DATEDIFF = 1 checks exact consecutive days. This tests both window functions and date arithmetic simultaneously.
Q19HardNth highestFind the 3rd highest order value for each product category (without using LIMIT).
WITH ranked AS (
  SELECT category, amount_inr,
    DENSE_RANK() OVER (PARTITION BY category
                       ORDER BY amount_inr DESC) AS dr
  FROM orders
)
SELECT category, amount_inr AS third_highest
FROM ranked
WHERE dr = 3;
WHAT THE INTERVIEWER TESTS: DENSE_RANK does not skip — if two orders tie for 2nd, the next is still 3rd (not 4th). Use DENSE_RANK for "Nth highest" problems, not RANK or ROW_NUMBER.
Q20HardPivot / CASE aggregationShow total orders per payment method (UPI, COD, Card) as separate columns, one row per city.
SELECT
  city,
  SUM(CASE WHEN payment_method = 'UPI'  THEN 1 ELSE 0 END) AS upi_orders,
  SUM(CASE WHEN payment_method = 'COD'  THEN 1 ELSE 0 END) AS cod_orders,
  SUM(CASE WHEN payment_method = 'Card' THEN 1 ELSE 0 END) AS card_orders,
  COUNT(*)                                                  AS total_orders
FROM orders
GROUP BY city
ORDER BY total_orders DESC;
WHAT THE INTERVIEWER TESTS: Conditional aggregation (SUM + CASE WHEN) is the MySQL way to pivot. Each CASE WHEN creates a 0/1 flag; SUM counts the 1s. More flexible than PIVOT (not in MySQL).

30 More Questions to Practice (Titles + Topics)

#DifficultyTopicQuestion
21EasyDISTINCTCount the number of unique cities that placed orders this month.
22EasyORDER BY + LIMITShow the 5 most expensive products in the Electronics category.
23EasyBETWEENFind orders placed between Diwali (1 Nov) and 15 Nov 2026.
24EasyLIKEFind customers whose email ends with @gmail.com.
25EasyINFind orders with status either "returned" or "cancelled".
26Medium3-table JOINShow each order with the customer name and product category.
27MediumSubquery in FROMFind the city with the highest average order value.
28MediumCASE + GROUP BYHow many orders in each payment method had a discount applied?
29MediumDate truncTotal weekly revenue — show the Monday start date of each week.
30MediumCOALESCE + JOINList all products and their total sales (show 0 for products never ordered).
31MediumRANKRank products by return count within each category.
32MediumHAVING + COUNT DISTINCTFind states where more than 50 distinct customers ordered last month.
33MediumPercentage of totalShow each category's share of total GMV as a percentage.
34MediumRolling 7-dayCalculate the 7-day rolling average of daily new customer registrations.
35MediumLEADFor each order, show the next order date from the same customer.
36MediumCTE chainFind the top customer by GMV in each city (break ties by most recent order).
37MediumEXISTS vs INFind products that were in orders that were later returned.
38MediumAnti-joinFind products that have never been returned.
39MediumDe-duplicateRemove duplicate customers (same email), keeping the oldest record.
40MediumYoY comparisonCompare September 2026 vs September 2025 GMV by category.
41HardGaps and islandsFind periods of consecutive days when a product was out of stock.
42HardMedianFind the median order value per city (without built-in MEDIAN function).
43HardRecursive CTEShow all managers and their direct + indirect reports (org chart).
44HardFunnel drop-offGiven an events table (viewed, added_to_cart, purchased), calculate conversion at each funnel step.
45HardFirst order analysisFor each customer, how much did they spend in the 30 days after first order?
46HardCumulative distributionWhat % of orders fall below ₹500, ₹1000, ₹2000, ₹5000?
47HardRunning rank resetRank orders by value within each customer's order history.
48HardSession analysisGiven a clickstream table with timestamps, group user events into sessions (30-min gap = new session).
49HardMulti-condition updateWrite a query that flags orders as "suspicious" if: same customer, same amount, within 1 hour.
50HardEXPLAIN optimisationRewrite this slow correlated subquery as a CTE + JOIN. Explain what makes it faster.
Continue the Series
← Ch 23: BI StrategyCh 25: Python Interview Prep →

Frequently Asked Questions

What SQL topics are tested in Indian data analyst interviews?

Based on patterns from Indian companies in 2026, the most frequently tested SQL topics for data analyst roles are: (1) JOINs — INNER, LEFT, and anti-joins using LEFT JOIN ... WHERE IS NULL. Almost every interview has at least one JOIN question. (2) GROUP BY and HAVING — aggregate functions (SUM, COUNT, AVG, MAX, MIN) with GROUP BY, and filtering aggregates with HAVING. (3) Window functions — RANK, DENSE_RANK, ROW_NUMBER for top-N-per-group problems; LAG/LEAD for period-over-period comparisons; running totals with SUM() OVER. (4) Subqueries and CTEs — especially multi-step business logic that requires intermediate aggregations. (5) NULL handling — COALESCE, NULLIF, IS NULL vs = NULL distinction. (6) Date functions — DATEDIFF, DATE_FORMAT, YEAR/MONTH/DAY extraction, period-over-period date arithmetic. (7) De-duplication — keeping the latest record per entity using ROW_NUMBER or MAX aggregation. The difficulty distribution is typically: 40% easy (basic SELECT/WHERE/GROUP BY), 40% medium (JOINs, subqueries, NULLs, date logic), 20% hard (window functions, CTEs, optimisation).

How do you prepare for a SQL interview in 2 weeks?

A structured 2-week SQL interview preparation plan: Week 1 — Solidify fundamentals. Day 1–2: SELECT, WHERE, ORDER BY, LIMIT. Day 3–4: GROUP BY, HAVING, aggregate functions (COUNT, SUM, AVG, MIN, MAX). Day 5–6: JOINs (INNER, LEFT, RIGHT, anti-join pattern). Day 7: NULL handling, COALESCE, CASE WHEN, date functions. Week 2 — Advanced and practice. Day 8–9: Subqueries (scalar, correlated, EXISTS). Day 10–11: Window functions (RANK, ROW_NUMBER, LAG, running totals). Day 12: CTEs and multi-step analysis. Day 13: Practice 10 medium-level questions timed (15 minutes each). Day 14: Review mistakes, practise explaining your solutions out loud. Key resources: LeetCode SQL (easy and medium), HackerRank SQL track, Mode Analytics SQL tutorial. For Indian context: practise on e-commerce and fintech scenarios (orders, customers, payments) rather than abstract problems. Be able to explain your solution in plain English — interviewers test understanding, not just correct syntax.

What is the hardest SQL concept tested in analyst interviews in India?

Window functions are consistently the hardest concept tested, specifically: (1) Top-N per group using ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) — then filtering with WHERE rn = 1 in an outer query. Many candidates write GROUP BY solutions that do not handle ties correctly. (2) Consecutive events — find customers who placed orders on 3 or more consecutive days. This requires LAG() with date arithmetic and a self-join or gaps-and-islands technique. (3) Running totals and period averages — "calculate a 7-day rolling average of daily orders" requires AVG() OVER with ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. (4) Cohort retention — what percentage of January customers placed a second order in February? This requires self-joining the orders table on customer_id with different date filters, which many candidates overcomplicate. The most common mistake is trying to solve window function problems with subqueries or multiple passes — window functions almost always produce cleaner, faster solutions.

EVIKA ACADEMY · NOIDA SECTOR 51

Crack Your SQL Interview

Our SQL interview preparation module covers 100+ practice problems on real Indian business datasets, with mock interview sessions and feedback from experienced data professionals.

Book Free Demo Class →
🎓 Free Demo Class — Online & Offline · Noida Sector 51