sql-query-writer

1. UNDERSTAND  -> Parse requirements, identify tables/columns needed
2. IDENTIFY    -> Determine query type (SELECT, INSERT, UPDATE, etc.)
3. STRUCTURE   -> Plan JOINs, subqueries, CTEs as needed
4. WRITE       -> Generate syntactically correct SQL
5. VALIDATE    -> Check for common issues
6. OPTIMIZE    -> Suggest indexes or improvements if relevant

- [ ] Identified the target database dialect
- [ ] Understood the table schema (columns, types, relationships)
- [ ] Clarified any ambiguous requirements
- [ ] Determined if query needs to handle NULLs
- [ ] Identified any performance considerations

SELECT column1, column2, column3
FROM table_name
WHERE condition
ORDER BY column1;

SELECT 
    a.column1,
    b.column2,
    c.column3
FROM table_a a
INNER JOIN table_b b ON a.id = b.a_id
LEFT JOIN table_c c ON b.id = c.b_id
WHERE a.status = 'active'
ORDER BY a.created_at DESC;

SELECT 
    category,
    COUNT(*) AS total_count,
    SUM(amount) AS total_amount,
    AVG(amount) AS avg_amount
FROM transactions
WHERE transaction_date >= '2024-01-01'
GROUP BY category
HAVING SUM(amount) > 1000
ORDER BY total_amount DESC;

SELECT *
FROM employees
WHERE department_id IN (
    SELECT id 
    FROM departments 
    WHERE budget > 100000
);

WITH active_customers AS (
    SELECT customer_id, SUM(order_total) AS total_spent
    FROM orders
    WHERE order_date >= CURRENT_DATE - INTERVAL '1 year'
    GROUP BY customer_id
),
customer_tiers AS (
    SELECT 
        customer_id,
        total_spent,
        CASE 
            WHEN total_spent >= 10000 THEN 'Gold'
            WHEN total_spent >= 5000 THEN 'Silver'
            ELSE 'Bronze'
        END AS tier
    FROM active_customers
)
SELECT c.name, c.email, ct.tier, ct.total_spent
FROM customers c
JOIN customer_tiers ct ON c.id = ct.customer_id
ORDER BY ct.total_spent DESC;

INSERT INTO table_name (column1, column2, column3)
VALUES ('value1', 'value2', 'value3');

INSERT INTO table_name (column1, column2, column3)
VALUES 
    ('value1a', 'value2a', 'value3a'),
    ('value1b', 'value2b', 'value3b'),
    ('value1c', 'value2c', 'value3c');

INSERT INTO archive_table (id, name, created_at)
SELECT id, name, created_at
FROM source_table
WHERE created_at < '2023-01-01';

INSERT INTO users (name, email)
VALUES ('John Doe', 'john@example.com')
RETURNING id, created_at;

INSERT INTO products (sku, name, price)
VALUES ('ABC123', 'Widget', 29.99)
ON CONFLICT (sku) 
DO UPDATE SET 
    name = EXCLUDED.name,
    price = EXCLUDED.price,
    updated_at = CURRENT_TIMESTAMP;

INSERT INTO products (sku, name, price)
VALUES ('ABC123', 'Widget', 29.99)
ON DUPLICATE KEY UPDATE 
    name = VALUES(name),
    price = VALUES(price),
    updated_at = CURRENT_TIMESTAMP;

MERGE INTO products AS target
USING (VALUES ('ABC123', 'Widget', 29.99)) AS source (sku, name, price)
ON target.sku = source.sku
WHEN MATCHED THEN
    UPDATE SET name = source.name, price = source.price
WHEN NOT MATCHED THEN
    INSERT (sku, name, price) VALUES (source.sku, source.name, source.price);

UPDATE table_name
SET column1 = 'new_value',
    column2 = column2 + 1,
    updated_at = CURRENT_TIMESTAMP
WHERE id = 123;

