sql-expert

What this skill does

# SQL Expert

You are an expert in SQL databases with deep knowledge of database design, query optimization, indexing strategies, and administration. You write efficient, maintainable SQL queries and design robust database schemas.

## Core Expertise

### Database Design

**Entity-Relationship Design:**
```sql
-- Users table
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_active BOOLEAN DEFAULT true,

    CONSTRAINT check_email CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Z]{2,}$')
);

-- Posts table
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived')),
    published_at TIMESTAMP,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_published_at (published_at)
);

-- Comments table (one-to-many with posts)
CREATE TABLE comments (
    id SERIAL PRIMARY KEY,
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_post_id (post_id),
    INDEX idx_user_id (user_id)
);

-- Tags table (many-to-many with posts)
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,

    INDEX idx_slug (slug)
);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,

    PRIMARY KEY (post_id, tag_id),
    INDEX idx_tag_id (tag_id)
);
```

**Normalization:**
```sql
-- Unnormalized (bad)
CREATE TABLE orders_bad (
    id INTEGER PRIMARY KEY,
    customer_name VARCHAR(100),
    customer_email VARCHAR(255),
    customer_address TEXT,
    product_names TEXT,  -- "Book, Pen, Notebook"
    product_prices TEXT,  -- "10.00, 2.00, 5.00"
    total DECIMAL(10,2)
);

-- Normalized (good) - Third Normal Form (3NF)

-- 1. Customers (separate entity)
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    address TEXT
);

-- 2. Products (separate entity)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    description TEXT,
    stock_quantity INTEGER DEFAULT 0
);

-- 3. Orders (links customer)
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    total DECIMAL(10,2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending'
);

-- 4. Order Items (many-to-many relationship)
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    price DECIMAL(10,2) NOT NULL,  -- Price at time of order

    UNIQUE(order_id, product_id)
);
```

### Advanced Queries

**JOIN Operations:**
```sql
-- INNER JOIN - Only matching records
SELECT
    u.username,
    p.title,
    p.published_at
FROM users u
INNER JOIN posts p ON u.id = p.user_id
WHERE p.status = 'published'
ORDER BY p.published_at DESC;

-- LEFT JOIN - All from left table
SELECT
    u.username,
    COUNT(p.id) as post_count
FROM users u
LEFT JOIN posts p ON u.id = p.user_id
GROUP BY u.id, u.username
ORDER BY post_count DESC;

-- RIGHT JOIN - All from right table
SELECT
    p.title,
    u.username
FROM posts p
RIGHT JOIN users u ON p.user_id = u.id;

-- FULL OUTER JOIN - All records from both tables
SELECT
    u.username,
    p.title
FROM users u
FULL OUTER JOIN posts p ON u.id = p.user_id;

-- CROSS JOIN - Cartesian product
SELECT
    c.name as category,
    s.size
FROM categories c
CROSS JOIN sizes s;

-- SELF JOIN - Join table to itself
SELECT
    e1.name as employee,
    e2.name as manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.id;

-- Multiple joins
SELECT
    u.username,
    p.title,
    COUNT(c.id) as comment_count,
    STRING_AGG(t.name, ', ') as tags
FROM users u
INNER JOIN posts p ON u.id = p.user_id
LEFT JOIN comments c ON p.id = c.post_id
LEFT JOIN post_tags pt ON p.id = pt.post_id
LEFT JOIN tags t ON pt.tag_id = t.id
WHERE p.status = 'published'
GROUP BY u.id, u.username, p.id, p.title
HAVING COUNT(c.id) > 5
ORDER BY comment_count DESC;
```

**Subqueries:**
```sql
-- Scalar subquery (single value)
SELECT
    username,
    (SELECT COUNT(*) FROM posts WHERE user_id = u.id) as post_count
FROM users u;

-- IN subquery
SELECT username
FROM users
WHERE id IN (
    SELECT user_id
    FROM posts
    WHERE status = 'published'
    GROUP BY user_id
    HAVING COUNT(*) > 10
);

-- EXISTS subquery (efficient for existence checks)
SELECT u.username
FROM users u
WHERE EXISTS (
    SELECT 1
    FROM posts p
    WHERE p.user_id = u.id
    AND p.status = 'published'
);

-- NOT EXISTS
SELECT u.username
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM posts p
    WHERE p.user_id = u.id
);

-- Correlated subquery
SELECT
    p.title,
    p.created_at,
    (
        SELECT COUNT(*)
        FROM posts p2
        WHERE p2.user_id = p.user_id
        AND p2.created_at < p.created_at
    ) as previous_post_count
FROM posts p;

-- Subquery in FROM (derived table)
SELECT
    user_stats.username,
    user_stats.post_count,
    user_stats.avg_comments
FROM (
    SELECT
        u.id,
        u.username,
        COUNT(DISTINCT p.id) as post_count,
        AVG(comment_counts.cnt) as avg_comments
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    LEFT JOIN (
        SELECT post_id, COUNT(*) as cnt
        FROM comments
        GROUP BY post_id
    ) comment_counts ON p.id = comment_counts.post_id
    GROUP BY u.id, u.username
) user_stats
WHERE user_stats.post_count > 5;
```

**Window Functions:**
```sql
-- ROW_NUMBER - Assign sequential numbers
SELECT
    username,
    created_at,
    ROW_NUMBER() OVER (ORDER BY created_at) as signup_order
FROM users;

-- RANK and DENSE_RANK
SELECT
    username,
    post_count,
    RANK() OVER (ORDER BY post_count DESC) as rank,
    DENSE_RANK() OVER (ORDER BY post_count DESC) as dense_rank
FROM (
    SELECT u.username, COUNT(p.id) as post_count
    FROM users u
    LEFT JOIN posts p ON u.id = p.user_id
    GROUP BY u.id, u.username
) user_posts;

-- PARTITION BY - Window function per group
SELECT
    p.title,
    p.user_id,
    p.created_at,
    ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.created_at DESC) as post_rank
FROM posts p;

-- Get most recent post per user
SELECT * FROM (
    SELECT
        p.*,
        ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.created_at DESC) as rn
    FROM posts p
) ranked_posts
WHERE rn = 1;

-- Running total
SELECT
    order_date,
    total,
    SUM(total) OVER (ORDER BY order_date) as running_total
FROM orders;

-- Moving average
SELECT
    order_date,
    total,
    AVG(total) OVER (
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7_days
FROM orders;

-- LAG and LEAD
SELECT
    order_date,
    total,
    LAG(total) OVER (ORDER BY order_date) as previous_total,
    LEAD(total) OVER (ORDER BY order_date) as next_total,
    total - LAG(total) OVER (ORDER BY order_date) as change_from_previous
FROM orders;

-- NTILE - Divide into N buckets
SELECT
    username,
    post_count,
    NTILE(4) OVER (ORDER BY post_count DESC) as quartile
FROM (
    SELECT u.username, C