Top SQL Interview Questions
for Data Analysts

The 15 most asked SQL questions in data analyst interviews — each with a real dataset, a complete answer, a step-by-step explanation, and the follow-up cross-questions interviewers actually ask.

All questions use three tables: employees, orders, and products. The schema and sample data are shown before each question where relevant. Questions build in difficulty — start from Q1 even if you are experienced, as interviewers use early questions to warm up before harder cross-questions.

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
-- Result: 120000

SELECT salary AS second_highest_salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;
-- Result: 120000

SELECT emp_name, salary
FROM employees
WHERE salary = (
  SELECT MAX(salary)
  FROM employees
  WHERE salary < (SELECT MAX(salary) FROM employees)
);
-- Returns: Rohan Mehta, Karan Patel — both at 120000

SELECT
  emp_name,
  salary,
  ROW_NUMBER()  OVER (ORDER BY salary DESC) AS row_num,
  RANK()        OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK()  OVER (ORDER BY salary DESC) AS dense_rnk
FROM employees;

DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank

-- Step 1: Find which combinations are duplicated
SELECT customer_id, product_id, order_date, COUNT(*) AS occurrences
FROM orders
GROUP BY customer_id, product_id, order_date
HAVING COUNT(*) > 1;
-- Shows: C001/P01/2026-10-01 appears 2 times
--        C002/P02/2026-10-02 appears 2 times

-- Step 2: Return all duplicate rows with their order_id
SELECT *
FROM orders
WHERE (customer_id, product_id, order_date) IN (
  SELECT customer_id, product_id, order_date
  FROM orders
  GROUP BY customer_id, product_id, order_date
  HAVING COUNT(*) > 1
)
ORDER BY customer_id, order_date;

-- Keep the row with the lowest order_id, delete the rest
DELETE FROM orders
WHERE order_id NOT IN (
  SELECT MIN(order_id)
  FROM orders
  GROUP BY customer_id, product_id, order_date
);

SELECT
  order_id,
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders
ORDER BY order_date;

SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date
                   ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

-- Self-join: join the table to itself
SELECT
  e.emp_name        AS employee,
  e.salary          AS employee_salary,
  m.emp_name        AS manager,
  m.salary          AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.salary > m.salary;

-- Result: Priya Sharma (95k) earns less than Neha Joshi (155k) — no result here
-- But if Sneha Gupta earned 100k with Priya (95k) as manager, she would appear

WITH RECURSIVE hierarchy AS (
  SELECT emp_id, emp_name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL  -- top level
  UNION ALL
  SELECT e.emp_id, e.emp_name, e.manager_id, h.depth + 1
  FROM employees e
  JOIN hierarchy h ON e.manager_id = h.emp_id
)
SELECT * FROM hierarchy ORDER BY depth;

SELECT emp_name, department, salary
FROM (
  SELECT
    emp_name,
    department,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS dept_rank
  FROM employees
) ranked
WHERE dept_rank <= 2
ORDER BY department, salary DESC;

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount)                     AS revenue
  FROM orders
  GROUP BY 1
),
with_prev AS (
  SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue
  FROM monthly_revenue
)
SELECT
  TO_CHAR(month, 'Mon YYYY')                                       AS month,
  revenue,
  prev_month_revenue,
  ROUND(
    100.0 * (revenue - prev_month_revenue) / prev_month_revenue, 1
  ) AS mom_change_pct
FROM with_prev
ORDER BY month;

-- Method 1: LEFT JOIN + NULL check (most readable)
SELECT c.customer_id, c.customer_name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;

-- Method 2: NOT IN subquery
SELECT customer_id, customer_name
FROM customers
WHERE customer_id NOT IN (
  SELECT DISTINCT customer_id FROM orders
  WHERE customer_id IS NOT NULL  -- ← CRITICAL: NOT IN fails silently if NULLs exist
);

-- Method 3: NOT EXISTS (best performance on large tables)
SELECT customer_id, customer_name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.customer_id = c.customer_id
);
-- Result: C005 (Dev Sharma) — no orders in the orders table

WITH customer_months AS (
  -- Distinct months each customer ordered in
  SELECT DISTINCT
    customer_id,
    DATE_TRUNC('month', order_date) AS order_month
  FROM orders
),
with_prev AS (
  SELECT
    customer_id,
    order_month,
    LAG(order_month, 1) OVER (PARTITION BY customer_id ORDER BY order_month) AS prev1,
    LAG(order_month, 2) OVER (PARTITION BY customer_id ORDER BY order_month) AS prev2
  FROM customer_months
)
SELECT DISTINCT customer_id
FROM with_prev
WHERE order_month = prev1 + INTERVAL '1 month'
  AND order_month = prev2 + INTERVAL '2 months';
-- A customer appears only if they have 3 consecutive months

SELECT customer_id
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '6 months'
GROUP BY customer_id
HAVING COUNT(DISTINCT DATE_TRUNC('month', order_date)) = 6;

SELECT
  e.department,
  SUM(CASE WHEN DATE_TRUNC('month', o.order_date) = '2026-10-01' THEN o.amount ELSE 0 END) AS oct_revenue,
  SUM(CASE WHEN DATE_TRUNC('month', o.order_date) = '2026-11-01' THEN o.amount ELSE 0 END) AS nov_revenue,
  SUM(CASE WHEN DATE_TRUNC('month', o.order_date) = '2026-12-01' THEN o.amount ELSE 0 END) AS dec_revenue,
  SUM(o.amount) AS total_revenue
