# SQL Interview Questions (2026): Window Functions, Optimization, and Advanced Queries

Source: https://www.techinterview.org/post/3233460377/sql-interview-questions-2026-window-functions-optimization-and-advanced-queries/
Updated: 2026-04-16 · techinterview.org

## SQL Interview Questions (2026): Window Functions, Optimization, and Advanced Queries

SQL is tested in virtually every technical interview — data engineering, backend engineering, analytics engineering, and ML engineering roles all expect SQL proficiency. This guide covers the most commonly tested SQL patterns, from basic joins to advanced window functions, query optimization, and schema design.

## Window Functions (Most Tested Topic)

Window functions are the #1 differentiator between average and strong SQL candidates. They compute values across a set of rows "related to" the current row, without collapsing rows like GROUP BY.


```
-- Setup: orders table
-- orders(order_id, customer_id, amount, order_date, category)

-- 1. ROW_NUMBER: assign sequential rank within partition
SELECT
    customer_id,
    order_id,
    order_date,
    amount,
    ROW_NUMBER() OVER (
        PARTITION BY customer_id
        ORDER BY order_date DESC
    ) AS recency_rank
FROM orders;
-- Use case: "Get the most recent order per customer"
-- Filter WHERE recency_rank = 1


-- 2. RANK vs DENSE_RANK vs ROW_NUMBER
SELECT
    customer_id,
    amount,
    ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num,  -- no ties: 1,2,3,4,5
    RANK()       OVER (ORDER BY amount DESC) AS rank_val,  -- ties: 1,2,2,4,5
    DENSE_RANK() OVER (ORDER BY amount DESC) AS dense_val  -- ties: 1,2,2,3,4
FROM orders;


-- 3. LAG / LEAD: access previous/next row
SELECT
    customer_id,
    order_date,
    amount,
    LAG(amount, 1, 0) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS prev_order_amount,
    amount - LAG(amount, 1, 0) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS amount_change
FROM orders;


-- 4. Running totals (ROWS BETWEEN)
SELECT
    order_date,
    amount,
    SUM(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW  -- 7-day moving average
    ) AS rolling_7day_avg
FROM orders;


-- 5. NTILE: divide rows into N buckets
SELECT
    customer_id,
    amount,
    NTILE(4) OVER (ORDER BY amount) AS spending_quartile
FROM orders;
-- Q1: bottom 25%, Q4: top 25%


-- 6. FIRST_VALUE / LAST_VALUE
SELECT
    customer_id,
    order_date,
    amount,
    FIRST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_order_amount,
    LAST_VALUE(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_order_amount
FROM orders;
```


## Commonly Tested Query Patterns

### Deduplication


```
-- Remove duplicate rows, keep most recent
-- WRONG: DELETE FROM table WHERE id NOT IN (SELECT MIN(id) ...)  ← slow, correlated

-- RIGHT: CTE + window function approach
WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY email          -- columns that define a duplicate
            ORDER BY created_at DESC    -- keep newest
        ) AS rn
    FROM users
)
DELETE FROM users
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

-- Read-only dedup (for SELECT):
SELECT DISTINCT ON (email) *  -- PostgreSQL
FROM users
ORDER BY email, created_at DESC;
```


### Median Calculation


```
-- Median using PERCENTILE_CONT (SQL standard)
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

-- Without PERCENTILE_CONT (some databases):
WITH ordered AS (
    SELECT
        amount,
        ROW_NUMBER() OVER (ORDER BY amount) AS rn,
        COUNT(*) OVER () AS total
    FROM orders
)
SELECT AVG(amount) AS median_amount
FROM ordered
WHERE rn IN (FLOOR((total + 1) / 2), CEIL((total + 1) / 2));
```


### Cohort Retention Analysis


```
-- Classic analytics query: user retention by signup cohort
-- "What % of users who signed up in January 2024 were active in February?"

WITH cohorts AS (
    SELECT
        user_id,
        DATE_TRUNC('month', signup_date) AS cohort_month
    FROM users
),
activities AS (
    SELECT
        user_id,
        DATE_TRUNC('month', activity_date) AS active_month
    FROM user_events
    GROUP BY 1, 2
),
cohort_activity AS (
    SELECT
        c.cohort_month,
        a.active_month,
        COUNT(DISTINCT c.user_id) AS active_users,
        DATEDIFF('month', c.cohort_month, a.active_month) AS months_since_signup
    FROM cohorts c
    JOIN activities a ON c.user_id = a.user_id
    GROUP BY 1, 2, 4
),
cohort_sizes AS (
    SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
    FROM cohorts
    GROUP BY 1
)
SELECT
    ca.cohort_month,
    ca.months_since_signup,
    ca.active_users,
    cs.cohort_size,
    ROUND(100.0 * ca.active_users / cs.cohort_size, 1) AS retention_pct
FROM cohort_activity ca
JOIN cohort_sizes cs ON ca.cohort_month = cs.cohort_month
ORDER BY ca.cohort_month, ca.months_since_signup;
```


