Reusable SQL, Python & R Snippet Library

Welcome — purpose and how to use this library

This library gathers compact, well-documented code patterns you can copy, adapt, and review for common data tasks. It is intentionally pragmatic: examples focus on intent, inputs, outputs, and minimal runnable code. Always adapt snippets to your database dialect, schema, governance rules, and performance needs.

How to use

• Read the short description and inputs/outputs before copying.
• Adapt identifiers, table/column names and types to your environment.
• Test on a small sample and measure performance before production use.
• Document any changes and add attribution or license notes per your policy.

Categories

• Date & time math
• Cohort joins & retention
• Windowing & ranking
• Aggregation & rollups
• String handling & normalization
• Pivoting / unpivoting
• Sampling & pagination
• Validation & lightweight testing
• Feature transforms (for ML & analytics)

Date & Time Math

Common patterns to align periods, compute intervals, and generate date ranges. Note: SQL functions differ by dialect—replace with DATEADD/DATE_TRUNC/DATE_TRUNC etc. where required.

SQL — truncate to period and get prior period

Inputs: table with event_date (date/time). Output: rows aggregated by period with prior-period values.

-- SQL (Postgres-like functions; adapt to your dialect)
SELECT
  date_trunc('month', event_date)::date AS month_start,
  COUNT(*) AS events
FROM events
GROUP BY 1
ORDER BY 1;

-- Prior month join
WITH monthly AS (
  SELECT date_trunc('month', event_date)::date AS month_start, COUNT(*) AS events
  FROM events
  GROUP BY 1
)
SELECT
  m.month_start,
  m.events,
  l.events AS prior_month_events
FROM monthly m
LEFT JOIN monthly l ON l.month_start = m.month_start - INTERVAL '1 month'
ORDER BY m.month_start;

Python (pandas) — floor dates to month and compute month-over-month change

import pandas as pd
# df has 'event_date' datetime
df['month_start'] = df['event_date'].dt.to_period('M').dt.to_timestamp()
monthly = df.groupby('month_start').size().reset_index(name='events')
monthly['prior_events'] = monthly['events'].shift(1)
monthly['pct_change'] = (monthly['events'] / monthly['prior_events']) - 1

Cohort Joins & Retention

Identify cohorts by first event and compute retention across periods.

SQL — cohort by first purchase and retention by month

WITH first_event AS (
  SELECT user_id, MIN(event_date)::date AS cohort_date
  FROM events
  GROUP BY user_id
),
user_months AS (
  SELECT e.user_id,
         date_trunc('month', e.event_date)::date AS activity_month,
         f.cohort_date
  FROM events e
  JOIN first_event f USING (user_id)
)
SELECT cohort_date,
       activity_month,
       COUNT(DISTINCT user_id) AS active_users
FROM user_months
GROUP BY 1,2
ORDER BY 1,2;

Windowing & Ranking

Common: dedupe by latest event per entity, running totals, rank by metric.

SQL — keep latest record per user

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) AS rn
  FROM user_profiles
) t WHERE rn = 1;

Python (pandas) equivalent

latest = df.sort_values(['user_id','updated_at']).groupby('user_id').tail(1)

Aggregation & Rollups

Use GROUP BY CUBE/ROLLUP where supported. When not available, union patterns work.

SQL — safe aggregation with NULL-safe grouping

SELECT
  COALESCE(country, 'UNKNOWN') AS country,
  COALESCE(channel, 'UNKNOWN') AS channel,
  COUNT(*) AS orders,
  SUM(amount) AS revenue
FROM orders
GROUP BY COALESCE(country, 'UNKNOWN'), COALESCE(channel, 'UNKNOWN');

String Handling & Normalization

Clean user-provided strings for joins or grouping: trimming, lowercasing, removing punctuation.

SQL — basic normalize function (Postgres-ish)

SELECT lower(regexp_replace(trim(name), '[^\w\s]', '', 'g')) AS name_norm
FROM customers;

Python — normalize text

import re
s = ' Acme, Inc. '\nname_norm = re.sub(r'[^\w\s]','', s.strip().lower())

Pivoting / Unpivoting

Examples for turning long-to-wide and vice-versa.

Python (pandas) pivot_table

pivot = df.pivot_table(index='user_id', columns='event_type', values='value', aggfunc='sum', fill_value=0)

R (tidyr) pivot_wider

library(tidyr)
wide <- df %>% pivot_wider(names_from = event_type, values_from = value, values_fill = 0)

Sampling & Pagination

Practical patterns for previewing data and producing paged API responses.

SQL — reservoir / random sample (Postgres)

SELECT * FROM users ORDER BY random() LIMIT 100;

-- Pagination (offset/limit) — beware performance for large offsets
SELECT * FROM events WHERE user_id = 123 ORDER BY event_date DESC LIMIT 50 OFFSET 0;

Python — stratified sample using pandas

sample = df.groupby('segment', group_keys=False).apply(lambda x: x.sample(frac=0.1))

Validation & Lightweight Tests

Small checks that help detect schema or data problems early.

• Count nulls per column (SQL and pandas)
• Row counts vs source table
• Unique keys check (duplicates)

-- SQL: check duplicates
SELECT id, COUNT(*) FROM table GROUP BY id HAVING COUNT(*) > 1;

-- pandas: null counts
df.isnull().sum()

Feature Transforms (ML & analytics)

Common transforms: one-hot, log scaling, winsorizing, bucketing.

-- Python example: log transform and one-hot
import numpy as np
from sklearn.preprocessing import OneHotEncoder

df['amount_log'] = np.log1p(df['amount'])
# One-hot for 'category'
enc = OneHotEncoder(sparse=False, handle_unknown='ignore')
encoded = enc.fit_transform(df[['category']])

Licensing, Attribution & Governance

Snippets here are patterns and examples, not production-ready modules. Use these rules when adopting snippets:

• Record who adapted the snippet, the date, and the target schema/DB dialect.
• Add a short test and expected results when you place a snippet into production code.
• Note performance considerations (indexes, partitioning) and security/privacy risks (PII in query results).
• Respect your organization's code-license policy; attribute third-party recipes if reused from external sources.

Mal Hungers / Cautions

Copying snippets without review can introduce errors, leak sensitive data, create inefficient queries, or propagate inconsistent metrics. Treat these examples as starting points — test, profile, and govern them before adoption.

Where to go next

If this collection is helpful, consider:

• Creating a curated, owned toolkit for your team with dialect-specific variants and tests.
• Adding CI checks that run validation queries on sample data after snippet updates.
• Documenting canonical metrics and canonical SQL/Python/R implementations to reduce inconsistent metrics across teams.

Author's note: This library is intentionally practical and extensible. If you want dialect-specific variants (e.g., BigQuery, Snowflake, Redshift, Postgres, MySQL), or interactive snippet-adaptation tools, the content can be expanded into a tailored toolkit with examples and automated tests.