SQL Interview Questions: Advanced Level (Q101–Q200)

-- Compare each order to the overall average
SELECT
  order_id,
  total_amount,
  AVG(total_amount) OVER () AS overall_avg,
  total_amount - AVG(total_amount) OVER () AS diff_from_avg
FROM orders;

-- ROWS BETWEEN: physical row offsets
SUM(amount) OVER (
  ORDER BY date
  ROWS BETWEEN 2 PRECEDING AND CURRENT ROW  -- current + 2 previous rows
)

-- RANGE BETWEEN: logical value range
SUM(amount) OVER (
  ORDER BY date
  RANGE BETWEEN INTERVAL '7 days' PRECEDING AND CURRENT ROW
)

-- Unbounded: entire partition
SUM(amount) OVER (
  PARTITION BY customer_id
  ORDER BY date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

WITH daily AS (
  SELECT created_at::DATE AS day, SUM(total_amount) AS revenue
  FROM orders WHERE status = 'delivered'
  GROUP BY 1
),
date_spine AS (
  SELECT generate_series(MIN(day), MAX(day), '1 day') AS day FROM daily
)
SELECT
  ds.day,
  COALESCE(d.revenue, 0) AS revenue,
  AVG(COALESCE(d.revenue, 0)) OVER (
    ORDER BY ds.day
    ROWS BETWEEN 29 PRECEDING AND CURRENT ROW
  ) AS moving_avg_30d
FROM date_spine ds
LEFT JOIN daily d ON ds.day = d.day;

SELECT
  o.order_id,
  o.customer_id,
  o.total_amount,
  SUM(o.total_amount) OVER (PARTITION BY o.customer_id) AS customer_total,
  ROUND(100.0 * o.total_amount /
    NULLIF(SUM(o.total_amount) OVER (PARTITION BY o.customer_id), 0), 2
  ) AS pct_of_customer_total
FROM orders o
WHERE status = 'delivered';

WITH ordered AS (
  SELECT
    customer_id,
    order_id,
    total_amount,
    LAG(total_amount) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_amount,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
  WHERE status = 'delivered'
)
SELECT customer_id, order_id, total_amount, prev_amount
FROM ordered
WHERE rn = 1
  AND total_amount < prev_amount;

-- Find missing order_ids between 1 and MAX
WITH series AS (
  SELECT generate_series(1, (SELECT MAX(order_id) FROM orders)) AS expected_id
)
SELECT expected_id AS missing_order_id
FROM series
WHERE expected_id NOT IN (SELECT order_id FROM orders);

-- Or more efficiently with LAG:
SELECT order_id + 1 AS gap_start,
       next_id - 1  AS gap_end
FROM (
  SELECT order_id,
    LEAD(order_id) OVER (ORDER BY order_id) AS next_id
  FROM orders
) t
WHERE next_id > order_id + 1;

-- Subscriptions table: (customer_id, date, is_active)
-- Find continuous active periods
WITH numbered AS (
  SELECT
    customer_id,
    date,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY date) -
    ROW_NUMBER() OVER (PARTITION BY customer_id, is_active ORDER BY date) AS grp
  FROM subscriptions
  WHERE is_active = TRUE
)
SELECT
  customer_id,
  MIN(date) AS period_start,
  MAX(date) AS period_end,
  COUNT(*) AS active_days
FROM numbered
GROUP BY customer_id, grp
ORDER BY customer_id, period_start;

WITH first_orders AS (
  SELECT customer_id, DATE_TRUNC('month', MIN(created_at)) AS cohort_month
  FROM orders GROUP BY customer_id
),
cohort_activity AS (
  SELECT
    fo.cohort_month,
    DATE_TRUNC('month', o.created_at) AS activity_month,
    COUNT(DISTINCT o.customer_id) AS active_customers
  FROM orders o
  JOIN first_orders fo ON o.customer_id = fo.customer_id
  GROUP BY 1, 2
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS size FROM first_orders GROUP BY 1
)
SELECT
  ca.cohort_month,
  cs.size AS cohort_size,
  EXTRACT(MONTH FROM AGE(ca.activity_month, ca.cohort_month)) AS months_later,
  ca.active_customers,
  ROUND(100.0 * ca.active_customers / cs.size, 1) AS retention_pct
FROM cohort_activity ca
JOIN cohort_sizes cs ON ca.cohort_month = cs.cohort_month
ORDER BY 1, 3;