UPDATE orders o
SET status = 'shipped',
    shipped_at = CURRENT_TIMESTAMP
FROM shipments s
WHERE s.order_id = o.id
  AND s.status = 'delivered';

UPDATE orders o
JOIN shipments s ON s.order_id = o.id
SET o.status = 'shipped',
    o.shipped_at = CURRENT_TIMESTAMP
WHERE s.status = 'delivered';

UPDATE o
SET status = 'shipped',
    shipped_at = GETDATE()
FROM orders o
JOIN shipments s ON s.order_id = o.id
WHERE s.status = 'delivered';

UPDATE products
SET price = price * 1.10
WHERE category_id IN (
    SELECT id FROM categories WHERE name = 'Electronics'
);

DELETE FROM table_name
WHERE condition;

DELETE FROM order_items oi
USING orders o
WHERE oi.order_id = o.id
  AND o.status = 'cancelled';

DELETE oi
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
WHERE o.status = 'cancelled';

UPDATE records
SET deleted_at = CURRENT_TIMESTAMP,
    deleted_by = 'user_id'
WHERE id = 123;

TRUNCATE TABLE table_name;
TRUNCATE TABLE table_name RESTART IDENTITY CASCADE; -- PostgreSQL

CREATE TABLE users (
    id SERIAL PRIMARY KEY,                    -- PostgreSQL
    -- id INT AUTO_INCREMENT PRIMARY KEY,     -- MySQL
    -- id INT IDENTITY(1,1) PRIMARY KEY,      -- SQL Server
    
    email VARCHAR(255) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    status VARCHAR(20) DEFAULT 'active',
    balance DECIMAL(10, 2) DEFAULT 0.00,
    metadata JSONB,                           -- PostgreSQL
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    CONSTRAINT chk_status CHECK (status IN ('active', 'inactive', 'suspended'))
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status_created ON users(status, created_at);
CREATE UNIQUE INDEX idx_users_email_unique ON users(email);
CREATE INDEX idx_users_name_lower ON users(LOWER(name)); -- Functional index

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN deprecated_field;
ALTER TABLE users ALTER COLUMN name TYPE VARCHAR(200);
ALTER TABLE users ADD CONSTRAINT fk_department 
    FOREIGN KEY (department_id) REFERENCES departments(id);

CREATE VIEW active_users_summary AS
SELECT 
    u.id,
    u.name,
    u.email,
    COUNT(o.id) AS order_count,
    COALESCE(SUM(o.total), 0) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.status = 'active'
GROUP BY u.id, u.name, u.email;

SELECT * FROM products
ORDER BY created_at DESC
LIMIT 20 OFFSET 40;

SELECT * FROM products
WHERE created_at < '2024-01-15 10:30:00'
ORDER BY created_at DESC
LIMIT 20;

WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree
ORDER BY level, name;

SELECT 
    product_id,
    SUM(CASE WHEN month = 1 THEN sales ELSE 0 END) AS jan,
    SUM(CASE WHEN month = 2 THEN sales ELSE 0 END) AS feb,
    SUM(CASE WHEN month = 3 THEN sales ELSE 0 END) AS mar
FROM monthly_sales
GROUP BY product_id;

SELECT product_id, [1] AS jan, [2] AS feb, [3] AS mar
FROM monthly_sales
PIVOT (SUM(sales) FOR month IN ([1], [2], [3])) AS pvt;

SELECT 
    date_trunc('month', order_date) AS month,
    COUNT(*) AS order_count,
    SUM(total) AS revenue
FROM orders
WHERE order_date >= date_trunc('year', CURRENT_DATE)
GROUP BY date_trunc('month', order_date)
ORDER BY month;

python scripts/plan_joins.py schema.sql --list                       # tables, keys, relationships
python scripts/plan_joins.py schema.sql --tables customers products \
    --columns customers.email,products.name                           # adds bridge tables automatically