### Gaps and Islands


```
-- Find consecutive date ranges for each user
-- "Show date ranges when each user was continuously active"

WITH user_dates AS (
    SELECT DISTINCT user_id, activity_date
    FROM user_events
),
with_groups AS (
    SELECT
        user_id,
        activity_date,
        -- Subtract row number from date to get "island" group ID
        -- Consecutive dates will have same group_id
        activity_date - INTERVAL '1 day' * ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY activity_date
        ) AS group_id
    FROM user_dates
)
SELECT
    user_id,
    MIN(activity_date) AS streak_start,
    MAX(activity_date) AS streak_end,
    COUNT(*) AS streak_length_days
FROM with_groups
GROUP BY user_id, group_id
ORDER BY user_id, streak_start;
```


## Query Optimization


```
-- EXPLAIN ANALYZE: understand query execution plan
EXPLAIN ANALYZE
SELECT u.*, COUNT(o.order_id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.country = 'US'
GROUP BY u.id;

-- Key things to look for in EXPLAIN output:
-- 1. Sequential Scan vs Index Scan (want index scan on large tables)
-- 2. Nested Loop vs Hash Join vs Merge Join
--    - Nested Loop: good for small inner table or indexed lookups
--    - Hash Join: good for large unsorted tables
--    - Merge Join: good for pre-sorted large tables
-- 3. Actual rows vs Estimated rows (big discrepancy → stale stats → ANALYZE)
-- 4. Cost (relative, not real time): (startup cost..total cost)

-- Common optimization techniques:
-- 1. Add index on WHERE, JOIN ON, ORDER BY columns
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_date ON orders(order_date DESC);

-- 2. Partial index (index only relevant rows)
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- 3. Covering index (index includes all needed columns, no table access)
CREATE INDEX idx_orders_covering ON orders(user_id, order_date, amount);

-- 4. Avoid function on indexed column (prevents index use)
-- WRONG:
WHERE DATE(created_at) = '2024-01-15'  -- function on column
-- RIGHT:
WHERE created_at >= '2024-01-15' AND created_at < '2024-01-16'
```


## Schema Design Questions

### Many-to-Many Relationships


```
-- Users can have many roles; roles can have many users

-- Junction table pattern:
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    email TEXT UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name TEXT UNIQUE NOT NULL  -- 'admin', 'moderator', 'viewer'
);

CREATE TABLE user_roles (
    user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
    role_id INTEGER REFERENCES roles(id) ON DELETE RESTRICT,
    granted_at TIMESTAMPTZ DEFAULT NOW(),
    granted_by BIGINT REFERENCES users(id),
    PRIMARY KEY (user_id, role_id)  -- composite PK prevents duplicates
);

-- Query: users with their roles
SELECT u.email, array_agg(r.name ORDER BY r.name) AS roles
FROM users u
JOIN user_roles ur ON u.id = ur.user_id
JOIN roles r ON ur.role_id = r.id
GROUP BY u.id, u.email;
```


### Self-Referential (Hierarchical) Data


```
-- Organization hierarchy: employees and their managers
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    manager_id INTEGER REFERENCES employees(id),  -- NULL for CEO
    department TEXT
);

-- Recursive CTE to get full org chart under a given manager:
WITH RECURSIVE org_chart AS (
    -- Base case: start with the given manager
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 42  -- starting manager

    UNION ALL

    -- Recursive case: find direct reports
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT id, name, depth
FROM org_chart
ORDER BY depth, name;
```


## Interview Questions by Level

### Junior (must know cold)

- INNER vs LEFT vs RIGHT vs FULL OUTER JOIN — give an example of each

- What's the difference between WHERE and HAVING?

- How do you find duplicates in a table?

- Write a query to get the second highest salary

### Senior (expected)

- Explain how a database index works (B-tree internals)

- When would you use a window function instead of a subquery?

- How do you analyze a slow query? (Walk through EXPLAIN ANALYZE)

- Write a cohort retention query

- What's the difference between UNION and UNION ALL?

### Staff (impressive)

- Explain MVCC (Multi-Version Concurrency Control) in PostgreSQL

- When does a query not use an index even when one exists?

- Design a schema for a multi-tenant SaaS with row-level security

- Write a gaps-and-islands query to find consecutive event streaks

## Quick Reference: Transaction Isolation Levels

| Level | Dirty Read | Non-Repeatable Read | Phantom Read | Use When |
| --- | --- | --- | --- | --- |
| READ UNCOMMITTED | Yes | Yes | Yes | Never (almost) |
| READ COMMITTED | No | Yes | Yes | Default in most DBs |
| REPEATABLE READ | No | No | Yes | Financial reports |
| SERIALIZABLE | No | No | No | Critical transactions |