-- components(parent_product_id, child_product_id, quantity)
WITH RECURSIVE bom AS (
  SELECT child_product_id, quantity, 1 AS level, child_product_id::TEXT AS path
  FROM components WHERE parent_product_id = 1  -- root product

  UNION ALL

  SELECT c.child_product_id, c.quantity * bom.quantity, bom.level + 1,
    bom.path || ' > ' || c.child_product_id
  FROM components c
  JOIN bom ON c.parent_product_id = bom.child_product_id
)
SELECT bom.child_product_id, p.name, bom.quantity AS total_needed, level, path
FROM bom
JOIN products p ON bom.child_product_id = p.product_id
ORDER BY path;

WITH RECURSIVE paths AS (
  SELECT from_node, to_node, ARRAY[from_node, to_node] AS path, FALSE AS cycle
  FROM edges WHERE from_node = 'A'

  UNION ALL

  SELECT e.from_node, e.to_node, p.path || e.to_node,
    e.to_node = ANY(p.path)  -- cycle detection
  FROM edges e
  JOIN paths p ON e.from_node = p.to_node
  WHERE NOT p.cycle
)
SELECT path FROM paths WHERE NOT cycle;

-- PostgreSQL: disable hash join for testing
SET enable_hashjoin = OFF;
EXPLAIN SELECT ...;

-- PostgreSQL: check last analyze time
SELECT schemaname, tablename, last_vacuum, last_analyze, last_autoanalyze, n_live_tup
FROM pg_stat_user_tables
WHERE last_analyze < NOW() - INTERVAL '7 days'
   OR last_analyze IS NULL
ORDER BY n_live_tup DESC;

-- Force statistics update
ANALYZE orders;
ANALYZE customers;

-- Original: slow
SELECT * FROM orders WHERE EXTRACT(YEAR FROM created_at) = 2025;

-- Optimized: range instead of function (uses index)
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

-- PostgreSQL: control parallelism
SET max_parallel_workers_per_gather = 4;
EXPLAIN ANALYZE SELECT SUM(total_amount) FROM orders;
-- Look for "Parallel Seq Scan" or "Parallel Hash Join"

WITH sessions AS (
  SELECT
    session_id,
    customer_id,
    MIN(viewed_at) AS session_start,
    MAX(viewed_at) AS session_end,
    COUNT(*) AS page_count
  FROM page_views
  GROUP BY session_id, customer_id
)
SELECT
  customer_id,
  AVG(EXTRACT(EPOCH FROM (session_end - session_start)) / 60) AS avg_session_minutes,
  AVG(page_count) AS avg_pages_per_session,
  COUNT(DISTINCT session_id) AS total_sessions
FROM sessions
GROUP BY customer_id
ORDER BY avg_session_minutes DESC;

WITH daily_orders AS (
  SELECT DISTINCT customer_id, created_at::DATE AS order_date
  FROM orders
),
grouped AS (
  SELECT customer_id, order_date,
    order_date - ROW_NUMBER() OVER (
      PARTITION BY customer_id ORDER BY order_date
    )::INTEGER AS grp
  FROM daily_orders
),
streaks AS (
  SELECT customer_id, COUNT(*) AS streak_length
  FROM grouped
  GROUP BY customer_id, grp
)
SELECT customer_id, MAX(streak_length) AS longest_streak
FROM streaks
GROUP BY customer_id
ORDER BY longest_streak DESC;

SELECT
  c.name AS category,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY oi.line_total) AS median_line_total,
  AVG(oi.line_total) AS mean_line_total,
  COUNT(*) AS item_count
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
JOIN categories c ON p.category_id = c.category_id
GROUP BY c.category_id, c.name
ORDER BY median_line_total DESC;

WITH monthly_orders AS (
  SELECT DISTINCT
    customer_id,
    DATE_TRUNC('month', created_at) AS month
  FROM orders
),
with_lag AS (
  SELECT
    customer_id,
    month,
    LAG(month, 1) OVER (PARTITION BY customer_id ORDER BY month) AS prev1,
    LAG(month, 2) OVER (PARTITION BY customer_id ORDER BY month) AS prev2
  FROM monthly_orders
)
SELECT DISTINCT customer_id, month AS lapsed_month
FROM with_lag
WHERE prev1 = month - INTERVAL '1 month'
  AND prev2 = month - INTERVAL '2 months'
  AND month + INTERVAL '1 month' NOT IN (
    SELECT month FROM monthly_orders mo WHERE mo.customer_id = with_lag.customer_id
  );

