Datamata Studios

User Guide

Overview

The SQL Snippet Library is a curated collection of battle-tested SQL patterns for common database tasks. Each snippet is copy-ready, tagged for discoverability and optimized for SQLite (most patterns work in PostgreSQL/MySQL too).

Instead of googling "SQL upsert syntax" for the 10th time, grab the snippet and adapt it to your schema in seconds.

When to Use This Tool

Use the snippet library when:

You need a quick reference for common SQL patterns
Learning advanced SQL techniques like window functions and CTEs
Building prototypes and need reliable starting code
Avoiding syntax errors by using proven patterns
Teaching SQL to colleagues or students with concrete examples
Looking for performance-optimized alternatives to naive queries

How to Use

1. Browse by Tag

Snippets are tagged: schema, dml, analytics, data-quality, json. Filter by clicking tags to find relevant patterns quickly.

2. Copy to Clipboard

Click the "Copy" button on any snippet. The code is ready to paste into the SQLite Playground, your IDE (VS Code, DataGrip) or production scripts.

3. Adapt to Your Schema

Replace placeholder names with your actual table and column names:

-- Original snippet:
SELECT user_id, COUNT(*) FROM events GROUP BY user_id;

-- Your version:
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id;

4. Test in Playground

Paste the snippet into the SQLite Playground to test it immediately with sample data.

Snippet Categories

schema: Table creation, constraints, primary keys, unique constraints
dml: Inserts, updates, deletes, upserts (INSERT ... ON CONFLICT)
analytics: Aggregations, window functions, pivots, Top-N per group
data-quality: Duplicate detection, NULL handling, data validation
json: JSON extraction and querying (SQLite 3.38+)
cte: Common Table Expressions, recursive queries for hierarchies

Popular Snippets

Upsert by Primary Key

Insert a row or update it if the primary key already exists:

INSERT INTO users (id, email, name)
VALUES (1, 'ada@example.com', 'Ada')
ON CONFLICT(id) DO UPDATE SET
  email = excluded.email,
  name = excluded.name;

Window Function - Ranking

Rank users by score within each cohort:

SELECT
  user_id,
  cohort,
  score,
  RANK() OVER (PARTITION BY cohort ORDER BY score DESC) AS cohort_rank
FROM leaderboard;

Top-N per Group

Get the best 3 scores per cohort:

SELECT * FROM (
  SELECT
    user_id,
    cohort,
    score,
    ROW_NUMBER() OVER (PARTITION BY cohort ORDER BY score DESC) AS rnk
  FROM leaderboard
) ranked
WHERE rnk <= 3;

Detect Duplicates

Find emails that appear multiple times:

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Tips and Best Practices

Test First: Always test snippets in a development environment before running in production.
Add Indexes: For snippets with WHERE clauses or window functions, create indexes on relevant columns for better performance.
Check Dialect: Some patterns are SQLite-specific (e.g., ON CONFLICT). For MySQL, use ON DUPLICATE KEY UPDATE instead.
Use EXPLAIN: Run EXPLAIN QUERY PLAN before your query to check if indexes are being used efficiently.
Avoid SELECT *: Specify columns explicitly to reduce I/O and improve query performance.

Snippet Library