SQL Interview Questions — Joins, Window Functions, Optimization, and the Real Patterns Companies Test

Every SQL round opens with a JOIN. The named JOIN types — INNER, LEFT, RIGHT, FULL OUTER, CROSS — are easy. The pattern recognition is what gets tested: anti-joins, self-joins, cardinality bugs, multi-table fan-outs.

SELECT u.user_id, u.email
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.user_id
WHERE o.order_id IS NULL;

SELECT a.user_id AS user_a,
       b.user_id AS user_b,
       a.city
FROM users a
JOIN users b
  ON a.city = b.city
 AND a.user_id < b.user_id;

-- Window function approach (preferred)
SELECT salary
FROM (
  SELECT salary,
         DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk = 3;

-- Subquery approach (older portable form)
SELECT MAX(salary) AS third_highest
FROM employees
WHERE salary < (
  SELECT MAX(salary)
  FROM employees
  WHERE salary < (SELECT MAX(salary) FROM employees)
);

SELECT COALESCE(c.customer_id, b.customer_id) AS customer_id,
       c.email   AS crm_email,
       b.email   AS billing_email,
       CASE
         WHEN c.customer_id IS NULL THEN 'billing_only'
         WHEN b.customer_id IS NULL THEN 'crm_only'
         ELSE 'both'
       END AS source
FROM crm_customers c
FULL OUTER JOIN billing_customers b
  ON c.customer_id = b.customer_id;

-- Wrong: order_items has many rows per order, payments has many rows per order
SELECT o.order_id, oi.product_id, p.amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN payments    p  ON p.order_id  = o.order_id;

-- Right: aggregate before joining, or join along a single chain
SELECT o.order_id, oi.product_id, p_total.amount
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN (
  SELECT order_id, SUM(amount) AS amount
  FROM payments
  GROUP BY order_id
) p_total ON p_total.order_id = o.order_id;

Window functions are the single highest-leverage skill in modern SQL. ROW_NUMBER, RANK, LAG, LEAD, FIRST_VALUE, plus running aggregates with frames. If a candidate cannot describe the difference between RANK and DENSE_RANK, the round usually ends there.

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

SELECT category,
       product_id,
       revenue,
       RANK() OVER (
         PARTITION BY category
         ORDER BY revenue DESC
       ) AS revenue_rank
FROM (
  SELECT p.category,
         p.product_id,
         SUM(oi.quantity * oi.unit_price) AS revenue
  FROM products p
  JOIN order_items oi ON oi.product_id = p.product_id
  GROUP BY p.category, p.product_id
) t;

WITH weekly AS (
  SELECT user_id,
         DATE_TRUNC('week', session_start) AS week,
         COUNT(*) AS sessions
  FROM sessions
  GROUP BY user_id, DATE_TRUNC('week', session_start)
),
flagged AS (
  SELECT user_id,
         week,
         sessions,
         LAG(sessions, 1) OVER (PARTITION BY user_id ORDER BY week) AS prev1,
         LAG(sessions, 2) OVER (PARTITION BY user_id ORDER BY week) AS prev2,
         LAG(sessions, 3) OVER (PARTITION BY user_id ORDER BY week) AS prev3
  FROM weekly
)
SELECT user_id, week
FROM flagged
WHERE prev3 > prev2
  AND prev2 > prev1
  AND prev1 > sessions;

SELECT department_id, employee_id, salary
FROM (
  SELECT department_id,
         employee_id,
         salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, employee_id
         ) AS rn
  FROM employees
) t
WHERE rn <= 3;

