SQL Interview Questions

SELECT e.first_name, e.salary, m.first_name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.employee_id = e.manager_id
WHERE e.salary > m.salary;

SELECT e.*
FROM employees e
JOIN jobs j ON j.job_id = e.job_id
WHERE e.salary NOT BETWEEN j.min_salary AND j.max_salary;

SELECT a.name AS emp1, b.name AS emp2, a.dept_id
FROM employees a
JOIN employees b ON a.dept_id = b.dept_id AND a.emp_id < b.emp_id;

SELECT a.id, a.val, b.val FROM a LEFT JOIN b ON a.id = b.id
UNION ALL
SELECT b.id, NULL, b.val FROM a RIGHT JOIN b ON a.id = b.id
WHERE a.id IS NULL;

SELECT s.store_id, d.day, COALESCE(SUM(f.amount), 0) AS sales
FROM stores s
CROSS JOIN calendar d
LEFT JOIN fact_sales f ON f.store_id = s.store_id AND f.sale_date = d.day
GROUP BY s.store_id, d.day;

SELECT e.name, e.hire_date, m.name AS manager, m.hire_date AS manager_hired
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id     -- alias the same table twice
WHERE e.hire_date < m.hire_date;
-- Chen | 2014-06-15 | Asha | 2015-01-10

SELECT e.name
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
WHERE m.emp_id IS NULL;                 -- Asha, Gus  (wrong: Asha has no manager at all)

SELECT e.name, e.manager_id
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
WHERE m.emp_id IS NULL
  AND e.manager_id IS NOT NULL;         -- Gus | 99

SELECT a.name, b.name AS colleague, ABS(a.salary - b.salary) AS diff
FROM employees a
JOIN employees b
  ON  a.dept = b.dept
  AND a.emp_id < b.emp_id               -- each unordered pair once, never a row with itself
  AND ABS(a.salary - b.salary) <= 20;
-- Ben–Chen 10 · Ben–Eli 20 · Chen–Eli 10

SELECT e.name,
       m.name  AS manager,
       gm.name AS skip_level
FROM employees e
LEFT JOIN employees m  ON m.emp_id  = e.manager_id
LEFT JOIN employees gm ON gm.emp_id = m.manager_id;
-- Asha  | NULL | NULL
-- Ben   | Asha | NULL
-- Dia   | Ben  | Asha
-- Fay   | Dia  | Ben  …

WITH RECURSIVE chain AS (
  SELECT emp_id, name, manager_id, 0 AS lvl
  FROM employees WHERE name = 'Fay'
  UNION ALL
  SELECT m.emp_id, m.name, m.manager_id, c.lvl + 1
  FROM employees m
  JOIN chain c ON m.emp_id = c.manager_id
)
SELECT lvl, name FROM chain ORDER BY lvl;
-- 0 Fay · 1 Dia · 2 Ben · 3 Asha

WITH RECURSIVE sub AS (
  SELECT emp_id, name, 0 AS depth, ',' || emp_id || ',' AS path
  FROM employees WHERE emp_id = 2                         -- Ben
  UNION ALL
  SELECT e.emp_id, e.name, s.depth + 1, s.path || e.emp_id || ','
  FROM employees e
  JOIN sub s ON e.manager_id = s.emp_id
  WHERE s.path NOT LIKE '%,' || e.emp_id || ',%'          -- skip anyone already on this path
    AND s.depth < 20                                      -- hard safety limit
)
SELECT depth, name FROM sub ORDER BY depth, name;

SELECT c.customer_id, o.order_id
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.status = 'SHIPPED';

SELECT CASE region WHEN NULL THEN 'missing' ELSE region END FROM sales;

SELECT ts, device_id,
       LAST_VALUE(reading IGNORE NULLS) OVER (
         PARTITION BY device_id ORDER BY ts
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS reading_filled
FROM sensor_readings;

SELECT COUNT(*)                                  AS total_rows,
       COUNT(*) - COUNT(email)                   AS null_emails,
       SUM(CASE WHEN phone IS NULL THEN 1 ELSE 0 END) AS null_phones
FROM customers;

-- employees who manage nobody (manager_id contains NULL for the CEO)
SELECT * FROM employees e
WHERE NOT EXISTS (SELECT 1 FROM employees r WHERE r.manager_id = e.employee_id);

SELECT region,
       SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1,
       SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2,
       COUNT(*) FILTER (WHERE revenue > 1000)              AS big_deals  -- Postgres / SQLite
FROM sales
GROUP BY region;

SELECT order_date, revenue,
       SUM(revenue) OVER (ORDER BY order_date) AS running_total,
       AVG(revenue) OVER (ORDER BY order_date
                          ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7d
FROM daily_revenue;

LAST_VALUE(salary) OVER (PARTITION BY dept ORDER BY salary
                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

SELECT * FROM employees
QUALIFY ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) = 1;

SELECT dept_id, name, salary,
       salary * 100.0 / SUM(salary) OVER ()                     AS pct_of_company,
       salary * 100.0 / SUM(salary) OVER (PARTITION BY dept_id) AS pct_of_dept
FROM employees;

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month)                      AS change,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1)         AS pct_change
FROM monthly_revenue;

