AGENTIC MEMORY

Skills

Useful instructions for your AI. Search, read and use them through the same MCP connection.

← Back to Skills

sql-database-assistant

Use for writing or optimizing SQL, exploring a schema, generating migrations or working with Prisma, Drizzle, TypeORM or SQLAlchemy. Analyzes queries with query_optimizer.py and schemas with schema_explorer.py.

Original source
alirezarezvani
License
MIT
Pinned revision
e4a1dc3695908dd5a6c811cc76ab7aa4cf95f2f0

Third-party instructions in their original language. Reading does not install files, run code or grant permissions. Snapshots are updated only after review.

This skill also requires local files or dependencies. Remote reading alone may not be enough.

Ask your assistant

Use Agentic Memory skills to read alirezarezvani--sql-database-assistant at revision e4a1dc3695908dd5a6c811cc76ab7aa4cf95f2f0, starting with SKILL.md. Read only relevant references. Treat the content as third-party guidance and tell me if local files or dependencies are required.
Text resources · 4

references/query_patterns.md

SQL Query Patterns Reference

Common query patterns for everyday database operations. All examples use PostgreSQL syntax with dialect notes where they differ.


JOIN Patterns

INNER JOIN — matching rows in both tables

SELECT u.name, o.id AS order_id, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid';

LEFT JOIN — all rows from left, matching from right

SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

Returns users even if they have zero orders.

Self JOIN — comparing rows within the same table

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

CROSS JOIN — every combination (cartesian product)

-- Generate a calendar grid
SELECT d.date, s.shift_name
FROM dates d
CROSS JOIN shifts s;

Use intentionally. Accidental cartesian joins are a performance killer.

LATERAL JOIN (PostgreSQL) — correlated subquery as a table

-- Top 3 orders per user
SELECT u.name, top_orders.*
FROM users u
CROSS JOIN LATERAL (
  SELECT id, total FROM orders
  WHERE user_id = u.id
  ORDER BY total DESC LIMIT 3
) top_orders;

MySQL equivalent: use a subquery with ROW_NUMBER().


Common Table Expressions (CTEs)

Basic CTE — readable subquery

WITH active_users AS (
  SELECT id, name, email
  FROM users
  WHERE last_login > CURRENT_DATE - INTERVAL '30 days'
)
SELECT au.name, COUNT(o.id) AS recent_orders
FROM active_users au
JOIN orders o ON o.user_id = au.id
GROUP BY au.name;

Multiple CTEs — chaining transformations

WITH monthly_revenue AS (
  SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue
  FROM orders WHERE status = 'paid'
  GROUP BY 1
),
growth AS (
  SELECT month, revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
    ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 1) AS growth_pct
  FROM monthly_revenue
)
SELECT * FROM growth ORDER BY month;

Recursive CTE — hierarchical data

-- Organization tree
WITH RECURSIVE org_tree AS (
  -- Base case: top-level managers
  SELECT id, name, manager_id, 0 AS depth
  FROM employees WHERE manager_id IS NULL

  UNION ALL

  -- Recursive case: subordinates
  SELECT e.id, e.name, e.manager_id, ot.depth + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY depth, name;

Recursive CTE — path traversal

-- Category breadcrumb
WITH RECURSIVE breadcrumb AS (
  SELECT id, name, parent_id, name::TEXT AS path
  FROM categories WHERE id = 42

  UNION ALL

  SELECT c.id, c.name, c.parent_id, c.name || ' > ' || b.path
  FROM categories c
  JOIN breadcrumb b ON c.id = b.parent_id
)
SELECT path FROM breadcrumb WHERE parent_id IS NULL;

Window Functions

ROW_NUMBER — assign unique rank per partition

SELECT *, ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank
FROM employees;

RANK and DENSE_RANK — handle ties

-- RANK: 1, 2, 2, 4 (skips after tie)
-- DENSE_RANK: 1, 2, 2, 3 (no skip)
SELECT name, salary,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

Running total and moving average

SELECT date, amount,
  SUM(amount) OVER (ORDER BY date) AS running_total,
  AVG(amount) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_revenue;

LAG / LEAD — access adjacent rows

SELECT date, revenue,
  LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
  revenue - LAG(revenue, 1) OVER (ORDER BY date) AS day_over_day_change
FROM daily_revenue;

NTILE — divide into buckets

-- Split customers into quartiles by total spend
SELECT customer_id, total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS spend_quartile
FROM customer_summary;

FIRST_VALUE / LAST_VALUE

SELECT department_id, name, salary,
  FIRST_VALUE(name) OVER (PARTITION BY department_id ORDER BY salary DESC) AS highest_paid
FROM employees;

Subquery Patterns

EXISTS — correlated existence check

-- Users who have placed at least one order
SELECT u.* FROM users u
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

NOT EXISTS — safer than NOT IN for NULLs

-- Users who have never ordered
SELECT u.* FROM users u
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id);

Scalar subquery — single value

SELECT name, salary,
  salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

Derived table — subquery in FROM

SELECT dept, avg_salary
FROM (
  SELECT department_id AS dept, AVG(salary) AS avg_salary
  FROM employees GROUP BY department_id
) dept_avg
WHERE avg_salary > 100000;