-- SCD Type 2 table: keep history with effective dates
CREATE TABLE customer_history (
  history_id    SERIAL PRIMARY KEY,
  customer_id   INT NOT NULL,
  email         VARCHAR(255),
  tier          VARCHAR(20),
  effective_from TIMESTAMP NOT NULL,
  effective_to   TIMESTAMP,           -- NULL = current record
  is_current     BOOLEAN DEFAULT TRUE
);

-- When a customer changes tier, expire the old record and insert new one
CREATE OR REPLACE PROCEDURE update_customer_scd2(
  p_customer_id INT, p_new_tier VARCHAR
)
LANGUAGE plpgsql AS $$
BEGIN
  -- Expire current record
  UPDATE customer_history
  SET effective_to = NOW(), is_current = FALSE
  WHERE customer_id = p_customer_id AND is_current = TRUE;

  -- Insert new current record
  INSERT INTO customer_history (customer_id, email, tier, effective_from)
  SELECT customer_id, email, p_new_tier, NOW()
  FROM customers WHERE customer_id = p_customer_id;
END;
$$;

-- Query: what was a customer's tier on a specific date?
SELECT tier FROM customer_history
WHERE customer_id = 42
  AND effective_from <= '2025-06-01'
  AND (effective_to > '2025-06-01' OR effective_to IS NULL);

-- Transaction 1 reads: SELECT * FROM orders WHERE amount > 100 → 5 rows
-- Transaction 2 inserts: INSERT INTO orders (amount) VALUES (500)
-- Transaction 1 re-reads: SELECT * FROM orders WHERE amount > 100 → 6 rows (phantom!)

-- FOR UPDATE: locks rows exclusively. Other transactions can't read FOR UPDATE or modify.
SELECT * FROM orders WHERE order_id = 42 FOR UPDATE;

-- FOR SHARE: locks rows for reading. Others can read but not modify.
SELECT * FROM orders WHERE order_id = 42 FOR SHARE;

-- SKIP LOCKED: skip rows already locked (useful for job queues)
SELECT * FROM jobs WHERE status = 'pending' LIMIT 10 FOR UPDATE SKIP LOCKED;

-- Using SELECT FOR UPDATE
BEGIN;
SELECT count FROM page_counters WHERE page_id = 1 FOR UPDATE;
UPDATE page_counters SET count = count + 1 WHERE page_id = 1;
COMMIT;

-- Better: use atomic UPDATE + RETURNING
UPDATE page_counters
SET count = count + 1
WHERE page_id = 1
RETURNING count;
-- This is atomic — no explicit lock needed

-- Students and courses — a student can take many courses, a course has many students
CREATE TABLE students (student_id SERIAL PRIMARY KEY, name VARCHAR(100));
CREATE TABLE courses (course_id SERIAL PRIMARY KEY, name VARCHAR(100));

-- Junction table
CREATE TABLE enrollments (
  student_id INT NOT NULL REFERENCES students(student_id),
  course_id  INT NOT NULL REFERENCES courses(course_id),
  enrolled_at TIMESTAMP DEFAULT NOW(),
  grade      CHAR(2),
  PRIMARY KEY (student_id, course_id)  -- composite PK prevents duplicates
);

-- category_path: 'Electronics/Laptops/Gaming'
WHERE category_path LIKE 'Electronics/%'  -- fast for simple lookups

CREATE TABLE attribute_types (
  type_id  SERIAL PRIMARY KEY,
  name     VARCHAR(50)  -- 'Size', 'Color', 'Material'
);

CREATE TABLE attribute_values (
  value_id SERIAL PRIMARY KEY,
  type_id  INT REFERENCES attribute_types(type_id),
  value    VARCHAR(100) -- 'Red', 'XL', 'Cotton'
);

CREATE TABLE product_variants (
  variant_id  SERIAL PRIMARY KEY,
  product_id  INT REFERENCES products(product_id),
  sku         VARCHAR(100) UNIQUE,
  price_adj   DECIMAL(10,2) DEFAULT 0  -- price adjustment from base
);

CREATE TABLE variant_attributes (
  variant_id INT REFERENCES product_variants(variant_id),
  value_id   INT REFERENCES attribute_values(value_id),
  PRIMARY KEY (variant_id, value_id)
);