SELECT DISTINCT
       user_id,
       FIRST_VALUE(login_at) OVER (
         PARTITION BY user_id ORDER BY login_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS first_login,
       LAST_VALUE(login_at) OVER (
         PARTITION BY user_id ORDER BY login_at
         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_login
FROM logins;

Most candidates know GROUP BY. Few can articulate when WHERE versus HAVING applies, why COUNT(DISTINCT) is expensive, and how to push predicates down. These three questions probe exactly that boundary.

SELECT user_id, COUNT(*) AS order_count
FROM orders
WHERE order_date >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY user_id
HAVING COUNT(*) > 5;

SELECT d.department_id,
       d.department_name,
       AVG(e.salary) AS dept_avg
FROM departments d
JOIN employees e ON e.department_id = d.department_id
GROUP BY d.department_id, d.department_name
HAVING AVG(e.salary) > (SELECT AVG(salary) FROM employees);

SELECT p.category,
       COUNT(DISTINCT o.user_id) AS distinct_buyers
FROM orders o
JOIN order_items oi ON oi.order_id = o.order_id
JOIN products    p  ON p.product_id = oi.product_id
GROUP BY p.category
HAVING COUNT(DISTINCT o.user_id) >= 100;

CTEs make queries readable. Recursive CTEs solve hierarchy traversal, sessionization, and gap-and-island problems that look impossible without them. These are the questions that signal a senior IC.

WITH RECURSIVE chain AS (
  SELECT employee_id,
         manager_id,
         employee_name,
         1 AS depth,
         CAST(employee_name AS TEXT) AS path
  FROM employees
  WHERE manager_id IS NULL  -- CEO

  UNION ALL

  SELECT e.employee_id,
         e.manager_id,
         e.employee_name,
         c.depth + 1,
         c.path || ' -> ' || e.employee_name
  FROM employees e
  JOIN chain c ON e.manager_id = c.employee_id
)
SELECT employee_id, employee_name, depth, path
FROM chain
ORDER BY depth, employee_name;

WITH gaps AS (
  SELECT user_id,
         event_time,
         CASE
           WHEN event_time - LAG(event_time) OVER (
             PARTITION BY user_id ORDER BY event_time
           ) > INTERVAL '30 minutes'
           THEN 1 ELSE 0
         END AS is_new_session
  FROM events
),
sessioned AS (
  SELECT user_id,
         event_time,
         SUM(is_new_session) OVER (
           PARTITION BY user_id ORDER BY event_time
         ) AS session_index
  FROM gaps
)
SELECT user_id,
       session_index,
       MIN(event_time) AS session_start,
       MAX(event_time) AS session_end,
       COUNT(*) AS event_count
FROM sessioned
GROUP BY user_id, session_index;

WITH days AS (
  SELECT DISTINCT user_id, DATE(event_time) AS day
  FROM events
),
streaks AS (
  SELECT user_id,
         day,
         day - INTERVAL '1 day' * ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY day
         ) AS streak_group
  FROM days
)
SELECT user_id,
       MIN(day) AS streak_start,
       MAX(day) AS streak_end,
       COUNT(*) AS streak_days
FROM streaks
GROUP BY user_id, streak_group
HAVING COUNT(*) >= 7;

Indexes, EXPLAIN plans, anti-patterns. Optimization questions are where DE and senior backend SWE rounds separate from junior ones. The expected answer is not a correct query — it is the correct mental loop.

SELECT user_id, order_id, total
FROM orders
WHERE status = 'shipped'
  AND created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC
LIMIT 100;

SELECT *
FROM users
WHERE LOWER(email) = 'shekhar@example.com'
  AND created_at::date = '2026-05-05'
  AND id IN (SELECT user_id FROM banned_users);

EXPLAIN ANALYZE
SELECT order_id, total
FROM orders
WHERE customer_id = 12345 AND status = 'shipped';

-- Plan output excerpt:
-- Seq Scan on orders (cost=0.00..1234567.00 rows=80000 width=16)
--   Filter: ((customer_id = 12345) AND (status = 'shipped'))
--   Rows Removed by Filter: 49920000

Schema design rounds are open-ended on purpose. The interviewer wants to see how you decompose entities, where you normalize, where you denormalize for read performance, and what you leave out for a follow-up.

-- Core entities
CREATE TABLE users (
  user_id      BIGSERIAL PRIMARY KEY,
  phone        TEXT UNIQUE NOT NULL,
  email        TEXT UNIQUE,
  role         TEXT NOT NULL CHECK (role IN ('rider','driver','both')),
  created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE drivers (
  user_id        BIGINT PRIMARY KEY REFERENCES users(user_id),
  license_number TEXT UNIQUE NOT NULL,
  rating         NUMERIC(3,2),
  status         TEXT NOT NULL CHECK (status IN ('offline','available','on_trip'))
);

CREATE TABLE vehicles (
  vehicle_id    BIGSERIAL PRIMARY KEY,
  driver_id     BIGINT NOT NULL REFERENCES drivers(user_id),
  plate         TEXT UNIQUE NOT NULL,
  vehicle_class TEXT NOT NULL  -- 'standard','xl','luxury'
);

CREATE TABLE trips (
  trip_id       BIGSERIAL PRIMARY KEY,
  rider_id      BIGINT NOT NULL REFERENCES users(user_id),
  driver_id     BIGINT REFERENCES drivers(user_id),
  vehicle_id    BIGINT REFERENCES vehicles(vehicle_id),
  origin_lat    NUMERIC(9,6) NOT NULL,
  origin_lng    NUMERIC(9,6) NOT NULL,
  dest_lat      NUMERIC(9,6) NOT NULL,
  dest_lng      NUMERIC(9,6) NOT NULL,
  requested_at  TIMESTAMPTZ NOT NULL,
  picked_up_at  TIMESTAMPTZ,
  completed_at  TIMESTAMPTZ,
  status        TEXT NOT NULL,  -- requested, matched, in_progress, completed, cancelled
  fare_cents    INTEGER
);

CREATE INDEX idx_trips_rider ON trips(rider_id, requested_at DESC);
CREATE INDEX idx_trips_driver ON trips(driver_id, requested_at DESC);
CREATE INDEX idx_trips_status ON trips(status) WHERE status IN ('requested','matched','in_progress');

-- Wide event table, partitioned by day
CREATE TABLE events (
  event_id      UUID PRIMARY KEY,
  event_time    TIMESTAMPTZ NOT NULL,
  event_name    TEXT NOT NULL,
  user_id       BIGINT,
  session_id    UUID,
  device_id     TEXT,
  app_version   TEXT,
  platform      TEXT,
  country       TEXT,
  -- semi-structured payload for evolving event-specific fields
  properties    JSONB
) PARTITION BY RANGE (event_time);

CREATE INDEX idx_events_user_time ON events (user_id, event_time DESC);
CREATE INDEX idx_events_name_time ON events (event_name, event_time DESC);
CREATE INDEX idx_events_props_gin ON events USING GIN (properties);

-- Daily partition
CREATE TABLE events_2026_05_05 PARTITION OF events
  FOR VALUES FROM ('2026-05-05') TO ('2026-05-06');