SQL interview questions you can explain and test

Exclude NULL salaries from the average; SQL AVG already does that. An employee with a NULL salary will not satisfy the comparison.

SELECT emp_id, emp_name, department, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC, emp_id;

Assume one row per order and one category per order in orders. If an order spans categories, use order-item amounts and define how to count distinct orders.

SELECT category, COUNT(*) AS order_count,
       SUM(order_amount) AS revenue
FROM orders
GROUP BY category
ORDER BY revenue DESC, category;

This question uses exact non-NULL email matches. Ask whether case and surrounding spaces should be normalised.

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

The result is the second distinct salary, not the second employee in a sorted list.

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

NOT EXISTS checks whether any matching order item exists without multiplying product rows.

SELECT p.id, p.product_name, p.category
FROM products AS p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items AS oi
  WHERE oi.product_id = p.id
)
ORDER BY p.id;

Return exactly three cities where possible. The city name breaks ties deterministically; use ranking if all tied cities must be included.

SELECT city, COUNT(*) AS customer_count
FROM customers
WHERE city IS NOT NULL
GROUP BY city
ORDER BY customer_count DESC, city
LIMIT 3;

Return every employee tied for the highest known salary. DENSE_RANK makes the tie policy explicit.

WITH ranked AS (
  SELECT emp_id, emp_name, department, salary,
         DENSE_RANK() OVER (
           PARTITION BY department ORDER BY salary DESC
         ) AS salary_rank
  FROM employees
  WHERE salary IS NOT NULL
)
SELECT emp_id, emp_name, department, salary
FROM ranked
WHERE salary_rank = 1
ORDER BY department, emp_id;

Use half-open date ranges and NOT EXISTS. This avoids the NULL behaviour that can make NOT IN unexpectedly exclude every row.

SELECT DISTINCT jan.customer_id
FROM orders AS jan
WHERE jan.order_date >= DATE '2024-01-01'
  AND jan.order_date < DATE '2024-02-01'
  AND jan.customer_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM orders AS feb
    WHERE feb.customer_id = jan.customer_id
      AND feb.order_date >= DATE '2024-02-01'
      AND feb.order_date < DATE '2024-03-01'
  )
ORDER BY jan.customer_id;

Aggregate to one row per day before applying the window. order_date is assumed to be a DATE; timestamps need a reporting-time-zone decision first.

WITH daily AS (
  SELECT order_date, SUM(order_amount) AS daily_revenue
  FROM orders
  GROUP BY order_date
)
SELECT order_date, daily_revenue,
       SUM(daily_revenue) OVER (
         ORDER BY order_date
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_revenue
FROM daily
ORDER BY order_date;

Multiply by 100.0 to avoid integer division. NULLIF prevents division by zero when total revenue is zero.

WITH category_totals AS (
  SELECT category, SUM(order_amount) AS revenue
  FROM orders
  GROUP BY category
)
SELECT category, revenue,
       ROUND(100.0 * revenue /
         NULLIF(SUM(revenue) OVER (), 0), 2) AS revenue_pct
FROM category_totals
ORDER BY revenue DESC, category;

Use the manager ID as part of the grouping so people with the same name are not merged. This returns managers with at least one direct report.

SELECT m.emp_id AS manager_id, m.emp_name AS manager,
       COUNT(e.emp_id) AS direct_reports
FROM employees AS m
JOIN employees AS e ON e.manager_id = m.emp_id
GROUP BY m.emp_id, m.emp_name
ORDER BY direct_reports DESC, manager_id;

This PostgreSQL example covers 30 calendar dates including today. Deduplicate user/date pairs before creating consecutive-day groups.

WITH days AS (
  SELECT DISTINCT user_id, purchase_date::date AS dt
  FROM purchases
  WHERE purchase_date >= CURRENT_DATE - INTERVAL '29 days'
    AND purchase_date < CURRENT_DATE + INTERVAL '1 day'
    AND user_id IS NOT NULL
), groups AS (
  SELECT user_id, dt,
         dt - (ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY dt
         ))::integer AS group_key
  FROM days
)
SELECT DISTINCT user_id
FROM groups
GROUP BY user_id, group_key
HAVING COUNT(*) >= 3
ORDER BY user_id;

Assume dau_table has exactly one row per product and calendar date, including explicit zero-activity days. Seven rows then represent seven days.

SELECT product_id, dt, dau,
       CASE WHEN COUNT(*) OVER w = 7
            THEN AVG(dau) OVER w END AS rolling_7d_avg
FROM dau_table
WINDOW w AS (
  PARTITION BY product_id ORDER BY dt
  ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)
ORDER BY product_id, dt;

An order is identified by order_id. Break same-day ties with that ID, and include customer_id in the result.

WITH ranked AS (
  SELECT customer_id, order_date,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date, order_id
         ) AS rn
  FROM orders
  WHERE customer_id IS NOT NULL
), first_two AS (
  SELECT customer_id,
         MAX(CASE WHEN rn = 1 THEN order_date END) AS first_order,
         MAX(CASE WHEN rn = 2 THEN order_date END) AS second_order
  FROM ranked
  WHERE rn <= 2
  GROUP BY customer_id
)
SELECT customer_id, first_order, second_order,
       second_order - first_order AS gap_days
FROM first_two
ORDER BY customer_id;