CREATE TABLE orders_audit (
  audit_id    SERIAL PRIMARY KEY,
  operation   CHAR(1),              -- 'I', 'U', 'D'
  changed_at  TIMESTAMP DEFAULT NOW(),
  changed_by  VARCHAR(100) DEFAULT current_user,
  order_id    INT,
  old_status  VARCHAR(20),
  new_status  VARCHAR(20),
  old_amount  DECIMAL(10,2),
  new_amount  DECIMAL(10,2)
);

CREATE OR REPLACE FUNCTION audit_orders()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO orders_audit (operation, order_id, old_status, new_status, old_amount, new_amount)
  VALUES (
    TG_OP::CHAR(1),
    COALESCE(NEW.order_id, OLD.order_id),
    OLD.status, NEW.status,
    OLD.total_amount, NEW.total_amount
  );
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_orders();

-- Monthly sales in wide format: (year, jan, feb, mar, ...)
-- Unpivot to: (year, month, sales)
SELECT year, month, sales
FROM monthly_sales
CROSS JOIN LATERAL (VALUES
  ('January',  jan_sales),
  ('February', feb_sales),
  ('March',    mar_sales),
  ('April',    apr_sales)
) AS unpivoted(month, sales);

-- PostgreSQL: similarity (pg_trgm extension)
CREATE EXTENSION pg_trgm;
SELECT name, similarity(name, 'Jon Smith') AS sim
FROM customers
WHERE similarity(name, 'Jon Smith') > 0.4
ORDER BY sim DESC;

-- Levenshtein distance (fuzzystrmatch extension)
CREATE EXTENSION fuzzystrmatch;
SELECT name, levenshtein(name, 'John Smith') AS distance
FROM customers
ORDER BY distance LIMIT 5;

-- SLOWER: counts all matching rows
IF (SELECT COUNT(*) FROM orders WHERE customer_id = 42) > 0 THEN ...

-- FASTER: stops at the first match
IF EXISTS (SELECT 1 FROM orders WHERE customer_id = 42) THEN ...

-- Declarative partitioning (PostgreSQL 10+)
CREATE TABLE orders (
  order_id    INT,
  created_at  TIMESTAMP NOT NULL,
  total_amount DECIMAL(10,2)
) PARTITION BY RANGE (created_at);

-- Create yearly partitions
CREATE TABLE orders_2024 PARTITION OF orders
  FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

CREATE TABLE orders_2025 PARTITION OF orders
  FOR VALUES FROM ('2025-01-01') TO ('2026-01-01');

-- Queries automatically use only the relevant partition
EXPLAIN SELECT * FROM orders WHERE created_at >= '2025-01-01';
-- Shows only "orders_2025" being scanned

-- UUID native type (PostgreSQL, MySQL 8.0+)
CREATE TABLE events (
  event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  ...
);

-- Advantages of native UUID:
-- 4 bytes less storage than CHAR(36) (16 bytes vs 36)
-- Proper type checking
-- Indexable without text comparison overhead

-- PostgreSQL
CREATE TABLE orders_backup AS SELECT * FROM orders WHERE FALSE;
-- or
CREATE TABLE orders_backup (LIKE orders INCLUDING ALL);  -- includes indexes and constraints

-- SQL Server
SELECT * INTO orders_backup FROM orders WHERE 1=0;

-- These are identical:
WHERE salary IN (SELECT salary FROM top_earners)
WHERE salary = ANY (SELECT salary FROM top_earners)

-- ANY with other operators:
WHERE salary > ANY (SELECT salary FROM managers)  -- above any manager's salary
WHERE salary > ALL (SELECT salary FROM managers)  -- above ALL managers' salaries

SELECT p1.name, p2.name, COUNT(*) AS co_occurrences
FROM order_items oi1
JOIN order_items oi2 ON oi1.order_id = oi2.order_id AND oi1.product_id < oi2.product_id
JOIN products p1 ON oi1.product_id = p1.product_id
JOIN products p2 ON oi2.product_id = p2.product_id
GROUP BY p1.product_id, p1.name, p2.product_id, p2.name
HAVING COUNT(*) >= 10
ORDER BY co_occurrences DESC;

SELECT week, revenue,
  LAG(revenue) OVER (ORDER BY week) AS prev_week,
  ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY week)) /
    NULLIF(LAG(revenue) OVER (ORDER BY week), 0), 1) AS wow_growth
FROM weekly_revenue;

WHERE created_at >= DATE_TRUNC('week', CURRENT_DATE - 7)
  AND created_at <  DATE_TRUNC('week', CURRENT_DATE);

