40 SQL Interview Questions and Answers (2026)

-- DDL
CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL);
ALTER TABLE users ADD COLUMN created_at TIMESTAMPTZ DEFAULT NOW();

-- DML
INSERT INTO users (email) VALUES ('alice@example.com');
SELECT * FROM users WHERE email LIKE '%@example.com';
UPDATE users SET email = 'alice@new.com' WHERE id = 1;
DELETE FROM users WHERE id = 1;

-- DCL
GRANT SELECT ON users TO readonly_role;
REVOKE INSERT ON users FROM readonly_role;

-- TCL
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT; -- or ROLLBACK if something went wrong

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,        -- one PK, not nullable, clustered
    email       VARCHAR(255) NOT NULL UNIQUE, -- enforces uniqueness, allows index lookup
    username    VARCHAR(100) UNIQUE,       -- allows ONE NULL in most databases
    phone       VARCHAR(20)  UNIQUE        -- multiple UNIQUE columns are fine
);

SELECT NULL = NULL;     -- NULL  (not TRUE)
SELECT NULL != NULL;    -- NULL  (not TRUE)
SELECT NULL = 0;        -- NULL
SELECT NULL IS NULL;    -- TRUE  (use IS NULL / IS NOT NULL)
SELECT NULL IS NOT NULL;-- FALSE

-- Common trap: WHERE column != 'value' does NOT return rows where column IS NULL
SELECT * FROM users WHERE status != 'inactive';
-- This MISSES rows where status IS NULL

-- Correct version:
SELECT * FROM users WHERE status != 'inactive' OR status IS NULL;

SELECT
    COUNT(*)       AS total_rows,        -- includes NULL phone rows
    COUNT(phone)   AS rows_with_phone,   -- excludes NULL phone rows
    AVG(salary)    AS avg_salary         -- averages only non-NULL salaries
FROM employees;

DELETE FROM orders WHERE status = 'cancelled';  -- removes matching rows only
DELETE FROM orders;  -- removes ALL rows but table structure remains

TRUNCATE TABLE orders;  -- empties the table, keeps structure and indexes
-- Also resets AUTO_INCREMENT counter in MySQL (DELETE does not)

DROP TABLE orders;           -- removes table entirely
DROP TABLE IF EXISTS orders; -- safer version, no error if table doesn't exist

-- WHERE: filters individual rows BEFORE GROUP BY
SELECT department_id, COUNT(*) AS headcount
FROM employees
WHERE salary > 50000          -- filters individual employees first
GROUP BY department_id;

-- HAVING: filters groups AFTER GROUP BY and aggregation
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;         -- filters departments with more than 10 people

-- Both together: WHERE and HAVING on the same query
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE hire_date >= '2020-01-01'   -- only consider employees hired after 2020
GROUP BY department_id
HAVING AVG(salary) > 70000;      -- only return departments with high avg salary

-- UNION: removes duplicates
SELECT email FROM customers
UNION
SELECT email FROM newsletter_subscribers;
-- If alice@example.com is in both tables, she appears once

-- UNION ALL: keeps duplicates, faster
SELECT email FROM customers
UNION ALL
SELECT email FROM newsletter_subscribers;
-- alice@example.com appears twice if she is in both tables

-- Practical example: combine data from multiple tables for a report
SELECT 'customer' AS source, name, created_at FROM customers
UNION ALL
SELECT 'vendor'   AS source, name, created_at FROM vendors
ORDER BY created_at DESC;

