Saving SQL Queries That Are Worth Keeping

Some SQL queries are throwaway. Others take 20 minutes to get right and are worth saving. The problem is there’s no good place to put them — notebook files get scattered, Slack threads are unsearchable, and wiki pages go stale.

CodeShelf keeps your best queries organised and accessible from the menu bar, with syntax highlighting that makes SQL look right at a glance.

What’s worth saving

Not every query deserves a snippet. Save it if:

• It took meaningful effort to write correctly (window functions, CTEs, timezone handling)
• You’ll want to adapt it for similar analyses later
• It’s a pattern you or your team uses repeatedly

Throwaway queries — quick SELECT * checks, ad-hoc counts — aren’t worth the overhead.

Categories that work for SQL

Organise by what the query does:

Data Quality

-- Find duplicates by email
SELECT email, COUNT(*) AS count, STRING_AGG(id::text, ', ') AS ids
FROM users
WHERE email IS NOT NULL
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY count DESC;

User Analytics

-- Monthly active users with month-over-month growth
WITH mau AS (
  SELECT DATE_TRUNC('month', login_date) AS month,
         COUNT(DISTINCT user_id) AS users
  FROM user_sessions
  WHERE login_date >= CURRENT_DATE - INTERVAL '12 months'
  GROUP BY 1
)
SELECT month, users,
       ROUND(100.0 * (users - LAG(users) OVER (ORDER BY month)) /
             NULLIF(LAG(users) OVER (ORDER BY month), 0), 1) AS mom_growth
FROM mau
ORDER BY month;

Performance Monitoring

-- Slow queries with share of total execution time
SELECT query,
       calls,
       ROUND(mean_exec_time / 1000, 3) AS avg_seconds,
       ROUND(100.0 * total_exec_time / SUM(total_exec_time) OVER (), 2) AS pct_total
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat_statements%'
ORDER BY total_exec_time DESC
LIMIT 20;

Making queries reusable

The best saved queries are parameterised, not hardcoded. Mark adjustable values in comments:

-- Parameters: replace the table name and date column below
SELECT COUNT(*) FROM users  -- ← change table
WHERE updated_at >= CURRENT_DATE - INTERVAL '7 days';  -- ← change interval

When you use it next time, the comment tells you exactly what to adapt.

Multi-step for query workflows

Some analytical work follows a fixed sequence — a data quality audit, an A/B test analysis, a monthly business review. Use multi-step snippets for these: each step is a query you can copy and run in order.

Data audit sequence:

1. Table row counts and dead rows
2. Column completeness check
3. Orphaned record scan
4. Data freshness check

Stored as a multi-step snippet, this audit takes a few minutes instead of the time it takes reassembling the queries from memory.

Syntax highlighting makes a difference

CodeShelf renders SQL with syntax highlighting via Highlightr and displays a SQL icon next to each snippet. When you have 40+ snippets, being able to visually distinguish a SQL query from a bash command at a glance is practically useful.

Download CodeShelf — free for 2 snippets, Pro unlocks unlimited for a one-time $4.99.