WITH ranked AS (
  SELECT p.category_id, p.product_id, p.name,
    SUM(oi.line_total) AS revenue,
    RANK() OVER (PARTITION BY p.category_id ORDER BY SUM(oi.line_total) DESC) AS rnk
  FROM products p JOIN order_items oi ON p.product_id = oi.product_id
  GROUP BY p.category_id, p.product_id, p.name
)
SELECT category_id, product_id, name, revenue FROM ranked WHERE rnk = 1;

-- Count delivered and cancelled orders in one scan
SELECT
  COUNT(*) FILTER (WHERE status = 'delivered') AS delivered,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled
FROM orders;

SELECT customer_id, COUNT(*) AS orders_last_hour
FROM orders
WHERE created_at >= NOW() - INTERVAL '1 hour'
GROUP BY customer_id
HAVING COUNT(*) > 5;

SELECT * FROM products ORDER BY product_id LIMIT 1 OFFSET N-1;
-- Or with ROW_NUMBER:
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY product_id) AS rn FROM products) t WHERE rn = N;

-- JSON column: tags JSONB = '["sql","python","data"]'
SELECT * FROM articles WHERE tags @> '["sql"]';          -- contains
SELECT * FROM articles WHERE tags ? 'python';             -- has key/element
SELECT jsonb_array_elements_text(tags) FROM articles;     -- unnest array

-- No two events for the same room should overlap in time (requires btree_gist)
CREATE TABLE bookings (
  room_id INT, period TSRANGE,
  EXCLUDE USING GIST (room_id WITH =, period WITH &&)
);

SELECT title, ts_rank(to_tsvector('english', content), query) AS rank
FROM articles,
  to_tsquery('english', 'machine & learning') AS query
WHERE to_tsvector('english', content) @@ query
ORDER BY rank DESC;

-- UNSAFE
query = "SELECT * FROM users WHERE email = '" + user_input + "'"

-- SAFE (parameterized)
query = "SELECT * FROM users WHERE email = $1"
execute(query, [user_input])

-- PostgreSQL
SELECT
  pg_size_pretty(pg_total_relation_size('orders')) AS total_size,
  pg_size_pretty(pg_relation_size('orders'))        AS table_size,
  pg_size_pretty(pg_indexes_size('orders'))          AS indexes_size;

-- Find long-running queries
SELECT pid, query, NOW() - query_start AS duration, state
FROM pg_stat_activity
WHERE state = 'active' AND NOW() - query_start > INTERVAL '1 minute'
ORDER BY duration DESC;

-- Kill it
SELECT pg_cancel_backend(pid);     -- sends SIGINT (graceful)
SELECT pg_terminate_backend(pid);  -- sends SIGTERM (force)

pg_dump -Fc -d mydb -f backup.dump    # custom format
pg_restore -d mydb backup.dump         # restore

ALTER TABLE old_name RENAME TO new_name;

INSERT INTO orders (customer_id, total_amount)
VALUES (42, 99.99)
RETURNING order_id, created_at;

CREATE USER analyst WITH PASSWORD 'password';
GRANT CONNECT ON DATABASE shopdb TO analyst;
GRANT USAGE ON SCHEMA public TO analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;
-- For future tables:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO analyst;

ALTER TABLE orders ENABLE ROW LEVEL SECURITY;
CREATE POLICY customer_own_orders ON orders
  USING (customer_id = current_setting('app.current_customer_id')::INT);
-- Each customer now only sees their own orders

-- Generate 10,000 test orders
INSERT INTO orders (customer_id, total_amount, created_at, status)
SELECT
  (RANDOM() * 999 + 1)::INT,
  ROUND((RANDOM() * 1000)::NUMERIC, 2),
  NOW() - (RANDOM() * INTERVAL '365 days'),
  (ARRAY['pending','delivered','cancelled'])[1 + (RANDOM() * 2)::INT]
FROM generate_series(1, 10000);

-- PostgreSQL: find most scanned indexes
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- Find unused indexes (idx_scan = 0)
SELECT indexrelname FROM pg_stat_user_indexes WHERE idx_scan = 0;

CREATE TABLE order_items (
  quantity   INT, unit_price DECIMAL(10,2),
  line_total DECIMAL(10,2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

CREATE EXTENSION postgres_fdw;
CREATE SERVER remote_db FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host 'remote', dbname 'db');
CREATE FOREIGN TABLE remote_orders (...) SERVER remote_db;
SELECT * FROM remote_orders;

SET work_mem = '256MB';  -- for this session only
SELECT ... ORDER BY ... LIMIT ...;