Advanced SQL & Internals

Window Functions Deep-Dive

A window function computes across a set of rows related to the current row, without collapsing them like GROUP BY does. Every window function uses OVER (...).

Ranking functions

SELECT name, salary,
  ROW_NUMBER() OVER (
    ORDER BY salary DESC) AS rn
FROM employees;

SELECT name, salary,
  RANK() OVER (
    ORDER BY salary DESC) AS rnk
FROM employees;

SELECT name, salary,
  DENSE_RANK() OVER (
    ORDER BY salary DESC) AS drnk
FROM employees;

Value functions

SELECT order_date, revenue,
  LAG(revenue, 1) OVER (
    ORDER BY order_date) AS prev_day
FROM daily_sales;

SELECT order_date, revenue,
  LEAD(revenue, 1) OVER (
    ORDER BY order_date) AS next_day
FROM daily_sales;

SELECT name, salary,
  FIRST_VALUE(name) OVER (
    ORDER BY salary DESC) AS top_earner
FROM employees;

Aggregate window functions

Any aggregate (SUM, AVG, COUNT, MIN, MAX) can run as a window function by adding OVER (...) — rows stay ungrouped.

SELECT department, name, salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg,
  SUM(salary) OVER (PARTITION BY department) AS dept_total
FROM employees;

Frame specifications

PARTITION BY groups rows (like GROUP BY, but no collapsing). ORDER BY inside OVER sets row order. The frame defines exactly which rows around the current row are included.

-- running total: every row up to and including the current one
SELECT order_date, revenue,
  SUM(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM daily_sales;

-- 3-row moving average: current row + 2 before it
SELECT order_date, revenue,
  AVG(revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) AS moving_avg_3d
FROM daily_sales;

CTEs vs Subqueries vs Views vs Materialized Views

-- CTE: readable, named step
WITH high_value AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders GROUP BY customer_id
)
SELECT * FROM high_value WHERE total > 10000;

-- View: saved, reusable definition
CREATE VIEW vw_high_value_customers AS
SELECT customer_id, SUM(amount) AS total
FROM orders GROUP BY customer_id;

-- Materialized View: stores the result, needs refresh
CREATE MATERIALIZED VIEW mv_daily_revenue AS
SELECT order_date, SUM(amount) AS revenue
FROM orders GROUP BY order_date;

REFRESH MATERIALIZED VIEW mv_daily_revenue;

Database Indexing

An index is a separate, sorted lookup structure that lets the engine find rows without scanning the whole table.

Clustered vs Non-Clustered

Composite indexes

An index on multiple columns, e.g. (last_name, first_name). Order matters — it only helps queries that filter on a left-to-right prefix of the columns.

CREATE INDEX idx_name ON employees(last_name, first_name);

-- ✅ uses the index (prefix match)
SELECT * FROM employees WHERE last_name = 'Shah';

-- ❌ does NOT use the index efficiently (skips the prefix)
SELECT * FROM employees WHERE first_name = 'Ana';

Query Optimization & Performance Tuning

Reading an execution plan

EXPLAIN ANALYZE
SELECT customer_id, SUM(amount)
FROM orders
WHERE order_date >= '2026-01-01'
GROUP BY customer_id;

EXPLAIN shows the planned steps and estimated cost; EXPLAIN ANALYZE actually runs the query and shows real timing too. Read it bottom-up / inside-out — the innermost operations run first and feed the ones above.

Common tuning wins

• Avoid SELECT * — name the columns you need. Less I/O, less network, and lets index-only scans work (no need to fetch the full row).
• Filter early — push selective WHERE conditions before joining large tables where possible.
• Join on indexed keys — an unindexed join column forces a scan on that side of the join.
• Watch join order — join the smallest / most selective result first to shrink intermediate row counts.
• Beware functions on indexed columns — WHERE YEAR(order_date) = 2026 can't use an index on order_date; rewrite as a range: WHERE order_date >= '2026-01-01' AND order_date < '2027-01-01'.
• LIMIT + ORDER BY without an index on the sort column still requires a full sort — index the sort key for "top N" queries.

Database Transactions & ACID

A transaction is a group of operations that succeed or fail together.

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;  -- both updates succeed together, or neither does

Production Query: CTEs + Window + Aggregation

A realistic multi-table query — monthly revenue per customer, their rank within each month, and a running total over time.

WITH monthly_customer_revenue AS (
  SELECT
    c.customer_id,
    c.customer_name,
    DATE_TRUNC('month', o.order_date) AS month,
    SUM(oi.quantity * oi.unit_price) AS revenue
  FROM customers c
  JOIN orders o       ON o.customer_id = c.customer_id
  JOIN order_items oi ON oi.order_id   = o.order_id
  GROUP BY c.customer_id, c.customer_name,
           DATE_TRUNC('month', o.order_date)
),
ranked AS (
  SELECT *,
    RANK() OVER (
      PARTITION BY month ORDER BY revenue DESC
    ) AS rank_in_month,
    SUM(revenue) OVER (
      PARTITION BY customer_id ORDER BY month
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total_revenue
  FROM monthly_customer_revenue
)
SELECT customer_name, month, revenue, rank_in_month, running_total_revenue
FROM ranked
WHERE rank_in_month <= 3
ORDER BY month, rank_in_month;

Reads as: build the monthly revenue base in the first CTE → rank and accumulate in the second CTE → filter to top 3 per month in the final SELECT. Note the filter on rank_in_month happens in the outer query, since window function results can't be used in WHERE/HAVING directly.

SQL Interview Tricks & Traps — Cheat Sheet

-- find duplicate rows by a key
SELECT email, COUNT(*)
FROM users GROUP BY email HAVING COUNT(*) > 1;

-- delete duplicates, keep the lowest id
DELETE FROM users
WHERE id NOT IN (
  SELECT MIN(id) FROM users GROUP BY email
);

-- gaps in RANK can skip valid "3rd place" ties
SELECT *, DENSE_RANK() OVER (
  PARTITION BY department ORDER BY salary DESC) AS drnk
FROM employees
QUALIFY drnk <= 3;  -- (Snowflake/BigQuery); else wrap in a subquery

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