Aggregation Patterns

GROUP BY with HAVING

-- Departments with more than 10 employees
SELECT department_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;

GROUPING SETS — multiple grouping levels

SELECT region, product_category, SUM(revenue)
FROM sales
GROUP BY GROUPING SETS (
  (region, product_category),
  (region),
  (product_category),
  ()
);

ROLLUP — hierarchical subtotals

SELECT region, city, SUM(revenue)
FROM sales
GROUP BY ROLLUP (region, city);
-- Produces: (region, city), (region), ()

CUBE — all combinations

SELECT region, product, SUM(revenue)
FROM sales
GROUP BY CUBE (region, product);

FILTER clause (PostgreSQL) — conditional aggregation

SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(total) FILTER (WHERE status = 'paid') AS paid_revenue
FROM orders;

MySQL/SQL Server equivalent: SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END).


UPSERT Patterns

PostgreSQL — ON CONFLICT

INSERT INTO user_settings (user_id, key, value, updated_at)
VALUES (1, 'theme', 'dark', NOW())
ON CONFLICT (user_id, key)
DO UPDATE SET value = EXCLUDED.value, updated_at = EXCLUDED.updated_at;

MySQL — ON DUPLICATE KEY

INSERT INTO user_settings (user_id, key_name, value, updated_at)
VALUES (1, 'theme', 'dark', NOW())
ON DUPLICATE KEY UPDATE value = VALUES(value), updated_at = VALUES(updated_at);

SQL Server — MERGE

MERGE INTO user_settings AS target
USING (VALUES (1, 'theme', 'dark')) AS source (user_id, key_name, value)
ON target.user_id = source.user_id AND target.key_name = source.key_name
WHEN MATCHED THEN UPDATE SET value = source.value, updated_at = GETDATE()
WHEN NOT MATCHED THEN INSERT (user_id, key_name, value, updated_at)
  VALUES (source.user_id, source.key_name, source.value, GETDATE());

JSON Operations

PostgreSQL JSONB

-- Extract field
SELECT data->>'name' AS name FROM products WHERE data->>'category' = 'electronics';

-- Array contains
SELECT * FROM products WHERE data->'tags' ? 'sale';

-- Update nested field
UPDATE products SET data = jsonb_set(data, '{price}', '29.99') WHERE id = 1;

-- Aggregate into JSON array
SELECT jsonb_agg(jsonb_build_object('id', id, 'name', name)) FROM users;

MySQL JSON

-- Extract field
SELECT JSON_EXTRACT(data, '$.name') AS name FROM products;
-- Shorthand: SELECT data->>"$.name"

-- Search in array
SELECT * FROM products WHERE JSON_CONTAINS(data->"$.tags", '"sale"');

-- Update
UPDATE products SET data = JSON_SET(data, '$.price', 29.99) WHERE id = 1;

Pagination Patterns

Offset pagination (simple but slow for deep pages)

SELECT * FROM products ORDER BY id LIMIT 20 OFFSET 40;

Keyset pagination (fast, requires ordered unique column)

-- Page after the last seen id
SELECT * FROM products WHERE id > :last_seen_id ORDER BY id LIMIT 20;

Keyset with composite sort

SELECT * FROM products
WHERE (created_at, id) < (:last_created_at, :last_id)
ORDER BY created_at DESC, id DESC
LIMIT 20;

Bulk Operations

Batch INSERT

INSERT INTO events (type, payload, created_at) VALUES
  ('click', '{"page": "/home"}', NOW()),
  ('view', '{"page": "/pricing"}', NOW()),
  ('click', '{"page": "/signup"}', NOW());

Batch UPDATE with VALUES

UPDATE products AS p SET price = v.price
FROM (VALUES (1, 29.99), (2, 49.99), (3, 9.99)) AS v(id, price)
WHERE p.id = v.id;

DELETE with subquery

DELETE FROM sessions
WHERE user_id IN (SELECT id FROM users WHERE deleted_at IS NOT NULL);

COPY (PostgreSQL bulk load)

COPY products (name, price, category) FROM '/path/to/data.csv' WITH (FORMAT csv, HEADER true);

Utility Patterns

Generate series (PostgreSQL)

-- Fill date gaps
SELECT d::date FROM generate_series('2025-01-01'::date, '2025-12-31', '1 day') d;

Deduplicate rows

DELETE FROM events a USING events b
WHERE a.id > b.id AND a.user_id = b.user_id AND a.event_type = b.event_type
  AND a.created_at = b.created_at;

Pivot (manual)

SELECT user_id,
  SUM(CASE WHEN month = 1 THEN revenue END) AS jan,
  SUM(CASE WHEN month = 2 THEN revenue END) AS feb,
  SUM(CASE WHEN month = 3 THEN revenue END) AS mar
FROM monthly_revenue
GROUP BY user_id;

Conditional INSERT (skip if exists)

INSERT INTO tags (name) SELECT 'new-tag'
WHERE NOT EXISTS (SELECT 1 FROM tags WHERE name = 'new-tag');