Patterns
Battle-tested regex, cron and SQL patterns — each one explained, edge-cased, and one click from a live test against your own data.
PatternRegex
Match a UUID v4[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-4[0-9a-fA-F]{3}-[89abAB][0-9a-fA-F]{3}-[0-9a-fA-F]{12}
PatternRegex
Match an ISO 8601 timestamp\d{4}-\d{2}-\d{2}T\d{2}:\d{2}:\d{2}(?:\.\d+)?(?:Z|[+-]\d{2}:\d{2})
PatternSQL
Top N per groupSELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY category
ORDER BY price DESC
) AS rn
FROM products
)
WHERE rn <= 3;
PatternSQL
Deduplicate, keeping the latest rowSELECT *
FROM (
SELECT *,
ROW_NUMBER() OVER (
PARTITION BY sku
ORDER BY updated_at DESC
) AS rn
FROM imports
)
WHERE rn = 1;
PatternSQL
Running totalSELECT day,
amount,
SUM(amount) OVER (ORDER BY day) AS running_total
FROM daily_sales
ORDER BY day;
PatternSQL
Find duplicate rowsSELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;
PatternRegex
Match any UUID\b[0-9a-fA-F]{8}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{4}-[0-9a-fA-F]{12}\b
PatternRegex
Validate a Base64 string^(?:[A-Za-z0-9+/]{4})*(?:[A-Za-z0-9+/]{2}==|[A-Za-z0-9+/]{3}=)?$
PatternRegex
Match an MD5, SHA-1 or SHA-256 hash\b[a-fA-F0-9]{64}\b|\b[a-fA-F0-9]{40}\b|\b[a-fA-F0-9]{32}\b
PatternSQL
Find orphaned rowsSELECT o.*
FROM orders AS o
LEFT JOIN customers AS c
ON o.customer_id = c.id
WHERE c.id IS NULL;
PatternSQL
Rows in one table but not anotherSELECT u.*
FROM users AS u
WHERE NOT EXISTS (
SELECT 1
FROM orders AS o
WHERE o.user_id = u.id
);
PatternSQL
Pivot rows into columnsSELECT
order_month,
SUM(CASE WHEN region = 'east' THEN amount ELSE 0 END) AS east,
SUM(CASE WHEN region = 'west' THEN amount ELSE 0 END) AS west,
SUM(CASE WHEN region = 'north' THEN amount ELSE 0 END) AS north
FROM orders
GROUP BY order_month;
PatternSQL
Group by monthSELECT
date_trunc('month', created_at) AS month,
COUNT(*) AS signups
FROM users
GROUP BY month
ORDER BY month;
PatternSQL
Percent of total per rowSELECT
product,
revenue,
revenue * 100.0 / SUM(revenue) OVER () AS pct_of_total
FROM product_revenue
ORDER BY pct_of_total DESC;
PatternSQL
7-row moving averageSELECT
day,
amount,
AVG(amount) OVER (
ORDER BY day
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
) AS moving_avg_7
FROM daily_sales
ORDER BY day;
PatternSQL
Day-over-day changeSELECT
day,
value,
LAG(value) OVER (ORDER BY day) AS previous_value,
value - LAG(value) OVER (ORDER BY day) AS day_over_day_change
FROM daily_metrics
ORDER BY day;
PatternSQL
Median and percentilesSELECT
sensor_id,
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value) AS median_value,
PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY value) AS p90_value
FROM measurements
GROUP BY sensor_id;
PatternSQL
Bucket values into rangesSELECT
CASE
WHEN order_total < 100 THEN '0-99'
WHEN order_total < 500 THEN '100-499'
WHEN order_total < 1000 THEN '500-999'
ELSE '1000+'
END AS bucket,
COUNT(*) AS orders
FROM orders
GROUP BY bucket
ORDER BY MIN(order_total);
PatternSQL
Split a delimited column into rowsSELECT
id,
unnest(string_split(tags, ',')) AS tag
FROM products;
PatternSQL
First and latest value per groupSELECT
user_id,
min_by(event_name, occurred_at) AS first_event,
max_by(event_name, occurred_at) AS latest_event
FROM events
GROUP BY user_id;
PatternSQL
Count distinct per groupSELECT
day,
COUNT(*) AS events,
COUNT(DISTINCT user_id) AS unique_users
FROM events
GROUP BY day
ORDER BY day;