Reusable SQL, Python & R Snippets — Starter Index

Welcome — purpose and scope

This repository is a starter index of vetted, reusable patterns for common data tasks analysts and teams perform frequently: time-series aggregations, rolling windows, cohort definitions and joins, data-quality checks and assertions, feature transformations, sampling and extracts. The goal is to help teams produce reliable, reproducible results faster while keeping attention on interpretation and decisions.

How to use these snippets

• Treat each snippet as a pattern, not a drop-in solution. Adapt table/column names, parameterize limits, and test against your data.
• Prefer parameterized queries and prepared statements for production SQL. Add indexes or materialized views where performance matters.
• Run data-quality checks on a sample before full-scale execution. Record results and thresholds so the checks become part of your pipeline.
• When converting between languages, verify numeric types, timezone handling, and null/NA semantics.

Categories (expanded)

1. Time-series aggregations and rolling windows

   Patterns for fixed-window and rolling computations, aligning by timestamp, and handling irregular time buckets.

2. Cohort definitions and cohort joins

   Define user/customer cohorts by first event, acquisition date, or other anchor and join to subsequent events for retention or behavioral cohorts.

3. Common data-quality checks and assertions

   Null counts, uniqueness checks, referential integrity scans, range/constraint validations, and sample record inspection queries.

4. Feature transformations and feature-store extracts

   Standardize numeric transforms, categorical encodings, temporal features, bucketing, and safe joins to build reproducible feature extracts.

5. Sampling, stratified extracts, and validation splits

   Reproducible sampling patterns for modeling and holdout validation.

6. How to contribute and naming conventions

   Guidance for submitting new snippets, tests, and versions so the library stays useful and maintainable.

Short vetted examples

SQL — 7-day rolling sum (time-ordered)

Use window functions. Adjust ORDER BY and frame depending on business definition.

SELECT
  id,
  event_date,
  value,
  SUM(value) OVER (PARTITION BY id ORDER BY event_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7
FROM your_schema.your_table
ORDER BY id, event_date;

Python (pandas) — groupwise rolling sum

Sort first, then transform per group. This preserves index order and is robust for irregular timestamps.

df = df.sort_values(['id', 'date'])
df['rolling_7'] = df.groupby('id')['value'].transform(
    lambda x: x.rolling(window=7, min_periods=1).sum()
)

R (dplyr + zoo) — rolling sum per group

Use zoo::rollapplyr for a right-aligned rolling window; ensure grouping and ordering first.

library(dplyr)
library(zoo)

df <- df %>%
  arrange(id, date) %>%
  group_by(id) %>%
  mutate(rolling_7 = rollapplyr(value, width = 7, partial = TRUE, FUN = sum))

Cohort join (SQL) — new-user retention example

WITH first_event AS (
  SELECT user_id, MIN(event_date) AS cohort_date
  FROM events
  GROUP BY user_id
)
SELECT f.cohort_date,
       DATE_TRUNC('week', e.event_date) AS event_week,
       COUNT(DISTINCT e.user_id) AS active_users
FROM first_event f
JOIN events e
  ON e.user_id = f.user_id
  AND e.event_date BETWEEN f.cohort_date AND f.cohort_date + INTERVAL '27 days'
GROUP BY 1, 2
ORDER BY 1, 2;

Data-quality quick checks (SQL)

• Null counts per column: SELECT COUNT(*) - COUNT(col) AS null_count FROM table;
• Duplicate keys: SELECT key, COUNT(*) FROM table GROUP BY key HAVING COUNT(*) > 1;
• Out-of-range values: SELECT * FROM table WHERE amount < 0 OR amount > 100000;

Naming, versioning, and contribution checklist

Keep contributions consistent and discoverable.

• File name pattern: lang/category/short-description-v1.sql|py|r (e.g., sql/time-series/rolling-7-days-v1.sql).
• Include: purpose, input assumptions (required columns/types), expected output schema, complexity notes, and minimal test or sample data.
• Tag snippets with: language, category, difficulty, dependencies (packages, DB version), and risk (privacy/performance).
• Document intended execution environment (analytics DB, production warehouse, local pandas, notebook).

Security, governance, and performance notes

• Never embed production credentials or PHI in snippets.
• Consider row-level filtering and access controls before running extracts.
• Large joins and window functions may need indexes, partitioning, or pre-aggregation. Test costs on representative data.
• Treat these snippets as starting points for query tuning and code review prior to deployment.

Suggested next steps

1. Search the index for a pattern, adapt to local schema, run on a small sample, and add tests.
2. When a snippet is promoted to production, add version metadata, a short changelog, and a reference test dataset.
3. Contribute back improvements with clear notes about backward compatibility.

Why this repository matters

Reusable patterns reduce repetitive work, increase consistency across teams, and free analysts to focus on interpretation and decision-making. Good governance, review, and testing keep reuse safe and sustainable.