FROM orders o
JOIN employees e ON o.customer_id = e.emp_id::text  -- join via employee who placed order
GROUP BY e.department
ORDER BY total_revenue DESC;

SELECT customer_id, 'oct_revenue' AS month, oct_revenue AS amount FROM wide_table
UNION ALL
SELECT customer_id, 'nov_revenue', nov_revenue FROM wide_table
UNION ALL
SELECT customer_id, 'dec_revenue', dec_revenue FROM wide_table;

-- The ROW_NUMBER approach (works in PostgreSQL, SQL Server, BigQuery)
WITH ranked AS (
  SELECT
    order_id,
    ROW_NUMBER() OVER (
      PARTITION BY customer_id, product_id, order_date
      ORDER BY order_id ASC   -- keep the lowest order_id
    ) AS rn
  FROM orders
)
DELETE FROM orders
WHERE order_id IN (
  SELECT order_id FROM ranked WHERE rn > 1
);

-- Verify: run SELECT COUNT(*) FROM orders before and after

DELETE FROM orders
WHERE order_id NOT IN (
  SELECT min_id FROM (
    SELECT MIN(order_id) AS min_id
    FROM orders
    GROUP BY customer_id, product_id, order_date
  ) AS keepers
);

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date)                    AS month,
    EXTRACT(YEAR  FROM order_date)                     AS yr,
    EXTRACT(MONTH FROM order_date)                     AS mo,
    SUM(amount)                                        AS revenue
  FROM orders
  GROUP BY 1, 2, 3
),
yoy AS (
  SELECT
    cur.month,
    cur.revenue                                        AS current_revenue,
    prev.revenue                                       AS prev_year_revenue,
    ROUND(
      100.0 * (cur.revenue - prev.revenue) / prev.revenue, 1
    )                                                  AS yoy_growth_pct
  FROM monthly cur
  LEFT JOIN monthly prev
    ON cur.mo = prev.mo
   AND cur.yr = prev.yr + 1   -- same calendar month, one year earlier
)
SELECT * FROM yoy ORDER BY month;

WITH monthly AS (
  SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(amount)                     AS revenue
  FROM orders
  GROUP BY 1
)
SELECT
  TO_CHAR(month, 'Mon YYYY')   AS month,
  revenue,
  ROUND(
    AVG(revenue) OVER (
      ORDER BY month
      ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ), 0
  )                            AS rolling_3m_avg
FROM monthly
ORDER BY month;

-- Simple GROUP BY approach
SELECT
  customer_id,
  MIN(order_date)   AS first_order,
  MAX(order_date)   AS last_order,
  COUNT(order_id)   AS total_orders,
  SUM(amount)       AS total_spend,
  CURRENT_DATE - MAX(order_date) AS days_since_last_order
FROM orders
GROUP BY customer_id
ORDER BY total_spend DESC;

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date ASC)  AS rn_first,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn_last
  FROM orders
)
SELECT
  customer_id,
  MAX(CASE WHEN rn_first = 1 THEN order_date END) AS first_order_date,
  MAX(CASE WHEN rn_first = 1 THEN amount END)     AS first_order_amount,
  MAX(CASE WHEN rn_last  = 1 THEN order_date END) AS last_order_date,
  MAX(CASE WHEN rn_last  = 1 THEN amount END)     AS last_order_amount
FROM ranked
GROUP BY customer_id;

WITH
-- Assign a sequential row number per user per date
ranked AS (
  SELECT
    user_id,
    login_date,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) AS rn
  FROM (
    SELECT DISTINCT user_id, login_date FROM user_logins
  ) deduped
),
-- The island key: login_date - rn stays constant within a consecutive streak
-- because consecutive dates decrease the gap by 1, and rn increases by 1
island_key AS (
  SELECT
    user_id,
    login_date,
    login_date - CAST(rn AS INT) AS grp  -- constant within each consecutive run
  FROM ranked
),
-- Count streak lengths
streaks AS (
  SELECT
    user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_length
  FROM island_key
  GROUP BY user_id, grp
)
SELECT
  user_id,
  streak_start,
  streak_end,
  streak_length AS longest_streak
FROM streaks
WHERE (user_id, streak_length) IN (
  SELECT user_id, MAX(streak_length) FROM streaks GROUP BY user_id
)
ORDER BY user_id;
-- U001: Oct 1–3 = 3 days, Oct 6–7 = 2 days → longest = 3
-- U002: Oct 1 = 1 day, Oct 4 = 1 day → longest = 1

SELECT
  user_id,
  prev_login + INTERVAL '1 day'   AS gap_start,
  login_date - INTERVAL '1 day'   AS gap_end,
  login_date - prev_login - 1     AS gap_days
FROM (
  SELECT
    user_id,
    login_date,
    LAG(login_date) OVER (PARTITION BY user_id ORDER BY login_date) AS prev_login
  FROM (SELECT DISTINCT user_id, login_date FROM user_logins) d
) lagged
WHERE login_date - prev_login > 1;

Quick Reference: What Interviewers Are Testing

Practice These Questions Live at EVIKA ACADEMY

Hands-on SQL training with mock interviews, real datasets, and expert feedback. Batches in Noida Sector 51.