25 SQL Reusable Patterns

By Chandra venkat

1) Filter records (NULL-safe)

When to use:
Filter users by country
Filter by signup month
Exclude rows with missing phone

Filter Indian users created in Jan-2025 and phone is not NULL.

SELECT *
FROM users
WHERE country = 'India'
AND created_at >= '2025-01-01' AND created_at < '2025-02-01'
AND phone IS NOT NULL;

Chandra Venkat
2) Summarize with totals
When to use:
Get order totals per category
Calculate number of orders per group
Filter out small categories

Summarize each category and keep only those with 10+ orders

SELECT category,
COUNT(*) AS total_orders,
SUM(amount) AS total_amount
FROM orders
GROUP BY category
HAVING COUNT(*) >= 10;

Tip: Use HAVING after GROUP BY to filter aggregates

Chandra Venkat
 3) Conditional counts (funnels)

When to use:
Count based on status or stage
Compare Completed vs Cancelled
Build funnel breakdowns

Get number of Completed and Cancelled orders

SELECT
SUM(CASE WHEN status = 'Completed' THEN 1 ELSE 0 END) AS completed_orders,
SUM(CASE WHEN status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled_orders
FROM orders;

Tip: Conditional `SUM` is more flexible than `COUNT(IF(...))`

Chandra Venkat
4) Matches or misses (anti-join)

When to use:
Find users with no orders
Spot unmatched records
Common audit/debug pattern

Get users who don’t have any matching orders

SELECT u.*
FROM users u
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.user_id = u.user_id
);

Chandra Venkat
5) Combine datasets

When to use:
Combine buyer and seller records
Append same-schema tables
Stack archived vs current

Merge buyers and sellers into one user list

SELECT user_id FROM buyers

UNION ALL
SELECT user_id FROM sellers;

Tip: Use `UNION ALL` if you don’t need duplicate removal

Chandra Venkat
 6) Top-N per group (ranking)

When to use:
Top products within each category
Best scores per class/team
Latest N events per user

Get Top 3 products by sales within each category

WITH ranked AS (
SELECT
product_id, category, sales,
ROW_NUMBER() OVER ( PARTITION BY category
ORDER BY sales DESC ) AS rn
FROM product_sales
)
SELECT product_id, category, sales
FROM ranked
WHERE rn <= 3;

Tip: `ROW_NUMBER()` breaks ties arbitrarily; use `RANK()` if ties should share rank.

Chandra Venkat
 7) Latest / first per entity

When to use:
Top products within each category
Best scores per class/team
Latest N events per user

Get Top 3 products by sales within each category

WITH r AS (
SELECT
user_id, login_time,
ROW_NUMBER() OVER (PARTITION BY user_id
ORDER BY login_time DESC ) AS rn
FROM logins
)
SELECT user_id, login_time
FROM r
WHERE rn = 1;

Tip: Add filters (e.g., last 90 days) inside the CTE to reduce scan cost.

Chandra Venkat
 8) Nth event per entity

When to use:
2nd order date per customer
3rd login per user
Nth subscription renewal

Return the 2nd order per customer

WITH r AS (
SELECT
customer_id, order_date,
ROW_NUMBER() OVER ( PARTITION BY customer_id
ORDER BY order_date ) AS rn
FROM orders
)
SELECT customer_id, order_date
FROM r
WHERE rn = 2;

Tip: Change the `ORDER BY` to `DESC` for “Nth latest”.

Chandra Venkat
 9) Running / cumulative totals

When to use:
Cumulative revenue
Running user signups
Progressive counts

Compute running revenue over time

SELECT
dt,
revenue,
SUM(revenue) OVER (
ORDER BY dt
ROWS UNBOUNDED PRECEDING
) AS running_rev
FROM daily_revenue;

Tip: Use `PARTITION BY` to reset the running total per group.

Chandra Venkat
10) Rolling / moving window

When to use:
7-day moving average
Rolling sums by date
Sliding window metrics

Get 7-day moving average of revenue

SELECT
dt,
revenue,
AVG(revenue) OVER (
ORDER BY dt
ROWS 6 PRECEDING
) AS avg_7d
FROM daily_revenue;

Tip: For true calendar windows (not row counts),