CREATE TABLE orders (
    id          SERIAL PRIMARY KEY,
    user_id     INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    status      VARCHAR(20) NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending','processing','shipped','cancelled')),
    total       NUMERIC(10,2) NOT NULL CHECK (total >= 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

SELECT
    department_id,
    AVG(salary) AS avg_sal     -- step 5: computed here
FROM employees                 -- step 1: data source
WHERE hire_date > '2020-01-01' -- step 2: filter rows
GROUP BY department_id         -- step 3: group
HAVING AVG(salary) > 60000     -- step 4: filter groups
ORDER BY avg_sal DESC          -- step 7: can use alias from step 5
LIMIT 5;                       -- step 8: top 5 results

SELECT e.name, d.name AS dept
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;
-- Employees with no department and departments with no employees are excluded

SELECT e.name, d.name AS dept
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;
-- Employees with no department appear with dept = NULL

-- PostgreSQL / SQL Server
SELECT e.name, d.name AS dept
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

SELECT colors.name, sizes.name
FROM colors CROSS JOIN sizes;
-- 3 colors x 4 sizes = 12 combinations

-- Find each employee and their manager's name
SELECT
    e.name  AS employee,
    m.name  AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id
ORDER BY m.name, e.name;
-- LEFT JOIN ensures employees with no manager (the CEO) still appear

-- Method 1: LEFT JOIN with IS NULL check (most readable, usually fastest)
SELECT c.id, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;
-- Customers who have never placed an order

-- Method 2: NOT EXISTS (clear intent, often same performance as LEFT JOIN)
SELECT c.id, c.email
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

-- Method 3: NOT IN (avoid with NULLs -- if subquery returns any NULL, the
-- entire NOT IN returns no rows)
SELECT id, email
FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

-- Find employees who earn more than the average salary in their own department
SELECT e.name, e.salary, e.department_id
FROM employees e
WHERE e.salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = e.department_id  -- references outer query's e
);
-- The inner query runs once for every employee row

-- Find employees who earn more than the company-wide average
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
-- Inner query runs once, result is reused

-- Same result as the correlated subquery above, but written with a window function
SELECT name, salary, department_id
FROM (
    SELECT name, salary, department_id,
           AVG(salary) OVER (PARTITION BY department_id) AS dept_avg
    FROM employees
) sub
WHERE salary > dept_avg;

-- Schema with different ON DELETE behaviors
CREATE TABLE departments (
    id   INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    id            INT PRIMARY KEY,
    department_id INT REFERENCES departments(id) ON DELETE SET NULL,
    -- If department is deleted, employees become unassigned (NULL dept)
    manager_id    INT REFERENCES employees(id) ON DELETE RESTRICT
    -- Cannot delete a manager who still has direct reports
);

CREATE TABLE orders (
    id          INT PRIMARY KEY,
    customer_id INT NOT NULL REFERENCES customers(id) ON DELETE CASCADE
    -- If customer is deleted, all their orders are deleted automatically
);

users = db.query("SELECT * FROM users LIMIT 100")  # 1 query
for user in users:
    order = db.query("SELECT * FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 1", user.id)
    # runs 100 times -- 100 queries
# Total: 101 queries

SELECT DISTINCT ON (u.id)
    u.id, u.name, u.email,
    o.id AS last_order_id, o.total, o.created_at
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.id, o.created_at DESC
LIMIT 100;
-- PostgreSQL DISTINCT ON keeps first row per group after ORDER BY

SELECT u.id, u.name, u.email, o.id AS last_order_id, o.total
FROM users u
LEFT JOIN (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders
) o ON u.id = o.user_id AND o.rn = 1
LIMIT 100;

-- Subquery version: find customers who placed orders above average value
SELECT name FROM customers
WHERE id IN (
    SELECT customer_id FROM orders WHERE total > (SELECT AVG(total) FROM orders)
);

-- JOIN version (often faster, no subquery overhead)
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.total > (SELECT AVG(total) FROM orders);

-- Create a B-Tree index (default type in MySQL and PostgreSQL)
CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_created ON orders(created_at);

-- Partial index (PostgreSQL) -- index only the rows you actually query
CREATE INDEX idx_orders_pending ON orders(created_at)
WHERE status = 'pending';
-- Much smaller index, only covers pending orders

-- Check which indexes exist on a table (PostgreSQL)
SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'orders';

-- MySQL
SHOW INDEXES FROM orders;

-- MySQL InnoDB: PK is always clustered
-- Secondary indexes store the PK value as the row pointer
CREATE TABLE products (
    id       INT AUTO_INCREMENT PRIMARY KEY,  -- clustered
    sku      VARCHAR(50) NOT NULL,
    name     VARCHAR(200) NOT NULL,
    price    DECIMAL(10,2),
    INDEX idx_sku (sku)                       -- non-clustered, points to id
);

-- PostgreSQL: CLUSTER command physically reorders table data by index
-- (one-time operation, not maintained automatically)
CLUSTER products USING idx_sku;

-- Composite index on (department_id, hire_date, salary)
CREATE INDEX idx_emp_dept_hire_salary
ON employees(department_id, hire_date, salary);

-- CAN use the index:
-- WHERE department_id = 5                              (leftmost column)
-- WHERE department_id = 5 AND hire_date > '2020-01-01' (leftmost two)
-- WHERE department_id = 5 AND hire_date = '2021-06-01' AND salary > 60000 (all three)

-- CANNOT fully use the index:
-- WHERE hire_date > '2020-01-01'                       (skips department_id)
-- WHERE salary > 60000                                 (skips both leading columns)

-- Query: find email and created_at for all active users
SELECT email, created_at FROM users WHERE status = 'active';

-- Without covering index: index lookup on status, then heap fetch for email + created_at
CREATE INDEX idx_status ON users(status);

-- Covering index: includes all columns needed by the SELECT
CREATE INDEX idx_status_covering ON users(status, email, created_at);
-- Now the query can be answered entirely from the index (no heap fetch)

-- PostgreSQL INCLUDE syntax (cleaner -- included columns are not part of the key)
CREATE INDEX idx_status_include ON users(status) INCLUDE (email, created_at);
-- MySQL does not support INCLUDE -- add columns to the key instead

-- PostgreSQL
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- Shows: estimated cost, rows, join type, index usage

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.name
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'shipped'
ORDER BY o.created_at DESC
LIMIT 20;
-- Shows: actual time, actual rows, loops, buffer cache hits/misses

-- MySQL
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
EXPLAIN FORMAT=JSON SELECT * FROM orders WHERE user_id = 42;

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
-- If the second UPDATE fails, the first is rolled back automatically
COMMIT;

-- PostgreSQL
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
BEGIN;
SELECT * FROM accounts WHERE user_id = 42;  -- first read
-- another transaction commits a change to this user's account here
SELECT * FROM accounts WHERE user_id = 42;  -- same result in REPEATABLE READ
COMMIT;

-- MySQL
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- ...
COMMIT;

Transaction A: locks row 1, then tries to lock row 2
Transaction B: locks row 2, then tries to lock row 1
-- Both wait forever

-- Consistent lock ordering pattern
-- Transaction A and B both acquire the user lock before the account lock
BEGIN;
SELECT * FROM users WHERE id = 1 FOR UPDATE;        -- acquire user lock first
SELECT * FROM accounts WHERE user_id = 1 FOR UPDATE; -- then account lock
-- do work
COMMIT;

-- PostgreSQL/MySQL: lock the row for update immediately on read
BEGIN;
SELECT * FROM inventory WHERE product_id = 99 FOR UPDATE; -- row is locked
-- check stock, decide to proceed
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 99;
COMMIT;

-- Read:
SELECT id, quantity, version FROM inventory WHERE product_id = 99;
-- Returns: id=99, quantity=10, version=5

-- Update (check version has not changed):
UPDATE inventory
SET quantity = quantity - 1, version = version + 1
WHERE product_id = 99 AND version = 5;  -- fails if another transaction changed it
-- Check affected rows: if 0 rows updated, conflict detected, retry

BEGIN;

INSERT INTO orders (user_id, total) VALUES (1, 99.00);  -- step 1

SAVEPOINT after_order;

INSERT INTO order_items (order_id, product_id, qty) VALUES (1001, 5, 2);  -- step 2

-- Something goes wrong with step 2
ROLLBACK TO SAVEPOINT after_order;  -- undo step 2, keep step 1

-- Try a different item
INSERT INTO order_items (order_id, product_id, qty) VALUES (1001, 7, 1);

COMMIT;  -- commits step 1 and the corrected step 2

-- GROUP BY collapses rows: one row per department
SELECT department_id, AVG(salary) AS dept_avg
FROM employees
GROUP BY department_id;

-- Window function: keeps every row, adds the dept average alongside
SELECT
    name,
    salary,
    department_id,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg,
    salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_avg
FROM employees;
-- 200 employee rows in, 200 rows out, with per-department averages on every row

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

SELECT name, department_id, salary
FROM (
    SELECT
        name, department_id, salary,
        DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dr
    FROM employees
) ranked
WHERE dr <= 3;

-- Running total of sales by date
SELECT
    sale_date,
    daily_total,
    SUM(daily_total) OVER (ORDER BY sale_date
                           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS running_total
FROM daily_sales;

-- Moving 7-day average
SELECT
    sale_date,
    daily_total,
    AVG(daily_total) OVER (ORDER BY sale_date
                           ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
        AS moving_avg_7d
FROM daily_sales;

-- Cumulative percentage of total
SELECT
    product_id,
    revenue,
    SUM(revenue) OVER (ORDER BY revenue DESC) AS cumulative_revenue,
    ROUND(
        100.0 * SUM(revenue) OVER (ORDER BY revenue DESC)
              / SUM(revenue) OVER (),
        2
    ) AS cumulative_pct
FROM product_revenue
ORDER BY revenue DESC;

-- Month-over-month revenue change
SELECT
    month,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY month)  AS prev_month_revenue,
    revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change,
    ROUND(
        100.0 * (revenue - LAG(revenue, 1) OVER (ORDER BY month))
              / LAG(revenue, 1) OVER (ORDER BY month),
        2
    ) AS pct_change
FROM monthly_revenue;

-- Find orders placed within 24 hours of the previous order per customer
SELECT
    customer_id,
    order_id,
    created_at,
    LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at) AS prev_order_at,
    created_at - LAG(created_at) OVER (PARTITION BY customer_id ORDER BY created_at)
        AS time_since_last_order
FROM orders;

-- Subquery version (deeply nested, harder to read)
SELECT *
FROM (
    SELECT department_id, AVG(salary) AS dept_avg
    FROM employees
    GROUP BY department_id
) dept_avgs
JOIN departments d ON dept_avgs.department_id = d.id
WHERE dept_avgs.dept_avg > 70000;

-- CTE version (flat, readable, same logic)
WITH dept_averages AS (
    SELECT department_id, AVG(salary) AS dept_avg
    FROM employees
    GROUP BY department_id
)
SELECT d.name, da.dept_avg
FROM dept_averages da
JOIN departments d ON da.department_id = d.id
WHERE da.dept_avg > 70000;

-- Multiple CTEs chained together
WITH
active_users AS (
    SELECT id, email FROM users WHERE status = 'active'
),
recent_orders AS (
    SELECT user_id, COUNT(*) AS order_count
    FROM orders
    WHERE created_at > NOW() - INTERVAL '30 days'
    GROUP BY user_id
)
SELECT u.email, o.order_count
FROM active_users u
JOIN recent_orders o ON u.id = o.user_id
ORDER BY o.order_count DESC;

-- Employee hierarchy: find all reports under a given manager
WITH RECURSIVE org_chart AS (
    -- Anchor: start with the top-level manager
    SELECT id, name, manager_id, 0 AS depth
    FROM employees
    WHERE id = 1  -- CEO or starting manager

    UNION ALL

    -- Recursive: join employees who report to the previous level
    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,
       REPEAT('  ', depth) || name AS indented_name  -- visual hierarchy
FROM org_chart
ORDER BY depth, name;

-- Category tree: find all subcategories
WITH RECURSIVE category_tree AS (
    SELECT id, name, parent_id, name AS path
    FROM categories
    WHERE parent_id IS NULL  -- root categories

    UNION ALL

    SELECT c.id, c.name, c.parent_id,
           ct.path || ' > ' || c.name
    FROM categories c
    JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, path FROM category_tree ORDER BY path;

-- PostgreSQL function: returns a scalar value
CREATE OR REPLACE FUNCTION get_dept_avg_salary(dept_id INT)
RETURNS NUMERIC AS $$
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = dept_id;
$$ LANGUAGE SQL;

-- Use it in a query like any other expression
SELECT name, salary, get_dept_avg_salary(department_id) AS dept_avg
FROM employees;

-- PostgreSQL stored procedure (Postgres 11+)
CREATE OR REPLACE PROCEDURE archive_old_orders(cutoff_date DATE)
LANGUAGE plpgsql AS $$
BEGIN
    INSERT INTO orders_archive SELECT * FROM orders WHERE created_at < cutoff_date;
    DELETE FROM orders WHERE created_at < cutoff_date;
    COMMIT;  -- stored procedures can commit
END;
$$;

CALL archive_old_orders('2024-01-01');

-- PostgreSQL: create a materialized view for a heavy analytics query
CREATE MATERIALIZED VIEW monthly_revenue_summary AS
SELECT
    DATE_TRUNC('month', o.created_at) AS month,
    p.category,
    SUM(oi.quantity * oi.unit_price)  AS total_revenue,
    COUNT(DISTINCT o.id)              AS order_count
FROM orders o
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
GROUP BY 1, 2;

-- Create an index on the materialized view for fast lookups
CREATE INDEX ON monthly_revenue_summary(month, category);

-- Refresh when data changes (full refresh, blocks reads while running)
REFRESH MATERIALIZED VIEW monthly_revenue_summary;

-- Concurrent refresh: does not block readers (requires a UNIQUE index)
CREATE UNIQUE INDEX ON monthly_revenue_summary(month, category);
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_revenue_summary;

-- PostgreSQL: ON CONFLICT DO UPDATE
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (42, 1, NOW())
ON CONFLICT (user_id) DO UPDATE
SET
    login_count = user_stats.login_count + 1,
    last_login  = EXCLUDED.last_login;
-- EXCLUDED refers to the row that failed to insert

-- PostgreSQL: ignore duplicate (do nothing)
INSERT INTO tags (name)
VALUES ('javascript')
ON CONFLICT (name) DO NOTHING;

-- MySQL: ON DUPLICATE KEY UPDATE
INSERT INTO user_stats (user_id, login_count, last_login)
VALUES (42, 1, NOW())
ON DUPLICATE KEY UPDATE
    login_count = login_count + 1,
    last_login  = VALUES(last_login);
-- VALUES(col) refers to the value that was being inserted

-- MySQL: REPLACE INTO (deletes + re-inserts, loses auto-increment continuity)
REPLACE INTO user_stats (user_id, login_count, last_login)
VALUES (42, 1, NOW());
-- Avoid REPLACE INTO: it deletes the row and re-inserts it, losing the original id

-- Store and query JSONB
CREATE TABLE events (
    id         SERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    payload    JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

INSERT INTO events (event_type, payload) VALUES
    ('purchase', '{"user_id": 42, "items": [{"sku": "ABC", "qty": 2}], "total": 49.99}'),
    ('login',    '{"user_id": 42, "ip": "192.168.1.1", "device": "mobile"}');

-- Extract a field
SELECT payload->>'user_id' AS user_id,      -- text
       payload->'total'    AS total          -- jsonb
FROM events WHERE event_type = 'purchase';

-- Filter by JSON value
SELECT * FROM events WHERE payload->>'user_id' = '42';
SELECT * FROM events WHERE payload @> '{"event_type": "purchase"}'; -- containment

-- Index JSONB for fast filtering
CREATE INDEX idx_events_payload ON events USING GIN (payload);
-- Supports @>, ?, ?|, ?& operators efficiently

-- Index a specific JSONB path
CREATE INDEX idx_events_user_id ON events((payload->>'user_id'));

-- Method 1: Window function (cleanest, handles ties correctly)
SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
    FROM employees
) ranked
WHERE dr = 2
LIMIT 1;

-- Method 2: Subquery with MAX
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Method 3: OFFSET (simple but fragile with ties)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Nth highest salary (generalized with window function)
SELECT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
    FROM employees
) ranked
WHERE dr = 5  -- change this to get any Nth highest
LIMIT 1;

-- 1. Find the department with the highest average salary
SELECT d.name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY avg_salary DESC
LIMIT 1;

-- Or using a CTE for clarity:
WITH dept_avgs AS (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM employees
    GROUP BY department_id
)
SELECT d.name, da.avg_salary
FROM dept_avgs da
JOIN departments d ON da.department_id = d.id
ORDER BY da.avg_salary DESC
LIMIT 1;

-- 2. Find duplicate emails in a users table
SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;

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

-- 3. Customers who have never placed an order
SELECT c.id, c.name, c.email
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.customer_id IS NULL;

-- 4. Find employees earning more than their manager
SELECT e.name AS employee, e.salary,
       m.name AS manager,  m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

-- 5. Running total and percentage of total in one query
SELECT
    product_name,
    revenue,
    SUM(revenue) OVER (ORDER BY revenue DESC
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
        AS cumulative_revenue,
    ROUND(100.0 * revenue / SUM(revenue) OVER (), 2) AS pct_of_total
FROM product_revenue
ORDER BY revenue DESC;