WITH first_seen AS (
  SELECT user_id, MIN(activity_date) AS first_day
  FROM activity GROUP BY user_id
)
SELECT first_day,
       SUM(COUNT(*)) OVER (ORDER BY first_day) AS cumulative_users
FROM first_seen
GROUP BY first_day;

SELECT e.* FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees x WHERE x.dept_id = e.dept_id);

SELECT * FROM (
  SELECT e.*, AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg FROM employees e
) t
WHERE salary > dept_avg;

WITH RECURSIVE org(employee_id, lvl, path) AS (
  SELECT employee_id, 1, first_name FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.employee_id, o.lvl + 1, o.path || ' > ' || e.first_name
  FROM employees e JOIN org o ON e.manager_id = o.employee_id
)
SELECT * FROM org ORDER BY path;

WITH RECURSIVE days(d) AS (
  SELECT DATE '2026-01-01'
  UNION ALL
  SELECT d + INTERVAL '1 day' FROM days WHERE d < DATE '2026-01-31'
)
SELECT d FROM days
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.order_date = d);

CREATE VIEW active_customers AS
SELECT customer_id, name, city
FROM customers
WHERE status = 'ACTIVE';

SELECT * FROM active_customers WHERE city = 'Pune';   -- runs the view's query with your filter added

CREATE VIEW pune_customers AS
SELECT customer_id, name, city FROM customers WHERE city = 'Pune'
WITH CHECK OPTION;

INSERT INTO pune_customers VALUES (9, 'Ravi', 'Pune');     -- OK
INSERT INTO pune_customers VALUES (10, 'Meera', 'Delhi');  -- ERROR: violates the view's WHERE

-- Postgres
CREATE MATERIALIZED VIEW daily_sales AS
SELECT order_date, region, SUM(amount) AS revenue
FROM orders GROUP BY order_date, region;

CREATE UNIQUE INDEX ON daily_sales (order_date, region);   -- required for CONCURRENTLY
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales;        -- readers are not blocked

df.createOrReplaceTempView("txn")                 # visible only in this SparkSession
spark.sql("SELECT channel, COUNT(*) FROM txn GROUP BY channel")

df.createOrReplaceGlobalTempView("txn_shared")    # visible to all sessions of this application
spark.sql("SELECT * FROM global_temp.txn_shared")  # note the global_temp database

spark.sql("""CREATE OR REPLACE VIEW gold.v_daily_txn AS
             SELECT dt, SUM(amount) AS total FROM silver.transactions GROUP BY dt""")  # permanent, in the catalog

SELECT * FROM orders
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 50;

SELECT * FROM alerts
ORDER BY CASE risk_level WHEN 'HIGH' THEN 1 WHEN 'MEDIUM' THEN 2 WHEN 'LOW' THEN 3 ELSE 4 END,
         created_at DESC;

-- Postgres / SQL Server / Oracle style (SQL Server can DELETE from the CTE directly)
DELETE FROM customers
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM customers
  ) t
  WHERE rn > 1
);

UPDATE employees
SET gender = CASE gender WHEN 'M' THEN 'F' WHEN 'F' THEN 'M' ELSE gender END;

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY updated_at DESC) AS rn
  FROM customers_raw
)
SELECT * FROM ranked WHERE rn = 1;

SELECT dept_id, salary
FROM (
  SELECT dept_id, salary,
         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

SELECT (
  SELECT DISTINCT salary FROM employees
  ORDER BY salary DESC
  LIMIT 1 OFFSET 2          -- N - 1, here N = 3
) AS third_highest;

SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

WITH d AS (
  SELECT DISTINCT user_id, login_date FROM logins
), g AS (
  SELECT user_id, login_date,
         login_date - ROW_NUMBER() OVER (PARTITION BY user_id
                                         ORDER BY login_date) * INTERVAL '1 day' AS grp
  FROM d
)
SELECT user_id, MIN(login_date) AS start_day, COUNT(*) AS streak
FROM g GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

SELECT department_id, AVG(salary) AS median_salary
FROM (
  SELECT department_id, salary,
         ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary) AS rn,
         COUNT(*)     OVER (PARTITION BY department_id)                 AS cnt
  FROM employees
) t
WHERE rn IN ((cnt + 1) / 2, (cnt + 2) / 2)
GROUP BY department_id;

SELECT dept_id, name, salary
FROM (
  SELECT e.*, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees e
) t
WHERE rnk = 1;

SELECT customer_id
FROM purchases
GROUP BY customer_id
HAVING COUNT(DISTINCT product_id) = (SELECT COUNT(*) FROM products);

SELECT user_id, order_id, order_ts
FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_ts, order_id) AS rn
  FROM orders o
) t
WHERE rn = 2;

SELECT u.signup_date,
       COUNT(*)                                  AS signups,
       COUNT(a.user_id)                          AS retained_d7,
       ROUND(100.0 * COUNT(a.user_id) / COUNT(*), 1) AS retention_pct
FROM users u
LEFT JOIN (SELECT DISTINCT user_id, activity_date FROM activity) a
       ON a.user_id = u.user_id
      AND a.activity_date = u.signup_date + INTERVAL '7 day'
GROUP BY u.signup_date;