pre-expand dates to daily grain without gaps.

Chandra Venkat
 11) Trend vs previous / next

When to use:
7-day moving average
Rolling sums by date
Sliding window metrics

Get 7-day moving average of revenue

SELECT
dt,
revenue,
revenue - LAG(revenue) OVER (ORDER BY dt) AS diff_prev,
ROUND(
100 * (revenue - LAG(revenue) OVER (ORDER BY dt))
/ NULLIF(LAG(revenue) OVER (ORDER BY dt), 0),
2
) AS pct_change
FROM daily_revenue;

Tip: Wrap the denominator with `NULLIF(...,0)` to avoid divide-by-zero.

Chandra Venkat
 12) Cohorts & retention

When to use:
Group users by signup month
Build cohort tables
High-level retention rollups

Count users per signup month (cohort)

SELECT
DATE_FORMAT(signup_dt, '%Y-%m-01') AS cohort_month,
COUNT(*) AS users
FROM users
GROUP BY cohort_month
ORDER BY cohort_month;

Tip: Use a derived table of month “buckets” to join with activity for retention matrices.

Chandra Venkat
 13) Funnel conversion (multi-stage)

When to use:
View → Add → Buy counts
Distinct user tallies per step
Stage-to-stage drop-offs

Distinct users who Viewed, Added, Purchased

SELECT
COUNT(DISTINCT CASE WHEN step='View' THEN user_id END) AS viewers,
COUNT(DISTINCT CASE WHEN step='Add' THEN user_id END) AS adders,
COUNT(DISTINCT CASE WHEN step='Purchase' THEN user_id END) AS buyers
FROM user_steps;

Tip: Prefer one tallies query over multiple joins—simpler and faster.

Chandra Venkat
14) Percent of total / contribution

When to use:
Category mix/share
% of total revenue
Composition by segment

Category share of total sales

WITH agg AS (
SELECT category, SUM(sales) AS sales
FROM category_sales
GROUP BY category
)
SELECT
category,
sales,
sales / NULLIF(SUM(sales) OVER (), 0) AS pct_of_total
FROM agg;

Tip: Pre-aggregate before dividing to avoid double counting.

Chandra Venkat
 15) De-duplication (keep latest)

When to use:
Keep 1 row per email
Dedup CRM imports
Latest snapshot per key

Keep latest row per email

WITH r AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY email
ORDER BY updated_at DESC ) AS rn
FROM users_raw
)
SELECT * FROM r
WHERE rn = 1;

Tip: If “latest” is ambiguous, add tie-breakers (e.g., `updated_at, id DESC`).

Chandra Venkat
 16) Pivot (rows → columns)

When to use:
Month columns per category
Status columns per team
One row per entity

Make months columns per category

SELECT
category,
SUM(CASE WHEN month='Jan' THEN sales ELSE 0 END) AS Jan_sales,
SUM(CASE WHEN month='Feb' THEN sales ELSE 0 END) AS Feb_sales,
SUM(CASE WHEN month='Mar' THEN sales ELSE 0 END) AS Mar_sales
FROM monthly_sales
GROUP BY category;

Tip: Keep column names short; too many pivoted columns hurt readability.
Chandra Venkat
17) Unpivot (cols → rows)

When to use:
Wide → long reshape
Report columns to rows
Normalize per-month fields

Turn Jan/Feb/Mar columns into rows

SELECT id, 'Jan' AS month, jan_sales AS sales FROM sales

UNION ALL
SELECT id, 'Feb', feb_sales FROM sales
UNION ALL
SELECT id, 'Mar', mar_sales FROM sales;

Tip: For many months, generate SQL via script or use a metadata table.
Chandra Venkat
18) Sessionization (gap > X)

When to use:
Break events into sessions
New session if gap > 30 min
Web/app analytics

Flag new session when gap > 30 min per user

SELECT
user_id, ts,
CASE
WHEN LAG(ts) OVER (PARTITION BY user_id ORDER BY ts) IS NULL
OR TIMESTAMPDIFF(
MINUTE,
LAG(ts) OVER (PARTITION BY user_id ORDER BY ts),
ts
) > 30
THEN 1 ELSE 0 END AS is_new_session FROM events;

Tip: Sum `is_new_session` over the partition to assign a session_id.

Chandra Venkat
19) Gaps / durations / inactivity

When to use:
Time between two timestamps
Idle time between events
SLAs and TATs

Days between ordered and delivered

SELECT
user_id,
order_id,
DATEDIFF(delivered_at, ordered_at) AS delivery_days
FROM orders
WHERE status = 'Delivered';

Tip: For hours/minutes, use `TIMESTAMPDIFF(unit, start, end)`

Chandra Venkat
20) Change detection across rows

When to use:
Detect status changes
Any value changed vs prior row
Emit change events

Emit rows where status changed

WITH x AS (
SELECT
ticket_id, status, ts,
LAG(status) OVER (PARTITION BY ticket_id ORDER BY ts) AS prev_status
FROM tickets
)
SELECT ticket_id, prev_status, status, ts
FROM x
WHERE (status <> prev_status)
OR (status IS NULL) <> (prev_status IS NULL);

Tip: Compare both inequality and NULL-ness for robust change detection.

Chandra Venkat
21) Streaks (islands & gaps)
When to use:
Consecutive daily logins
Runs of activity/inactivity
Start/end and length of streak
Compute login streaks per user
WITH a AS (
SELECT
user_id, login_date AS dt,
ROW_NUMBER() OVER ( PARTITION BY user_id
ORDER BY login_date ) AS rn
FROM user_logins_daily
),
b AS (
SELECT
user_id, DATE_SUB(dt, INTERVAL rn DAY) AS streak_key, dt
FROM a
)
SELECT
user_id,
MIN(dt) AS streak_start,
MAX(dt) AS streak_end,
COUNT(*) AS days_in_streak
FROM b
GROUP BY user_id, streak_key;

Tip: Ensure the input has one row per day per user (fill gaps first ifChandra
needed).
Venkat
22) Percentiles
When to use:
Tag into deciles
Pay banding by percentile

Assign deciles salary

SELECT
emp_id,
salary,
NTILE(10) OVER (ORDER BY salary DESC) AS decile
FROM employees;

Tip: Use `PERCENT_RANK()` for percentile thresholds without sorting twice.

Chandra Venkat
23) Event attribution (X before Y)
When to use:
“View before Purchase?”
First X vs first Y ordering
Basic path attribution

Users whose first View occurred before first Purchase

SELECT
user_id,
MIN(CASE WHEN event='View' THEN ts END) AS first_view,
MIN(CASE WHEN event='Purchase' THEN ts END) AS first_buy
FROM events
GROUP BY user_id
HAVING first_view IS NOT NULL
AND first_buy IS NOT NULL
AND first_view < first_buy;

Tip: Replace `MIN` with `MAX` for “last X before Y” style questions.

Chandra Venkat
 24) String aggregation (CSV per entity)
When to use:
CSV of child values per parent
Tags per post
Items per order

CSV of products per user, newest first

SELECT
user_id,
GROUP_CONCAT(
product_name
ORDER BY purchase_time DESC
SEPARATOR ','
) AS products
FROM orders
GROUP BY user_id;

Tip: Add `DISTINCT` inside `GROUP_CONCAT(DISTINCT col ...)` to remove repeats.

Chandra Venkat
25) Recursive CTE (hierarchies / sequences)
When to use:
Org tree under a manager
Category hierarchies
Generate date/number sequences
Build org tree under manager id=100

-- employees(id, name, manager_id)

WITH RECURSIVE org AS (
-- anchor (root)
SELECT id, name, manager_id, 0 AS lvl
FROM employees
WHERE id = 100
UNION ALL
-- recursive step (children)
SELECT [Link], [Link], e.manager_id, [Link] + 1
FROM employees e
JOIN org o ON e.manager_id = [Link]
WHERE [Link] < 10 -- safety stop
)
SELECT *
FROM org
ORDER BY lvl, id;

Tip: Always include a stop condition (by level or count) to prevent runaway recursion.
Chandra Venkat
Want the full SQL Interview Kit (100+ questions)?
Built on these core SQL patterns.

Comment/DM SQL KIT - I’ll DM you free access.

Closes Tue, Sep 9, 2025 EOD

Follow for more practical data tips.

Chandra Venkat