DEV Community: Vivek Kumar

KPI Tracking with SQL: A Practical Starter Kit for SaaS Developers

Vivek Kumar — Fri, 22 May 2026 04:50:54 +0000

You've shipped the product. Users are signing up. Subscriptions are flowing in. Now your founder, your investors, or your own curiosity is asking: how are we actually doing?

Most teams reach for a BI tool or a spreadsheet at this point. But if your data lives in a SQL database—Postgres, MySQL, Redshift, BigQuery—you already have everything you need to answer the most important product and business questions. No third-party analytics event pipeline required.

This guide gives you practical SQL queries for the KPIs that matter most in a SaaS product: revenue, engagement, retention, and growth. These aren't toy examples—they use realistic table names you'd find in a real product codebase, and they come with the gotchas that trip most developers up the first time.

Your Assumed Schema

To keep the queries grounded, here's the database schema we'll work against throughout:

Table	Key Columns
`users`	`id`, `email`, `created_at`, `plan_id`
`subscriptions`	`id`, `user_id`, `status`, `amount_cents`, `billing_cycle`, `started_at`, `canceled_at`
`payments`	`id`, `user_id`, `amount_cents`, `paid_at`, `status`
`events`	`id`, `user_id`, `event_name`, `occurred_at`
`trials`	`id`, `user_id`, `started_at`, `converted_at`

Adapt column names to match your own schema, but the patterns will transfer directly.

1. Monthly Recurring Revenue (MRR)

MRR is the single most-watched number in any subscription business. It answers: how much predictable revenue do we have right now?

SELECT
  DATE_TRUNC('month', CURRENT_DATE) AS month,
  SUM(amount_cents) / 100.0          AS mrr
FROM subscriptions
WHERE status = 'active'
  AND billing_cycle = 'monthly';

If you have annual subscribers, normalize their value to monthly by dividing by 12:

SELECT
  SUM(
    CASE billing_cycle
      WHEN 'monthly' THEN amount_cents
      WHEN 'annual'  THEN amount_cents / 12
    END
  ) / 100.0 AS mrr
FROM subscriptions
WHERE status = 'active';

Gotcha: Don't double-count. If a user can have multiple subscriptions (e.g. per-seat billing), make sure your subscriptions table represents the correct unit. Summing at the user level first and then aggregating is safer when your data model is complex.

2. Churn Rate

Churn tells you how fast you're losing customers. Monthly churn is typically calculated as:

Churn Rate = Customers Lost This Month ÷ Customers at Start of Month

WITH start_of_month AS (
  SELECT COUNT(DISTINCT user_id) AS total
  FROM subscriptions
  WHERE status = 'active'
    AND started_at < DATE_TRUNC('month', CURRENT_DATE)
),
churned AS (
  SELECT COUNT(DISTINCT user_id) AS lost
  FROM subscriptions
  WHERE status = 'canceled'
    AND canceled_at >= DATE_TRUNC('month', CURRENT_DATE)
    AND canceled_at  < DATE_TRUNC('month', CURRENT_DATE) + INTERVAL '1 month'
)
SELECT
  ROUND(lost * 100.0 / NULLIF(total, 0), 2) AS monthly_churn_pct
FROM start_of_month, churned;

Gotcha: Using NULLIF(total, 0) prevents division-by-zero errors, which will otherwise silently crash your query or return NULL without warning. A 2–3% monthly churn rate is a common benchmark to aim below; above 5% is a red flag for most B2B SaaS.

3. Daily Active Users (DAU) and the Stickiness Ratio

DAU tells you engagement. But the ratio of DAU to MAU—your "stickiness ratio"—tells you whether users are building habits.

-- DAU: today
SELECT
  COUNT(DISTINCT user_id) AS dau
FROM events
WHERE occurred_at::date = CURRENT_DATE;

-- MAU: last 30 days
SELECT
  COUNT(DISTINCT user_id) AS mau
FROM events
WHERE occurred_at >= CURRENT_DATE - INTERVAL '30 days';

Combine them to get the stickiness ratio:

WITH dau AS (
  SELECT COUNT(DISTINCT user_id) AS daily
  FROM events
  WHERE occurred_at::date = CURRENT_DATE
),
mau AS (
  SELECT COUNT(DISTINCT user_id) AS monthly
  FROM events
  WHERE occurred_at >= CURRENT_DATE - INTERVAL '30 days'
)
SELECT
  ROUND(daily * 100.0 / NULLIF(monthly, 0), 1) AS stickiness_pct
FROM dau, mau;

A stickiness ratio above 20% is generally considered healthy. Slack and Facebook sit north of 50%—most B2B tools are happy at 25–40%.

Gotcha: Be precise about what counts as an "active" event. Including page loads inflates the number. Stick to intentional actions: creating a record, running a query, submitting a form.

4. Average Revenue Per User (ARPU)

ARPU helps you understand whether your pricing is working and how different segments compare.

SELECT
  ROUND(
    SUM(amount_cents) / 100.0 / NULLIF(COUNT(DISTINCT user_id), 0),
    2
  ) AS arpu
FROM subscriptions
WHERE status = 'active';

Break it down by plan to see which tier drives the most value:

SELECT
  u.plan_id,
  COUNT(DISTINCT s.user_id)                             AS customers,
  ROUND(AVG(s.amount_cents) / 100.0, 2)                AS avg_revenue
FROM subscriptions s
JOIN users u ON u.id = s.user_id
WHERE s.status = 'active'
GROUP BY u.plan_id
ORDER BY avg_revenue DESC;

5. Trial-to-Paid Conversion Rate

If you have a freemium or free trial model, conversion rate is often more valuable than new signup counts—it's what actually drives revenue.

WITH trial_cohort AS (
  SELECT COUNT(DISTINCT user_id) AS started
  FROM trials
  WHERE started_at >= CURRENT_DATE - INTERVAL '30 days'
),
converted AS (
  SELECT COUNT(DISTINCT user_id) AS paid
  FROM trials
  WHERE started_at >= CURRENT_DATE - INTERVAL '30 days'
    AND converted_at IS NOT NULL
)
SELECT
  ROUND(paid * 100.0 / NULLIF(started, 0), 1) AS conversion_pct
FROM trial_cohort, converted;

Gotcha: Be careful comparing trials from this month to conversions from this month. Many trials take 14–30 days to convert. A better approach is to look at the conversion rate for trials that started 30–60 days ago, giving them time to complete their journey.

6. Revenue Growth Rate (Month-over-Month)

Revenue growth tells you if the business is accelerating, stalling, or shrinking. Track it month-over-month.

WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', paid_at) AS month,
    SUM(amount_cents) / 100.0    AS revenue
  FROM payments
  WHERE status = 'succeeded'
  GROUP BY 1
)
SELECT
  month,
  revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY month)) * 100.0
    / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
    1
  ) AS mom_growth_pct
FROM monthly_revenue
ORDER BY month DESC
LIMIT 6;

This query uses a window function (LAG) to pull the previous month's value and compute the percentage change. The result looks something like:

month	revenue	prev_month_revenue	mom_growth_pct
2026-05-01	48,200	41,500	16.1%
2026-04-01	41,500	36,800	12.8%
2026-03-01	36,800	34,100	7.9%

7. Feature Adoption Rate

If you're building a product, you need to know which features people actually use.

SELECT
  event_name                                                AS feature,
  COUNT(DISTINCT user_id)                                   AS users_used,
  (SELECT COUNT(DISTINCT user_id) FROM events
   WHERE occurred_at >= CURRENT_DATE - INTERVAL '30 days') AS total_active_users,
  ROUND(
    COUNT(DISTINCT user_id) * 100.0 /
    NULLIF(
      (SELECT COUNT(DISTINCT user_id) FROM events
       WHERE occurred_at >= CURRENT_DATE - INTERVAL '30 days'),
      0
    ),
    1
  )                                                         AS adoption_pct
FROM events
WHERE occurred_at >= CURRENT_DATE - INTERVAL '30 days'
  AND event_name IN ('export_report', 'invite_teammate', 'create_dashboard', 'connect_datasource')
GROUP BY event_name
ORDER BY adoption_pct DESC;

Common Mistakes to Avoid

1. Forgetting time zones. CURRENT_DATE returns a date in your server's time zone. If your users are spread across time zones, you may be cutting off events. Store timestamps in UTC and always convert explicitly.

2. Counting users vs. counting events. Many queries should use COUNT(DISTINCT user_id), not COUNT(*). One user generating 100 events should not count as 100 active users.

3. Including test accounts. Seed data, internal team emails, and test subscriptions will skew every metric. Add a WHERE email NOT LIKE '%@yourcompany.com%' filter or flag test accounts in your users table with an is_test column.

4. Pulling raw SQL into spreadsheets. Once your KPI queries are working, don't copy-paste results into a spreadsheet every week. That workflow breaks. Connect a dashboard tool directly to your database to keep numbers fresh and reduce manual error.

5. Ignoring failed payments. When calculating MRR from payments, always filter by status = 'succeeded'. Failed payments are not revenue, and including them will inflate your numbers.

Key Takeaways

SQL is one of the most powerful tools you have for understanding your own product. You don't need a data warehouse or a dedicated analyst to get started—you need a database connection and the right queries.

Start with these seven metrics: MRR, churn rate, DAU/MAU stickiness, ARPU, trial conversion, revenue growth, and feature adoption. Each one answers a specific question about whether your product is healthy and growing.

Build them once, pin them to a dashboard you check weekly, and you'll make better product decisions with far less guesswork.

How do you track KPIs at your company? Do you query your database directly, use a BI tool, or pull from a third-party analytics service? Drop your setup in the comments — I'd love to hear what works for your team.

From English to SQL: How LLMs Actually Understand Your Database Schema

Vivek Kumar — Thu, 21 May 2026 05:28:35 +0000

You type "show me total revenue by customer for last month" into a chat interface. A second later, out pops a perfectly formatted SQL query against your orders and customers tables. It feels like magic.

But it's not. And once you understand the mechanics — specifically, how the model reads and interprets your database schema — you'll know exactly why text-to-SQL tools sometimes get it right, sometimes get it embarrassingly wrong, and what you can do to tip the odds in your favor.

This post breaks down the internal mechanics of schema understanding in LLM-powered text-to-SQL systems, with practical advice for developers building or integrating these tools.

The First Problem: The LLM Doesn't Know Your Database

Out of the box, an LLM has no idea that your SaaS product has a subscriptions table, or that mrr lives in billing_events, or that you store user_id in lowercase snake_case everywhere except the one legacy table where it's UserID.

That knowledge has to be injected into the model's prompt — every single time. The process of selecting which schema information to include, and how to format it, is called schema linking. It's the most critical (and most commonly fumbled) part of any text-to-SQL pipeline.

A typical schema injection looks like this:

-- Passed to the LLM in the system or user prompt:

Table: users
Columns: id (integer, PK), email (text), created_at (timestamp), plan (text)

Table: orders
Columns: id (integer, PK), user_id (integer, FK → users.id), amount_cents (integer), status (text), created_at (timestamp)

Table: subscriptions
Columns: id (integer, PK), user_id (integer, FK → users.id), plan_id (integer), started_at (timestamp), cancelled_at (timestamp, nullable)

The model reads this and builds an internal understanding of your data model — which tables exist, what columns they have, how they relate to each other, and what data types to expect.

Step 1: Schema Parsing — What the Model Actually Sees

When a user asks "which customers churned last month?", the LLM doesn't just search for the word "churned" in your schema. It reasons through what "churned" likely means given the available columns: probably a cancelled_at timestamp that falls within the last 30 days, joined against users via subscriptions.

This reasoning — connecting semantic intent in the question to structural elements in the schema — is called schema linking. It involves three sub-problems:

1. Table selection: Which tables are relevant to this question? For large databases with hundreds of tables, the model must filter down to the handful that matter. Dumping your entire schema into the prompt doesn't scale — a 300-table database easily exceeds a model's effective context window.

2. Column selection: Within those tables, which columns are needed? This is where naming matters enormously. amt is ambiguous. amount_cents is clear. ts is useless. created_at is useful.

3. Join inference: How do the relevant tables connect? The model needs to reconstruct the relationship graph — ideally from explicit foreign key declarations in the schema you provide, rather than guessing from column name patterns alone.

Step 2: Why Column Names and Table Names Matter More Than You Think

Here's a test. Give an LLM these two schemas and ask "how many users signed up last week?"

Schema A:

Table: u
Columns: uid, em, ts, pl

Schema B:

Table: users
Columns: id, email, created_at, plan

Schema B wins every time. Natural language tokens in column names (email, created_at, plan) map cleanly to words a model understands. Abbreviations break that mapping.

Research from IBM and others has shown that schema-aware models — those given rich metadata including table descriptions, column definitions, and sample values — outperform models handed raw DDL by a significant margin.

The practical takeaway: invest in good naming conventions, and pass them into your text-to-SQL pipeline explicitly. If you're stuck with legacy short column names, add descriptions.

Step 3: How to Structure Schema for Maximum LLM Comprehension

Here's an example of a well-formatted schema prompt that a text-to-SQL system might use:

You are a SQL expert. Use the following schema to answer the user's question.

### Schema

Table: users
Description: One row per registered user.
Columns:
  - id (integer, primary key)
  - email (text) — the user's login email
  - created_at (timestamp) — when the account was created
  - plan (text) — current plan: 'free', 'pro', 'enterprise'

Table: orders
Description: Purchases made by users.
Columns:
  - id (integer, primary key)
  - user_id (integer) — foreign key → users.id
  - amount_cents (integer) — order total in cents (divide by 100 for dollars)
  - status (text) — 'pending', 'completed', 'refunded'
  - created_at (timestamp)

### Question
Which users on the 'pro' plan have made more than 3 orders in the past 30 days?

Notice what's included beyond just column names:

Table descriptions that explain business purpose
Inline comments on ambiguous columns (units, enumerations)
Explicit foreign key relationships spelled out in plain language

This is the difference between a mediocre text-to-SQL result and one you'd actually run in production.

Step 4: The Large Schema Problem — When You Can't Fit Everything

Most real-world databases have far more tables than you'd ever want in a single prompt. Cramming 200 table definitions into the context window creates two problems: cost, and accuracy. Models are measurably worse at attending to schema details when surrounded by irrelevant tables.

The solution is retrieval-augmented schema selection. Before calling the LLM, a retrieval step filters the schema:

# Pseudocode for schema-aware RAG in a text-to-SQL pipeline

user_question = "show me monthly revenue by plan for the last 6 months"

# 1. Embed the user question
question_embedding = embed(user_question)

# 2. Search a vector index of table + column descriptions
relevant_tables = vector_search(
    index=schema_index,
    query=question_embedding,
    top_k=5
)

# 3. Build a focused schema prompt with only relevant tables
schema_context = format_schema(relevant_tables)

# 4. Call the LLM with a focused prompt
sql = llm.complete(
    system_prompt=f"Use this schema:\n{schema_context}",
    user_prompt=user_question
)

Systems like RASL (Retrieval Augmented Schema Linking) from Amazon use this approach to handle databases with thousands of tables, achieving dramatically better accuracy than naive full-schema injection.

The key insight: the right schema in the prompt beats a larger model every time.

Step 5: Foreign Keys Are the Secret Weapon

Joins are where text-to-SQL models fail most visibly. A model might correctly identify that you need both users and orders, then produce a broken query because it guessed the wrong join column.

The fix is explicit: always include foreign key relationships in your schema prompt. Compare these two approaches:

Vague:

Table: orders
Columns: id, user_id, amount_cents, created_at

Explicit:

Table: orders
Columns:
  - id (integer, primary key)
  - user_id (integer) — foreign key → users.id; never null
  - amount_cents (integer)
  - created_at (timestamp)

When foreign keys are declared, models reliably produce correct JOINs:

-- Generated correctly when FK is explicit:
SELECT 
  u.email,
  COUNT(o.id) AS order_count,
  SUM(o.amount_cents) / 100.0 AS total_revenue
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed'
  AND o.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.email
ORDER BY total_revenue DESC;

Without the FK hint, you're relying on the model to guess that user_id in orders maps to id in users. It usually gets this right — but "usually" isn't good enough for production.

Common Mistakes Developers Make

Passing raw DDL without descriptions. Your CREATE TABLE statements are a start, but they're optimized for the database engine, not for an LLM. Add comments and descriptions.

-- Don't just pass this:
CREATE TABLE ev (
  id SERIAL PRIMARY KEY,
  uid INT,
  typ VARCHAR(50),
  ts TIMESTAMPTZ
);

-- Also include:
-- Table: events (alias: ev)
-- Description: user activity events for product analytics
-- uid → users.id, typ = 'click' | 'page_view' | 'signup' | 'purchase'

Including every table for every query. Token-bloat hurts accuracy. Filter schema by relevance before injecting.

Ignoring enum values. If a column like status has a fixed set of values, list them. The model needs to know that 'completed' is a valid status, not 'done' or 'success'.

No sample data. Even a single example row per table dramatically improves accuracy for edge cases — especially date formats, unit conventions, and enum spellings.

Key Takeaways

The gap between a text-to-SQL tool that impresses and one you'd trust in production almost always comes down to schema quality, not model quality. Here's the short version:

Use descriptive table and column names; avoid abbreviations
Declare foreign key relationships explicitly in your schema prompt
Include column descriptions for ambiguous fields (units, enums, nullable meaning)
Filter schema by relevance using vector search for large databases
Add sample values or enum lists for categorical columns
Never assume the model will infer what you didn't tell it

The LLM is doing surprisingly sophisticated reasoning — it's matching your English words to your schema structure, inferring joins, and constructing syntactically valid SQL all in one pass. Give it the right schema context, and it'll give you queries you can actually use.

Have you built a text-to-SQL system or integrated one into your product? I'd love to hear how you handle schema injection — drop a comment below with your approach, especially if you've tackled the large-schema problem in an interesting way.

How to Prompt AI Tools to Write Accurate SQL Queries (And Why Most Developers Get This Wrong)

Vivek Kumar — Wed, 20 May 2026 17:03:43 +0000

If you've tried asking ChatGPT, Claude, or any AI SQL assistant to generate a query and gotten back something that looked plausible but was subtly wrong — you're not alone. The frustrating part is it often runs. The database returns rows, the numbers look reasonable, and you ship it. Three days later, someone points out the totals are off by 20%.

The problem isn't the AI. The problem is the prompt.

Text-to-SQL works remarkably well when you give the model what it actually needs. According to AWS's benchmarking, GPT-4-class models achieve a 94% first-try success rate on ad-hoc analytics queries when the schema and foreign key constraints are properly provided. Without that context? You're closer to 60%. The difference is entirely in how you prompt.

This guide covers the practical techniques — schema context, few-shot examples, business term definitions, and chain-of-thought decomposition — that separate accurate AI-generated SQL from the kind that silently lies to you.

Why AI Gets SQL Wrong (It's Not the Model's Fault)

When you ask an AI "give me last month's revenue by plan tier," the model has to make a series of guesses:

Which table holds revenue? orders? subscriptions? invoices?
What column tracks the amount? total? amount_cents? mrr?
What does "last month" mean in your timezone?
Is "revenue" recognized revenue, gross, or net of refunds?
What column stores "plan tier"?

Without your schema, the model invents plausible-sounding answers to all of these. It will write syntactically valid SQL that is semantically wrong for your specific database.

The key insight: From the model's perspective, the schema is the problem space. You wouldn't ask a contractor to remodel your kitchen without giving them the floor plan.

Technique 1: Always Include Your Schema (But Not All of It)

The most impactful single change you can make is providing your table definitions in the prompt.

The naive approach — dumping your entire database schema — backfires. Enterprise databases with 100+ tables drown the model in noise and push important tables out of its effective context window. The right move is to include only the tables relevant to the question.

Here's a solid schema context block to include in your prompt:

You are a SQL expert working with a PostgreSQL database.

Relevant tables:

CREATE TABLE subscriptions (
  id          SERIAL PRIMARY KEY,
  user_id     INTEGER REFERENCES users(id),
  plan        TEXT,         -- 'starter', 'pro', 'enterprise'
  status      TEXT,         -- 'active', 'cancelled', 'trialing'
  mrr_cents   INTEGER,      -- monthly recurring revenue in cents
  started_at  TIMESTAMPTZ,
  cancelled_at TIMESTAMPTZ
);

CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  email       TEXT,
  created_at  TIMESTAMPTZ,
  country     TEXT
);

Write a SQL query to: [your question here]

Notice the inline comments on columns like plan and status. These are crucial — they tell the model the exact set of values to filter on. Without them, the model might write WHERE plan = 'professional' instead of WHERE plan = 'pro'.

Practical tip: If you don't know which tables are relevant upfront, ask the model first: "Given this question, which tables from the following list would you need?" Then provide only those.

Technique 2: Define Your Business Terms

Business vocabulary rarely maps cleanly to column names. "Revenue" might mean mrr_cents. "Active users" might mean users who logged in within 30 days, not users with status = 'active'. "Churn" could be calculated six different ways.

Add a glossary block to your prompt for any domain-specific terms:

Business term definitions:
- "Active subscriber": a user with subscriptions.status = 'active'
- "MRR": SUM(mrr_cents) / 100.0, expressed in dollars
- "Last month": the full calendar month before the current month
  (e.g., if today is May 15 2026, last month = April 1–30 2026)
- "Churned": status changed from 'active' to 'cancelled' in the period

This is the step most developers skip. And it's the reason queries that look right return numbers that don't match your finance team's spreadsheet.

Technique 3: Use Few-Shot Examples

Few-shot prompting — showing the model one or two example question/query pairs — dramatically improves accuracy on complex queries. It teaches the model your style, your naming conventions, and the kinds of JOINs your schema requires.

Here are two example queries to guide your output:

-- Question: How many new subscribers signed up in March 2026?
-- Query:
SELECT COUNT(*)
FROM subscriptions
WHERE started_at >= '2026-03-01'
  AND started_at < '2026-04-01';

-- Question: What is total MRR by plan for active subscriptions?
-- Query:
SELECT
  plan,
  SUM(mrr_cents) / 100.0 AS mrr_dollars
FROM subscriptions
WHERE status = 'active'
GROUP BY plan
ORDER BY mrr_dollars DESC;

-- Now answer this question: [your new question]

The examples serve as a template. The model learns that you use >= / < for date ranges (not BETWEEN), that you divide cents by 100.0, and that you prefer ORDER BY descending. You'll get output that fits into your codebase without needing cleanup.

Technique 4: Break Complex Questions into Steps (Chain-of-Thought)

For multi-step analytical queries — cohort analysis, funnel calculations, retention — don't ask for the whole query at once. Ask the model to reason through it first.

I need to calculate 30-day retention for users who signed up in April 2026.
Retention means: they had at least one active session in the 30-day window
after signup (tracked in the `events` table with event_type = 'session_start').

Before writing the SQL:
1. What intermediate steps are needed?
2. Which tables will you join?
3. How will you define the 30-day window?

Then write the final query.

When you ask the model to think through the steps explicitly, it catches its own mistakes before producing the final SQL. This mirrors what an experienced developer does mentally before writing a complex query. The chain-of-thought approach consistently outperforms direct generation on multi-table, multi-condition queries.

Here's what the output might look like for that retention query:

WITH april_signups AS (
  SELECT id, created_at
  FROM users
  WHERE created_at >= '2026-04-01'
    AND created_at < '2026-05-01'
),
retained AS (
  SELECT DISTINCT u.id
  FROM april_signups u
  JOIN events e ON e.user_id = u.id
    AND e.event_type = 'session_start'
    AND e.occurred_at >= u.created_at + INTERVAL '1 day'
    AND e.occurred_at <= u.created_at + INTERVAL '30 days'
)
SELECT
  COUNT(DISTINCT a.id)                        AS total_signups,
  COUNT(DISTINCT r.id)                        AS retained_users,
  ROUND(
    COUNT(DISTINCT r.id)::NUMERIC /
    NULLIF(COUNT(DISTINCT a.id), 0) * 100, 1
  )                                           AS retention_pct
FROM april_signups a
LEFT JOIN retained r ON r.id = a.id;

A direct one-shot prompt rarely produces something this structurally sound. Decomposing the problem does.

Technique 5: Ask for Explanation and Validation

After receiving a query, always ask the model to explain it:

Explain what this query does, step by step.
Are there any edge cases (nulls, division by zero, time zone assumptions)
that could return incorrect results?

This serves two purposes: it helps you catch logical errors before running the query in production, and it forces the model to re-examine its own output. Models often catch their own mistakes during this review pass.

For example, the model might flag:

"This query uses CURRENT_DATE which assumes UTC. If your database runs in a different timezone, you may want CURRENT_DATE AT TIME ZONE 'America/New_York'."
"If mrr_cents is NULL for any rows, SUM() will silently exclude them. You may want COALESCE(mrr_cents, 0)."

These are exactly the gotchas that cause silent data quality issues.

Common Mistakes to Avoid

Vague questions. "Show me user activity" will produce a vague, probably useless query. "Show me the count of distinct users who triggered at least one purchase event in the last 7 days, grouped by their plan" will produce something accurate and useful.

No schema, but expecting column-level accuracy. The model will guess, and its guesses will be syntactically valid. That's what makes them dangerous.

Ignoring dialect differences. If you're on BigQuery, asking a generic model for date functions might get you DATE_TRUNC when you need DATE_TRUNC(date, MONTH). Always tell the model your database flavor (PostgreSQL, MySQL, BigQuery, Snowflake, etc.).

Trusting output that executes without errors. A query that runs is not a query that's correct. Always sanity-check with a small sample (LIMIT 100), compare a few rows to known-good data, and check totals against a source of truth.

Not iterating. The first query you get back is a first draft. Paste it back in with corrections: "This is close, but I need the results grouped by week, not month, and I only want users in the 'pro' plan." Iteration gets you to accuracy faster than trying to write the perfect prompt on the first try.

Key Takeaways

The gap between 60% and 94% AI SQL accuracy comes down to what you put into the prompt:

Include your schema — just the relevant tables, with column comments for values
Define business terms — don't let the model guess what "revenue" or "active" means
Add 1–2 example queries — establish your style and naming conventions
Decompose complex questions — ask for reasoning steps before the final query
Ask for explanation and edge-case review — let the model catch its own mistakes
Always specify your database flavor — PostgreSQL, MySQL, BigQuery, etc.

AI-powered SQL generation is genuinely useful once you treat prompting as a craft, not an afterthought. The developers who get the most out of these tools aren't the ones using the fanciest models — they're the ones who've learned to give the model what it actually needs to succeed.

What techniques have worked well for you when prompting AI for SQL? Have you found ways to handle large, complex schemas effectively? Drop your approach in the comments — I'd love to compare notes.

Multi-Tenant SQL Reporting: How to Show Each Customer Only Their Own Data

Vivek Kumar — Wed, 13 May 2026 18:43:48 +0000

You're three months into your SaaS. Customers want dashboards. The first one is easy — Acme Corp wants to see their orders, their signups, their revenue. You write a quick WHERE tenant_id = 42 and ship it.

Six months later you have 400 tenants, 30 reports, an AI assistant that writes SQL on the fly, and an engineer who one Tuesday morning forgets the WHERE clause on a single query. Now Acme Corp can see Globex's data. That's a Monday-morning headline you don't want.

This article is about how to architect multi-tenant SQL reporting so a single forgotten clause never becomes a breach. We'll cover tenant schema design, Postgres Row-Level Security, how to wire it to JWTs, indexing for performance, and the gotchas that bite people in production.

The three multi-tenant patterns (and why one wins for reporting)

Before any SQL, pick your isolation model. There are three classic options:

Pattern	Isolation	Cost	Best for
Database-per-tenant	Strongest	Highest (ops, migrations, connections)	Enterprise, regulated industries
Schema-per-tenant	Strong	High (schemas multiply fast)	Mid-market with <1000 tenants
Shared schema with tenant_id	Logical	Lowest	Most B2B SaaS

For 90% of SaaS apps, shared schema with a tenant_id column is the right call. It's cheap, it scales, and Postgres gives you the tools to make it safe. Everything below assumes this pattern.

Step 1: Tag every row with a tenant_id

Every table that stores customer data needs a tenant discriminator. No exceptions. Even your events, audit_logs, and ai_query_history tables.

CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,
  tenant_id    BIGINT NOT NULL REFERENCES tenants(id),
  customer_id  BIGINT NOT NULL,
  amount_cents INT NOT NULL,
  status       TEXT NOT NULL,
  created_at   TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX orders_tenant_created_idx
  ON orders (tenant_id, created_at DESC);

Two things to notice: tenant_id is NOT NULL (a null tenant means an orphan row that bypasses your filters), and the index leads with tenant_id. Every reporting query will filter by tenant, so this becomes your bread-and-butter index.

Step 2: Enable Row-Level Security as a safety net

Application-level filtering is fine until someone forgets. Postgres Row-Level Security (RLS) enforces the filter at the database, so even a raw SELECT * FROM orders returns only the rows the current tenant is allowed to see.

-- 1. Turn on RLS
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 2. Define the policy
CREATE POLICY tenant_isolation ON orders
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::BIGINT)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::BIGINT);

The USING clause filters rows you can read; the WITH CHECK clause prevents you from inserting or updating rows into another tenant. Together they cover both directions.

Now every connection has to set app.tenant_id before issuing queries:

SET LOCAL app.tenant_id = '42';
SELECT count(*) FROM orders;  -- automatically scoped to tenant 42

SET LOCAL ties the setting to the current transaction, so it can't leak across requests on a pooled connection — important.

Step 3: Wire the tenant context to your auth layer

The setting has to come from a trusted source. The standard pattern is to extract it from a verified JWT:

# Pseudocode — runs on every request inside a transaction
claims = jwt.decode(request.token, public_key)
tenant_id = claims["tenant_id"]

with db.transaction() as tx:
    tx.execute("SET LOCAL app.tenant_id = %s", [tenant_id])
    # All queries inside this transaction are now tenant-scoped
    run_report(tx)

Two non-negotiables:

Decode the JWT server-side with a verified signature. Never trust a tenant_id coming from a query parameter or request body.
Use SET LOCAL, not SET. Connection pools recycle connections; SET persists, SET LOCAL doesn't.

Step 4: Reporting queries that "just work"

With RLS on, your reporting queries get simpler — you don't write WHERE tenant_id = ? in every query. Compare:

-- Before RLS — you must remember every time
SELECT date_trunc('day', created_at) AS day,
       count(*) AS orders,
       sum(amount_cents) / 100.0 AS revenue
FROM orders
WHERE tenant_id = $1
  AND created_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;

-- After RLS — same query, scoped automatically
SELECT date_trunc('day', created_at) AS day,
       count(*) AS orders,
       sum(amount_cents) / 100.0 AS revenue
FROM orders
WHERE created_at >= now() - interval '30 days'
GROUP BY 1
ORDER BY 1;

This matters most for AI-generated SQL and user-defined reports. If an AI assistant writes a query against your warehouse, you don't want safety to depend on whether it remembered the tenant filter. RLS makes the database itself the last line of defense.

Step 5: Handle cross-tenant admin queries explicitly

Eventually you'll want internal queries that span tenants — billing rollups, churn cohorts, support tooling. Don't disable RLS; create a separate admin role:

CREATE ROLE app_admin;
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

CREATE POLICY admin_all_access ON orders
  FOR ALL
  TO app_admin
  USING (true);

FORCE ROW LEVEL SECURITY makes RLS apply even to the table owner, which prevents your migrations user from accidentally reading everyone's data. The app_admin role has its own explicit policy.

Indexing for tenant-scoped queries

A common surprise: after adding RLS, queries that used to be fast get slow. The fix is almost always indexes that lead with tenant_id.

-- Good — supports the most common reporting pattern
CREATE INDEX orders_tenant_status_created_idx
  ON orders (tenant_id, status, created_at DESC);

-- Also useful for tenant-scoped joins
CREATE INDEX events_tenant_user_idx
  ON events (tenant_id, user_id, created_at DESC);

Rule of thumb: any index you'd create on a single-tenant table, prefix with tenant_id for a multi-tenant one. Postgres can then satisfy both the RLS predicate and your ORDER BY/filter from the same index.

Common gotchas

Here's what bites people in production:

Forgetting to set app.tenant_id. If RLS is on and the setting isn't set, queries return zero rows — not an error. Wrap your transaction setup so missing context throws loudly in development.

Using SET instead of SET LOCAL. On a pooled connection, the next request inherits the previous tenant's context. This is a real, observed bug — and a nasty one because the symptom is "wrong customer's data shown."

RLS on the parent table but not on partitions. Partitioned tables need RLS enabled on each partition, or queries skip the policy entirely. Easy to miss.

Cross-tenant joins. A query like SELECT * FROM orders o JOIN users u ON o.user_id = u.id works fine if both tables have RLS. But if users doesn't, you can leak data through the join. Enable RLS on every tenant-scoped table — not just the ones you think are sensitive.

Bypassing RLS for views. Views run with the privileges of their creator by default. Use CREATE VIEW … WITH (security_invoker = true) (Postgres 15+) so RLS evaluates against the calling user, not the view owner.

No tenant_id on derived tables. Materialized views and reporting tables get rebuilt nightly and someone forgets the tenant column. Now your "fast dashboard" returns everyone's data. Always carry tenant_id through.

Key takeaways

For multi-tenant SQL reporting, shared schema with tenant_id is the pragmatic default. Row-Level Security turns that filter from a convention you have to remember into a constraint the database enforces. Pair it with JWT-driven session context, indexes that lead with tenant_id, and an explicit admin role for cross-tenant queries.

The payoff is that your reporting layer — whether you build it yourself, embed a third-party dashboard, or let an AI assistant write queries on top of your schema — becomes safe by default. The database itself refuses to leak data, no matter what query lands on it.

Over to you

How are you handling tenant isolation in your reporting layer today — RLS, application filters, schema-per-tenant, or something else? Have you hit a multi-tenant gotcha that wasn't on this list? Drop it in the comments — I'd love to compare notes.

Don't Trust AI-Generated SQL Blindly: A Developer's Validation Checklist

Vivek Kumar — Tue, 12 May 2026 09:44:13 +0000

AI coding assistants can knock out a SQL query in seconds. You describe what you want in plain English, and out comes a SELECT statement that looks completely reasonable. So you copy it, paste it into your app or dashboard query runner, and hit execute.

This is where things go wrong.

Unlike a syntax error that crashes loudly, a semantically wrong SQL query runs successfully and returns a result. The result looks plausible. You ship the dashboard. A week later, someone notices the revenue numbers are 15% off because the AI joined on the wrong foreign key. The silent failure is the dangerous one.

This post is a practical checklist for validating AI-generated SQL before it touches your production database — whether you're building a reporting feature, an embedded dashboard, or using a text-to-SQL tool internally.

Why AI Gets SQL Wrong

Large language models generate SQL by predicting likely token sequences based on training data. They don't execute the query or check your actual schema — they hallucinate a plausible-looking result. The four failure patterns that show up most often are:

1. Schema hallucination — the model invents column names that don't exist, or references the right column from the wrong table.

2. Faulty joins — missing ON clauses (producing accidental Cartesian products), wrong join keys, or incorrect join types (LEFT vs INNER).

3. Aggregation mistakes — applying COUNT or SUM to the wrong column, forgetting GROUP BY columns, or confusing WHERE (row-level) with HAVING (group-level).

4. Missing filters — dropping a WHERE clause entirely, or missing a tenant/user scoping condition that you absolutely need for correctness and security.

A 2026 benchmark study found that even top models only achieve ~78% execution accuracy on zero-shot text-to-SQL tasks. That means roughly 1 in 5 queries is wrong — often in ways that won't throw an error.

The Validation Checklist

✅ 1. Check Every Column Against Your Actual Schema

Before anything else, look up every column name in the generated query against your real schema. Don't assume — AI models frequently confuse similar column names like user_id vs account_id, or reference created_at on a table that uses created_date.

-- AI generated this
SELECT u.name, o.total_amount
FROM users u
JOIN orders o ON u.user_id = o.user_id
WHERE o.status = 'completed';

-- Your schema actually uses:
-- users.id (not users.user_id)
-- orders.customer_id (not orders.user_id)
-- orders.amount (not orders.total_amount)

Result if run as-is: either a syntax error (best case) or a silent Cartesian product (worst case). The fix is one minute of schema cross-referencing.

✅ 2. Trace Every JOIN Condition

For each JOIN, ask yourself:

Is the ON condition using the actual primary key → foreign key relationship?
Is the join type (INNER, LEFT, RIGHT) what you actually want?
Could this produce duplicate rows?

-- Potentially dangerous: AI used INNER JOIN, dropping users without orders
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
INNER JOIN orders o ON u.id = o.customer_id
GROUP BY u.name;

-- Correct if you want ALL users, including those with zero orders:
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.customer_id
GROUP BY u.name;

The INNER JOIN version silently excludes users with no orders. If you're building a "user activity" report, that's a significant data gap.

✅ 3. Verify Aggregation Logic

Check that:

GROUP BY includes all non-aggregated columns in SELECT
SUM and COUNT are applied to the right columns
HAVING is used for filtering aggregated results (not WHERE)

-- Bug: filtering on an aggregate in WHERE (will error in most databases)
SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE SUM(amount) > 1000
GROUP BY customer_id;

-- Correct:
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 1000;

AI tools make this exact mistake surprisingly often, especially when you phrase the question as "show me customers who spent more than $1000."

✅ 4. Check for Missing Tenant/User Scoping

If you're building a multi-tenant SaaS app or any system where different users should only see their own data, this is the most critical check. AI tools have no idea your events table contains data from 500 different customers.

-- AI generates this when asked "show me top pages by view count"
SELECT page_url, COUNT(*) AS views
FROM page_events
GROUP BY page_url
ORDER BY views DESC
LIMIT 10;

-- What you actually need in a multi-tenant app:
SELECT page_url, COUNT(*) AS views
FROM page_events
WHERE account_id = :current_account_id  -- ← THIS is what the AI forgot
GROUP BY page_url
ORDER BY views DESC
LIMIT 10;

Running the AI version against production would expose every customer's data to every other customer. Always add tenant scoping filters as a mandatory review step.

✅ 5. Run Against a Staging Database First

Never run an untested AI-generated query directly on production — especially anything that writes data. For SELECT queries, use a staging copy of your database and compare:

Row counts (are they in the expected ballpark?)
Known values (does the result match what you'd expect for a test account?)
Edge cases (what happens with NULL values? What about accounts with no data?)

-- Spot-check: does this query return the right count for a known account?
SELECT COUNT(*) FROM orders WHERE customer_id = 42;  -- you know this customer has 7 orders

If the result is 7, you're on the right track. If it's 0 or 700, something is wrong with the query logic.

✅ 6. Watch Out for NULL Handling

AI-generated queries often miss NULL edge cases. The = operator doesn't work with NULL — it silently excludes rows.

-- This will miss rows where discount_code IS NULL
SELECT * FROM orders WHERE discount_code != 'PROMO10';

-- Correct if you want all orders without that specific code, including nulls:
SELECT * FROM orders
WHERE discount_code != 'PROMO10' OR discount_code IS NULL;

This is especially common in WHERE filters on optional fields like coupon codes, referral sources, or plan tiers.

✅ 7. For Write Operations: Always Dry-Run First

If the AI generates an UPDATE, INSERT, or DELETE, wrap it in a transaction and inspect the affected rows before committing:

BEGIN;

UPDATE subscriptions
SET status = 'cancelled'
WHERE trial_ends_at < NOW() AND status = 'trialing';

-- Check what would be affected before committing:
SELECT COUNT(*) FROM subscriptions
WHERE trial_ends_at < NOW() AND status = 'trialing';

ROLLBACK;  -- Don't commit until you're sure

This gives you a preview of the blast radius before any data changes are permanent.

A Simple Pre-Run Mental Model

Before executing any AI-generated query, ask yourself:

Question	What to check
Do all columns exist?	Cross-reference against schema
Are all JOINs correct?	Verify keys and join types
Is aggregation right?	Check GROUP BY, HAVING vs WHERE
Is tenant data scoped?	Look for account_id / user_id filters
Are NULLs handled?	Check optional column filters
Is this a write operation?	Use BEGIN/ROLLBACK to preview

Common Mistakes to Watch For

Trusting output that "looks right" — plausible-looking results are the most dangerous kind of wrong
Skipping staging for read-only queries — even SELECTs can cause problems (performance, data leakage) if they're wrong
Not providing schema context to the AI — the better your prompt (including table definitions and sample values), the more accurate the output
Running AI queries against production without row limits — add LIMIT 100 while testing to avoid accidentally returning or processing millions of rows

Key Takeaways

AI tools are genuinely useful for writing SQL faster — especially for repetitive patterns and boilerplate joins. But they're drafting assistants, not a replacement for your understanding of the data model. Every AI-generated query deserves at least a 30-second review against the checklist above.

The silent failure — a query that runs cleanly but returns wrong data — is what makes SQL validation non-negotiable. A 15% revenue discrepancy discovered a week after shipping is a bad day. A 30-second schema check is not.

Do you have a validation workflow you follow for AI-generated SQL? Share it in the comments — I'd love to hear how other teams handle this, especially in multi-tenant or high-stakes reporting contexts.

Beyond Basics: Mastering SQL's LEAD, LAG, RANK, DENSE_RANK, and NTILE

Vivek Kumar — Mon, 11 May 2026 13:58:12 +0000

You've learned that window functions exist. You've run a ROW_NUMBER() or two. But there's a whole tier of power hiding just one step further — functions that let you compare rows against their neighbors, assign competition-style rankings, and slice datasets into meaningful percentile buckets. No self-joins. No correlated subqueries. No tears.

This article goes deep on five advanced window functions: LEAD, LAG, RANK, DENSE_RANK, and NTILE. Each one solves a specific class of problem you'll hit constantly in analytics, reporting, and data engineering. We'll use realistic scenarios — e-commerce orders, employee salaries, and product sales — so you can see exactly where these functions shine.

Prerequisite: This article assumes you already know what the OVER() clause and PARTITION BY are. If not, check out the intro to SQL window functions first.

The LEAD and LAG Functions: Looking Forward and Backward

LAG retrieves a value from a previous row. LEAD retrieves a value from a future row. Both are essential for time-series comparisons where you need to know "how did this value change from last period?"

Syntax:

LAG(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)
LEAD(column, offset, default_value) OVER (PARTITION BY ... ORDER BY ...)

offset — how many rows back (LAG) or forward (LEAD) to look. Defaults to 1.
default_value — what to return when there's no row at that offset (instead of NULL).

Example 1: Month-over-Month Revenue Change

You have a monthly_revenue table tracking each month's sales:

CREATE TABLE monthly_revenue (
  month       DATE,
  product_id  INT,
  revenue     NUMERIC(12,2)
);

To calculate the change from the previous month for each product:

SELECT
  month,
  product_id,
  revenue,
  LAG(revenue, 1, 0) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS prev_month_revenue,
  revenue - LAG(revenue, 1, 0) OVER (
    PARTITION BY product_id
    ORDER BY month
  ) AS revenue_change
FROM monthly_revenue
ORDER BY product_id, month;

Result (sample rows):

month	product_id	revenue	prev_month_revenue	revenue_change
2026-01-01	101	12000.00	0.00	12000.00
2026-02-01	101	15400.00	12000.00	3400.00
2026-03-01	101	13800.00	15400.00	-1600.00

Notice the default_value of 0 prevents the first row from showing NULL. That's a small touch that makes downstream reporting much cleaner.

Example 2: Detecting Customer Churn Risk with LEAD

You want to flag customers who placed an order but then didn't order again within 90 days:

SELECT
  customer_id,
  order_date,
  LEAD(order_date) OVER (
    PARTITION BY customer_id
    ORDER BY order_date
  ) AS next_order_date,
  CASE
    WHEN LEAD(order_date) OVER (
           PARTITION BY customer_id ORDER BY order_date
         ) IS NULL
      OR LEAD(order_date) OVER (
           PARTITION BY customer_id ORDER BY order_date
         ) - order_date > 90
    THEN 'At Risk'
    ELSE 'Active'
  END AS churn_status
FROM orders;

This is the kind of query that would previously require a self-join on orders o1 JOIN orders o2 ON o1.customer_id = o2.customer_id AND o2.order_date > o1.order_date — messy, slow, and hard to read.

RANK vs DENSE_RANK vs ROW_NUMBER: Choosing the Right Ranking Function

These three functions all assign row numbers, but they handle ties very differently. Picking the wrong one is a silent bug that poisons your reports.

Here's the core difference on a single dataset:

SELECT
  employee_name,
  department,
  salary,
  ROW_NUMBER()   OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()         OVER (PARTITION BY department ORDER BY salary DESC) AS rnk,
  DENSE_RANK()   OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rnk
FROM employees;

Result (Engineering department):

employee_name	salary	row_num	rnk	dense_rnk
Alice	120000	1	1	1
Bob	105000	2	2	2
Carol	105000	3	2	2
Dan	98000	4	4	3

ROW_NUMBER always gives unique numbers — ties are broken arbitrarily. Use this for pagination.
RANK skips numbers after ties — Bob and Carol are both rank 2, so Dan is rank 4 (not 3). Use this for competition-style rankings ("top 3 scores").
DENSE_RANK never skips — Bob and Carol are both rank 2, and Dan is rank 3. Use this when gaps in ranking would be confusing or misleading.

Example 3: Top-Earning Employee Per Department

A very common analytics requirement — "show me the top earner in each department":

WITH ranked_employees AS (
  SELECT
    employee_name,
    department,
    salary,
    DENSE_RANK() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS salary_rank
  FROM employees
)
SELECT employee_name, department, salary
FROM ranked_employees
WHERE salary_rank = 1;

Why DENSE_RANK instead of RANK here? Because if two people tie for top salary, you want both to appear. RANK = 1 and DENSE_RANK = 1 both return tied rows, but with DENSE_RANK you avoid the counterintuitive gap at rank 2.

NTILE: Slicing Data into Buckets

NTILE(n) divides ordered rows into n roughly equal groups and assigns each row a bucket number (1 through n). This is the window function for percentile analysis, quartiles, and cohort segmentation.

Syntax:

NTILE(n) OVER (PARTITION BY ... ORDER BY ...)

Example 4: Segmenting Customers by Purchase Value

Imagine you want to label customers as Bronze, Silver, Gold, or Platinum based on their total annual spend:

WITH customer_spend AS (
  SELECT
    customer_id,
    SUM(order_total) AS annual_spend
  FROM orders
  WHERE EXTRACT(YEAR FROM order_date) = 2025
  GROUP BY customer_id
),
customer_tiers AS (
  SELECT
    customer_id,
    annual_spend,
    NTILE(4) OVER (ORDER BY annual_spend ASC) AS spend_quartile
  FROM customer_spend
)
SELECT
  customer_id,
  annual_spend,
  CASE spend_quartile
    WHEN 1 THEN 'Bronze'
    WHEN 2 THEN 'Silver'
    WHEN 3 THEN 'Gold'
    WHEN 4 THEN 'Platinum'
  END AS tier
FROM customer_tiers
ORDER BY annual_spend DESC;

Result (sample):

customer_id	annual_spend	tier
C-4821	48320.00	Platinum
C-3019	31005.00	Gold
C-7733	12400.00	Silver
C-2210	1890.00	Bronze

One important caveat: if the total number of rows isn't evenly divisible by n, the higher-numbered buckets get one fewer row. This is mathematically correct but can surprise you if you expect perfectly even groups.

Example 5: Identifying Performance Outliers with NTILE

In a data pipeline, you want to flag the slowest 25% of queries for investigation:

SELECT
  query_id,
  execution_ms,
  NTILE(4) OVER (ORDER BY execution_ms DESC) AS slowness_quartile
FROM query_logs
WHERE logged_at >= NOW() - INTERVAL '7 days';

Filter WHERE slowness_quartile = 1 and you have your slowest 25% — no hardcoded thresholds, no percentile math in application code.

Common Mistakes and Gotchas

1. Missing ORDER BY inside OVER()

LEAD, LAG, RANK, and DENSE_RANK are meaningless without an ORDER BY inside OVER(). The database will either error out or return unpredictable results. Always specify it explicitly.

2. Confusing the outer ORDER BY with the window ORDER BY

The ORDER BY inside OVER(...) determines the window ordering — which row is "previous" or "next." The ORDER BY at the end of the query determines the display order. These are independent. Your results might look sorted even without an outer ORDER BY, but never rely on that.

3. RANK vs DENSE_RANK for filtering

Using WHERE rank = 2 after RANK() might return zero rows if rank 2 was skipped due to a tie. DENSE_RANK avoids this. When filtering by rank in a CTE, think carefully about which behavior you want.

4. NULLs in LAG/LEAD

The first row for each partition has no previous row, so LAG returns NULL by default. Always provide a sensible default value (the third argument) if downstream calculations can't handle NULL.

5. NTILE and uneven groups

NTILE(3) on 10 rows gives groups of sizes 4, 3, 3 — not 3.33 each. Document this behavior if you're presenting "even segments" to stakeholders.

Key Takeaways

All five of these functions share the same OVER() structure you already know — what changes is what they compute. In summary:

LAG / LEAD — access values from neighboring rows, ideal for time-series deltas and gap detection
RANK — competition-style ranking with gaps after ties, best for "top N" filtering
DENSE_RANK — ranking without gaps after ties, best when missing rank numbers would be confusing
ROW_NUMBER — always unique, best for pagination and deduplication
NTILE(n) — bucket rows into n groups, best for percentiles and cohort segmentation

The real superpower here is combining them. Use LAG to calculate MoM change, then NTILE to segment products into growth quartiles, then DENSE_RANK to rank within each quartile — all in a single query using CTEs.

What's Next?

Try rewriting a query you currently use that relies on a self-join for row comparison. Chances are LAG or LEAD collapses it into something half the length. Share what you built in the comments — I'd love to see real-world use cases you've solved with these functions!

Beyond Basic Indexes: Mastering Partial, Composite, and Covering Indexes in SQL

Vivek Kumar — Mon, 11 May 2026 04:21:41 +0000

Most developers know that adding an index speeds up queries. But slapping a basic index on every column you filter by is a recipe for bloated storage, slower writes, and no real performance gain. The real power comes from choosing the right type of index for the job.

In this article, we'll go beyond the basics and explore three advanced indexing strategies: partial indexes, composite indexes, and covering indexes. Each solves a different class of performance problem, and knowing when to reach for each one separates good database work from great database work.

We'll use PostgreSQL throughout, but these concepts apply (with some syntax variation) to MySQL, SQL Server, and other major databases.

The Setup: An E-Commerce Orders Table

Let's ground everything in a realistic scenario. Imagine an orders table with tens of millions of rows:

CREATE TABLE orders (
  id          BIGSERIAL PRIMARY KEY,
  customer_id BIGINT        NOT NULL,
  status      VARCHAR(20)   NOT NULL,  -- 'pending', 'shipped', 'delivered', 'cancelled'
  total       NUMERIC(10,2) NOT NULL,
  created_at  TIMESTAMPTZ   NOT NULL DEFAULT now(),
  region      VARCHAR(50)   NOT NULL
);

Over time, the table grows to 50 million rows. Most orders are 'delivered' or 'cancelled' — a tiny fraction (maybe 2%) are 'pending' or 'shipped'. This distribution is the key to understanding why generic indexes often fall short.

Partial Indexes: Index Only What You Query

A partial index is built over a subset of rows, defined by a WHERE condition. It's one of the most underused features in PostgreSQL.

The Problem

Your support dashboard queries pending and shipped orders constantly:

SELECT id, customer_id, total
FROM orders
WHERE status IN ('pending', 'shipped')
ORDER BY created_at DESC;

A plain index on status would index all 50 million rows, even though 98% of them will never match this query. That's 50 million index entries serving 1 million actual lookups.

The Solution: A Partial Index

CREATE INDEX idx_orders_active
  ON orders (created_at DESC)
  WHERE status IN ('pending', 'shipped');

Now PostgreSQL only indexes the ~1 million "active" rows. The index is 98% smaller, fits more easily in memory, and stays faster even as the full table grows — because delivered/cancelled orders never bloat it.

Query Plan Comparison

Before (sequential scan on 50M rows):

Seq Scan on orders  (cost=0.00..1245000.00 rows=1100000 width=32)
  Filter: (status = ANY ('{pending,shipped}'::text[]))

After (index scan on 1M-entry partial index):

Index Scan using idx_orders_active on orders  (cost=0.43..9834.12 rows=1100000 width=32)

When to Use Partial Indexes

Partial indexes shine when:

A column has highly skewed distribution (most queries target a small minority of rows)
You filter on "active" or "open" records and the closed ones pile up over time
You want a unique constraint only for non-null values: CREATE UNIQUE INDEX ON users (email) WHERE email IS NOT NULL;

Composite Indexes: Column Order Is Everything

A composite index (also called a multi-column index) covers more than one column. Done right, it eliminates multiple sequential scans. Done wrong, it wastes space and never gets used.

The Left-to-Right Rule

PostgreSQL can use a composite index only when the query filters on a prefix of the indexed columns. Given:

CREATE INDEX idx_orders_customer_status_date
  ON orders (customer_id, status, created_at DESC);

Query predicate	Uses index?	Why
`WHERE customer_id = 42`	✅ Yes	Matches leading column
`WHERE customer_id = 42 AND status = 'pending'`	✅ Yes	Matches first two columns
`WHERE customer_id = 42 AND status = 'pending' AND created_at > '2026-01-01'`	✅ Yes (full use)	Matches all three columns
`WHERE status = 'pending'`	❌ No	Skips leading column
`WHERE created_at > '2026-01-01'`	❌ No	Skips first two columns

Column Ordering Strategy

Follow this rule of thumb for column order in a composite index:

Equality columns first — columns used with = or IN
Range columns next — columns used with >, <, BETWEEN
Sort columns last — columns used in ORDER BY

So for this query:

SELECT id, total
FROM orders
WHERE customer_id = 42
  AND status = 'pending'
  AND created_at > now() - interval '30 days'
ORDER BY created_at DESC;

The ideal index is exactly what we created: (customer_id, status, created_at DESC). PostgreSQL can narrow by customer_id (equality), then narrow further by status (equality), then scan only the recent range of created_at — all within the index structure.

Avoid Index Proliferation

One well-designed composite index often replaces two or three single-column indexes. If you have idx_customer_id and idx_status separately, queries needing both columns force a "bitmap AND" merge — two index lookups joined in memory. A single composite index eliminates that overhead.

Covering Indexes: Make the Index the Answer

A covering index is one that contains every column a query needs — so PostgreSQL never has to touch the actual table rows. This is called an index-only scan, and it's the fastest possible read path.

The Problem: Heap Fetches

Normally, after an index lookup, PostgreSQL must fetch the actual row from the table heap to retrieve non-indexed columns. On large tables with many concurrent writes, those heap fetches cause random I/O that kills performance.

The INCLUDE Clause

PostgreSQL 11+ introduced the INCLUDE clause to add "payload" columns to an index without making them part of the sort key:

CREATE INDEX idx_orders_customer_covering
  ON orders (customer_id, status)
  INCLUDE (total, created_at);

Now this query runs entirely off the index — no heap access at all:

SELECT total, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'shipped';

INCLUDE vs Adding to the Key

Why not just add total and created_at to the index key itself? Because:

Non-key columns aren't sorted, so they can't help filter or sort — they just live as payload in the leaf pages
Smaller B-tree height — fewer levels to traverse since non-key columns only appear in leaf nodes, not interior nodes
Avoids over-indexing — the key remains selective; INCLUDE columns just come along for the ride

Seeing It in Action

EXPLAIN (ANALYZE, BUFFERS)
SELECT total, created_at
FROM orders
WHERE customer_id = 42
  AND status = 'shipped';

Without INCLUDE:

Index Scan using idx_orders_customer_status on orders
  (cost=0.56..892.34 rows=847 width=24)
  (actual time=0.031..4.821 rows=847 loops=1)
  Buffers: shared hit=423 read=634   ← 634 heap blocks read

With INCLUDE (total, created_at):

Index Only Scan using idx_orders_customer_covering on orders
  (cost=0.56..112.18 rows=847 width=24)
  (actual time=0.025..0.893 rows=847 loops=1)
  Buffers: shared hit=52 read=0      ← 0 heap blocks read

That's a 5× reduction in execution time and zero heap I/O.

Combining All Three: The Power Move

You can combine all three strategies. Here's a partial covering composite index:

CREATE INDEX idx_orders_active_by_region
  ON orders (region, created_at DESC)
  INCLUDE (customer_id, total)
  WHERE status IN ('pending', 'shipped');

This index:

Only indexes active orders (partial — keeps it small)
Supports filtering by region and sorting by date (composite — ordered key)
Returns customer_id and total without a heap fetch (covering — zero table I/O)

A query like this will hit this index perfectly:

SELECT customer_id, total, created_at
FROM orders
WHERE region = 'EU-West'
  AND status IN ('pending', 'shipped')
ORDER BY created_at DESC
LIMIT 50;

Common Gotchas

Over-indexing writes more than you read. Every index is updated on every INSERT, UPDATE, and DELETE. An extra 5 indexes on a high-write table can make inserts 2–3× slower. Audit with pg_stat_user_indexes to find unused indexes:

SELECT indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
ORDER BY idx_scan ASC;

Partial index predicates must match exactly. PostgreSQL uses a partial index only when the query's WHERE clause implies (or matches) the index predicate. WHERE status = 'pending' won't use an index defined as WHERE status IN ('pending', 'shipped') — you need WHERE status IN ('pending', 'shipped') or the planner must be able to prove the subset relationship.

INCLUDE columns don't help WHERE or ORDER BY. They are non-key payload only — PostgreSQL can't use them to narrow or sort. If you filter or sort by a column, it must be in the key part of the index, not in INCLUDE.

Build indexes concurrently on live tables. Creating an index on a large table locks out writes. Use CREATE INDEX CONCURRENTLY to build the index without blocking:

CREATE INDEX CONCURRENTLY idx_orders_active_by_region
  ON orders (region, created_at DESC)
  INCLUDE (customer_id, total)
  WHERE status IN ('pending', 'shipped');

Key Takeaways

Partial indexes target a narrow, frequently queried subset of rows. They stay small and fast even as the full table balloons in size.
Composite indexes cover multi-column queries efficiently — but column order matters. Equality predicates first, range predicates second, sort columns last.
Covering indexes (with INCLUDE) allow index-only scans, eliminating costly random I/O to the table heap.
Combining all three gives you a precision instrument: small, ordered, and self-contained.

The best index strategy always starts with understanding your query patterns. Profile with EXPLAIN (ANALYZE, BUFFERS), identify where heap reads or sequential scans dominate, then choose the index type that surgically removes that bottleneck.

Have you tuned a slow query with one of these techniques? Drop a comment below — especially if you've hit a surprising edge case. And if you're new to indexing fundamentals, check out the earlier post in this series on basic SQL indexes before diving deeper.

SQL Views Explained: Write Once, Query Forever

Vivek Kumar — Thu, 07 May 2026 03:01:49 +0000

You've written the same 12-line JOIN query for the third time this week. It lives in your reporting script, your admin dashboard, and your data export job. Every time the business logic changes, you update it in three places — and inevitably miss one.

SQL views were built to fix exactly this. A view is a named, stored query that you can treat like a table. Define your logic once, then reference it from anywhere. This article walks through everything you need to know to start using views effectively: what they are, how to create them, real-world scenarios where they shine, and the gotchas that trip people up.

What Is a SQL View?

A view is a virtual table based on the result of a SELECT statement. It doesn't store data itself — it stores the query. Every time you SELECT from a view, the database runs that underlying query and returns fresh results.

Here's the simplest possible view:

CREATE VIEW active_customers AS
SELECT id, name, email, created_at
FROM customers
WHERE status = 'active';

Now instead of repeating that filter everywhere, you just do:

SELECT * FROM active_customers;

The database sees through the view and runs the full underlying query — you get current data, every time.

Creating, Replacing, and Dropping Views

CREATE VIEW

CREATE VIEW monthly_revenue AS
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(total_amount)               AS revenue,
  COUNT(*)                        AS order_count
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month DESC;

Once this exists, any analyst on your team can run:

SELECT * FROM monthly_revenue WHERE month >= '2026-01-01';

No need to remember the aggregation logic — it's baked into the view.

CREATE OR REPLACE VIEW

To update a view's definition without dropping and recreating it:

CREATE OR REPLACE VIEW monthly_revenue AS
SELECT
  DATE_TRUNC('month', order_date) AS month,
  SUM(total_amount)               AS revenue,
  COUNT(*)                        AS order_count,
  AVG(total_amount)               AS avg_order_value  -- new column added
FROM orders
WHERE status = 'completed'
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month DESC;

DROP VIEW

DROP VIEW IF EXISTS monthly_revenue;

Use IF EXISTS to avoid errors if the view doesn't exist yet.

Real-World Use Cases

1. Simplifying Complex Reports

Imagine you have an e-commerce schema: orders, order_items, products, customers. A typical report query might join all four tables and apply several filters. Put that in a view:

CREATE VIEW order_summary AS
SELECT
  o.id            AS order_id,
  c.name          AS customer_name,
  c.email,
  o.order_date,
  o.status,
  SUM(oi.quantity * oi.unit_price) AS total_amount,
  COUNT(oi.id)                     AS item_count
FROM orders o
JOIN customers  c  ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.name, c.email, o.order_date, o.status;

Now your dashboard queries look clean:

-- Top 10 customers by spending this year
SELECT customer_name, email, SUM(total_amount) AS lifetime_value
FROM order_summary
WHERE order_date >= '2026-01-01'
GROUP BY customer_name, email
ORDER BY lifetime_value DESC
LIMIT 10;

2. Hiding Sensitive Columns

You want your analytics team to query the employees table but not see salaries or social security numbers. Create a view that exposes only safe columns:

CREATE VIEW employees_public AS
SELECT
  id,
  first_name,
  last_name,
  department,
  job_title,
  hire_date
FROM employees;
-- salary, ssn, and bank_account columns are never exposed

Grant access to the view, not the base table:

GRANT SELECT ON employees_public TO analytics_role;

This is one of the most practical uses of views in production systems.

3. Maintaining Backward Compatibility

Your application queries a table called user_profiles. You need to refactor the schema — split it into users and profile_details. Create a view with the old name that reconstructs the original shape:

CREATE VIEW user_profiles AS
SELECT
  u.id,
  u.email,
  u.created_at,
  pd.bio,
  pd.avatar_url,
  pd.location
FROM users u
JOIN profile_details pd ON pd.user_id = u.id;

Old queries keep working. You can migrate downstream consumers at your own pace.

4. Pre-Filtering for Multi-Tenant Apps

In a SaaS app, data is often segmented by tenant_id. Views can bake in row-level filtering:

CREATE VIEW tenant_orders AS
SELECT *
FROM orders
WHERE tenant_id = current_setting('app.current_tenant')::int;

(PostgreSQL supports this pattern using session-level settings.)

Updatable Views

In some situations you can run INSERT, UPDATE, and DELETE directly on a view. This works when the view is simple enough that the database can unambiguously map the change back to a single base table row.

A view is generally updatable if it:

Selects from a single base table
Does not use DISTINCT, GROUP BY, HAVING, UNION, or aggregate functions
Does not include computed columns

Example of an updatable view:

CREATE VIEW active_products AS
SELECT id, name, price, stock_quantity
FROM products
WHERE is_active = TRUE;

-- This works because it maps cleanly to one row in `products`
UPDATE active_products SET price = 29.99 WHERE id = 42;

WITH CHECK OPTION

Add WITH CHECK OPTION to prevent updates that would push rows out of the view's filter:

CREATE VIEW active_products AS
SELECT id, name, price, stock_quantity
FROM products
WHERE is_active = TRUE
WITH CHECK OPTION;

-- This would fail — it would make the row invisible to the view
UPDATE active_products SET is_active = FALSE WHERE id = 42;
-- ERROR: new row violates check option for view "active_products"

Views vs. CTEs: When to Use Which

Both views and CTEs (covered in a previous article) let you name and reuse query logic. The key difference:

Feature	View	CTE
Persistence	Stored in database permanently	Exists only during one query
Reusability	Available to all queries, all users	Local to the single SQL statement
Access control	Can be granted/revoked independently	Not applicable
Best for	Shared, frequently used logic	Ad-hoc, one-off query breakdowns
Recursive queries	No	Yes

Rule of thumb: If you're writing the same subquery in multiple queries or multiple applications, make it a view. If you're just breaking up a complex single query for readability, use a CTE.

Common Mistakes and Gotchas

1. Assuming Views Cache Results

Views do not store data. Every query against a view re-executes the underlying SQL. If that underlying query is slow, your view will be slow too. Views give you reusability, not performance — for that, look at materialized views (a topic for another day).

2. Nesting Views Too Deeply

It's tempting to build views on top of views on top of views. Resist this. Deep view nesting makes debugging painful and can confuse the query planner. If your view references another view which references another view, the optimizer has to unpack all of them before building an execution plan. Keep hierarchies shallow (two levels max).

3. Putting Too Much Logic Into One View

Views encourage DRY code, but that doesn't mean one monster view should serve every use case. A view that joins 8 tables for every query will be slow for callers who only need 2 of those tables. Create targeted views for specific, well-understood use cases.

4. Forgetting That Schema Changes Can Break Views

If you drop a column from a base table that a view depends on, the view breaks — often silently until someone queries it. Most databases won't warn you at ALTER TABLE time. Build a habit of checking view dependencies before modifying base tables.

In PostgreSQL, you can check what views depend on a table:

SELECT viewname, definition
FROM pg_views
WHERE definition ILIKE '%your_table_name%';

5. Trying to Pass Parameters Into Views

Views are static — they can't accept parameters the way stored procedures can. If you find yourself creating dozens of near-identical views (orders_2024, orders_2025, orders_jan, orders_feb), that's a sign you want a parameterized query, a function, or a filtered query against a single general-purpose view instead.

Key Takeaways

SQL views are a powerful tool for keeping your database logic clean, consistent, and secure. In summary:

A view is a named, stored SELECT query — not stored data
Use them to centralize shared query logic so changes propagate everywhere at once
Use them to restrict access by exposing only safe columns to certain roles
Use CREATE OR REPLACE to update view definitions without downtime
Use WITH CHECK OPTION to enforce constraints on updatable views
Prefer CTEs for one-off breakdowns, views for recurring shared logic
Watch out for deep nesting, bloated view definitions, and silent schema-change failures

Try It Yourself

Take one query you write repeatedly — a filter, a join, a report — and convert it into a view today. Then ask: who else on your team could benefit from having access to it?

Have you run into situations where views saved you from repetition, or caused unexpected performance headaches? Drop a comment below — I'd love to hear how you're using them in production.

This is part of an ongoing series on SQL databases. Previous articles covered CTEs, window functions, indexes, and more. Follow along for new posts every few days.

SQL String Functions: Clean, Transform, and Tame Messy Data Like a Pro

Vivek Kumar — Wed, 06 May 2026 04:45:52 +0000

Real-world data is messy. Users type their names in ALL CAPS, phone numbers come in five different formats, email addresses have trailing spaces, and city names are spelled three different ways. Before you can analyze or display that data meaningfully, you need to clean and reshape it — and SQL string functions are one of the most powerful tools in your toolkit for doing exactly that.

In this guide, we'll go beyond the basics. You'll see how string functions work together to solve real data problems — the kind that show up constantly in reporting pipelines, backend APIs, and data migrations.

The Sample Database

We'll use a realistic customers table throughout this article:

CREATE TABLE customers (
  customer_id   SERIAL PRIMARY KEY,
  first_name    VARCHAR(100),
  last_name     VARCHAR(100),
  email         VARCHAR(255),
  phone         VARCHAR(30),
  city          VARCHAR(100),
  country_code  CHAR(2)
);

And here's the kind of data you might find in it after a messy data import:

customer_id	first_name	last_name	email	phone	city
1	ALICE	Johnson	alice@example.com	(555) 123-4567	new york
2	bob	Smith	BOB@EXAMPLE.COM	555.987.6543	Los Angeles
3	María	García	maria@example.com	5552345678	CHICAGO
4	James	NULL	james@example.com	+1-555-876-5432	Houston

Classic import chaos. Let's fix it.

TRIM: Killing the Invisible Enemies

Leading and trailing whitespace is the cockroach of data problems — invisible, persistent, and it breaks things at the worst moments. TRIM removes it.

-- Remove spaces from both sides (default behavior)
SELECT TRIM(last_name) AS clean_last_name
FROM customers;

clean_last_name
---------------
Johnson
Smith
García
(null)

You can also target only one side:

SELECT
  LTRIM(email) AS no_leading_space,
  RTRIM(email) AS no_trailing_space
FROM customers;

Gotcha: TRIM only removes spaces by default. If your data has tabs (\t) or newlines (\n) — common in CSV imports — you need REGEXP_REPLACE (covered below).

UPPER and LOWER: Standardizing Case

Mixed-case data makes comparisons a nightmare. alice@example.com and ALICE@example.com will fail an equality check even though they're the same address.

-- Normalize all emails to lowercase
SELECT
  customer_id,
  LOWER(TRIM(email)) AS normalized_email
FROM customers;

customer_id | normalized_email
------------+-------------------
1           | alice@example.com
2           | bob@example.com
3           | maria@example.com
4           | james@example.com

For display names, you often want title case. SQL doesn't have a built-in INITCAP function in MySQL, but PostgreSQL does:

-- PostgreSQL only
SELECT
  INITCAP(LOWER(first_name)) AS display_first,
  INITCAP(LOWER(last_name))  AS display_last
FROM customers;

display_first | display_last
--------------+-------------
Alice         | Johnson
Bob           | Smith
María         | García
James         | (null)

In MySQL, you'd build your own with CONCAT(UPPER(LEFT(name,1)), LOWER(SUBSTRING(name,2))).

CONCAT: Building Strings From Parts

CONCAT joins strings together. Use it for display names, slugs, full addresses — anywhere you need to assemble a value from multiple columns.

-- Build a full display name
SELECT
  CONCAT(
    INITCAP(LOWER(TRIM(first_name))),
    ' ',
    INITCAP(LOWER(TRIM(last_name)))
  ) AS full_name
FROM customers
WHERE last_name IS NOT NULL;

full_name
-----------
Alice Johnson
Bob Smith
María García

The || operator is the ANSI-standard alternative to CONCAT() and works in PostgreSQL:

SELECT TRIM(first_name) || ' ' || TRIM(last_name) AS full_name FROM customers;

Gotcha: In MySQL, CONCAT(value, NULL) returns NULL — a single null column poisons the whole concatenation. Use CONCAT_WS (concat with separator) or COALESCE to guard against this:

-- Safe version that handles NULLs
SELECT CONCAT(first_name, ' ', COALESCE(last_name, '')) AS full_name
FROM customers;

REPLACE: Fixing Formatted Junk

Our phone numbers are a mess — parentheses, dots, dashes, country codes. REPLACE lets you strip unwanted characters one at a time.

-- Strip common phone number formatting characters
SELECT
  phone AS original,
  REPLACE(
    REPLACE(
      REPLACE(
        REPLACE(
          REPLACE(phone, '(', ''),
        ')', ''),
      '-', ''),
    '.', ''),
  ' ', '') AS digits_only
FROM customers;

original	digits_only
(555) 123-4567	5551234567
555.987.6543	5559876543
5552345678	5552345678
+1-555-876-5432	+15558765432

For the last row, you'd still need to handle the +1 country prefix — which is where REGEXP_REPLACE shines.

REGEXP_REPLACE: The Power Tool

When REPLACE isn't enough, regular expressions let you target patterns instead of literal characters.

-- PostgreSQL: strip ALL non-digit characters from phone numbers
SELECT
  phone,
  REGEXP_REPLACE(phone, '[^0-9]', '', 'g') AS digits_only
FROM customers;

phone            | digits_only
-----------------+------------
(555) 123-4567   | 5551234567
555.987.6543     | 5559876543
5552345678       | 5552345678
+1-555-876-5432  | 15558765432

In MySQL 8+, use REGEXP_REPLACE(phone, '[^0-9]', '').

Gotcha: Regex is powerful but slow on large tables. Don't run REGEXP_REPLACE on a column inside a WHERE clause if you can help it — you'll kill index usage and force a full scan. Clean your data in a migration, not on every query.

SUBSTRING: Extracting Exactly What You Need

SUBSTRING (or SUBSTR) extracts part of a string by position.

-- Extract domain from email addresses
SELECT
  email,
  SUBSTRING(email FROM POSITION('@' IN email) + 1) AS domain
FROM customers;

email                   | domain
------------------------+------------
alice@example.com       | example.com
bob@example.com         | example.com
maria@example.com       | example.com
james@example.com       | example.com

A more practical example — extracting year from a date stored as text (not ideal, but it happens):

-- Pull the year from 'YYYY-MM-DD' stored as text
SELECT
  order_ref,
  SUBSTRING(order_ref, 1, 4) AS order_year
FROM orders
WHERE order_ref LIKE '____-__-__%';

SUBSTRING(string, start, length) is the MySQL/SQL-standard syntax; PostgreSQL also supports SUBSTRING(string FROM start FOR length).

LENGTH and POSITION: Measuring and Locating

LENGTH tells you how many characters a string has. POSITION (or CHARINDEX in SQL Server) tells you where a substring appears.

-- Find customers with suspiciously short email addresses
SELECT customer_id, email, LENGTH(email) AS email_length
FROM customers
WHERE LENGTH(TRIM(email)) < 10;

-- Find where the '@' sits in each email
SELECT email, POSITION('@' IN email) AS at_position
FROM customers;

Together, POSITION and SUBSTRING let you split strings on a delimiter without regex — useful for parsing values like "Smith, John" or "Houston, TX":

-- Split "city, state" into two columns
SELECT
  location,
  TRIM(SUBSTRING(location FROM 1 FOR POSITION(',' IN location) - 1)) AS city,
  TRIM(SUBSTRING(location FROM POSITION(',' IN location) + 1))       AS state
FROM offices;

Putting It All Together: A Real Cleanup Query

Here's a single query that normalizes the entire customers table for a reporting export:

SELECT
  customer_id,
  INITCAP(LOWER(TRIM(first_name)))                         AS first_name,
  INITCAP(LOWER(TRIM(COALESCE(last_name, ''))))            AS last_name,
  LOWER(TRIM(email))                                       AS email,
  REGEXP_REPLACE(phone, '[^0-9]', '', 'g')                 AS phone_digits,
  INITCAP(LOWER(TRIM(city)))                               AS city,
  UPPER(country_code)                                      AS country_code
FROM customers;

The result:

customer_id	first_name	last_name	email	phone_digits	city
1	Alice	Johnson	alice@example.com	5551234567	New York
2	Bob	Smith	bob@example.com	5559876543	Los Angeles
3	María	García	maria@example.com	5552345678	Chicago
4	James		james@example.com	15558765432	Houston

Common Mistakes to Avoid

Assuming NULLs are empty strings. LENGTH(NULL) returns NULL, not 0. Always wrap nullable columns in COALESCE(column, '') before passing them to string functions.

Chaining REPLACE instead of using REGEXP_REPLACE. Five nested REPLACE calls are harder to read and maintain than a single regex pattern. If you find yourself nesting more than two, switch to regex.

Running string transformations in WHERE clauses on large tables. WHERE LOWER(email) = 'alice@example.com' prevents the database from using an index on email. Either store emails already lowercased, or create a functional index: CREATE INDEX idx_email_lower ON customers (LOWER(email)).

Forgetting character vs. byte length. In MySQL, LENGTH() returns byte count — a multi-byte UTF-8 character (like é or 中) counts as 2–3 bytes. Use CHAR_LENGTH() when you care about the number of visible characters.

Key Takeaways

SQL string functions aren't just cosmetic — they're essential data engineering tools. A few patterns worth remembering:

Clean at storage time, not query time — normalize data when it enters your database, not on every SELECT
Layer functions — INITCAP(LOWER(TRIM(name))) is perfectly valid and readable SQL
Use COALESCE defensively — any function receiving NULL will return NULL, so guard early
Prefer REGEXP_REPLACE for pattern-based cleanup — it's more maintainable than chained REPLACE calls
Watch performance — function calls in WHERE clauses can kill index efficiency on large tables

What's Next?

String functions pair beautifully with SQL's UPDATE statement — you can run a one-time data cleanup across your whole table:

UPDATE customers
SET
  email      = LOWER(TRIM(email)),
  first_name = INITCAP(LOWER(TRIM(first_name))),
  city       = INITCAP(LOWER(TRIM(city)));

Have you run into a particularly gnarly data cleaning challenge in SQL? Drop it in the comments — I'd love to see how you solved it, or we can work through it together.

SQL Date & Time Functions: A Practical Guide for Real-World Queries

Vivek Kumar — Mon, 04 May 2026 05:53:12 +0000

Working with dates and times is one of those things that sounds simple until you're staring at a timestamp column wondering why your "last 30 days" query returns unexpected results. Date and time functions are essential for analytics, reporting, and any application that tracks events over time — yet the subtle differences between database engines trip up even experienced developers.

In this guide, we'll cover the most useful SQL date/time functions across PostgreSQL and MySQL — two of the most widely used databases — with practical, real-world examples. By the end, you'll know how to confidently filter by time ranges, calculate durations, group data by period, and avoid the most common pitfalls.

Why Date/Time Functions Matter

Think about how many queries involve time: "Show me orders placed in the last 7 days," "Calculate how long customers have been subscribed," "Group revenue by month." Nearly every meaningful business question has a temporal dimension.

The challenge is that SQL databases don't all speak the same date dialect. PostgreSQL has DATE_TRUNC and AGE. MySQL has DATE_FORMAT and TIMESTAMPDIFF. Standard SQL gives you EXTRACT and CURRENT_TIMESTAMP. Knowing what's available — and what differs — saves you hours of debugging.

We'll use a consistent example throughout: an e-commerce database with orders and customers tables.

-- Our example schema
CREATE TABLE customers (
  customer_id   INT PRIMARY KEY,
  name          VARCHAR(100),
  joined_at     TIMESTAMP NOT NULL
);

CREATE TABLE orders (
  order_id      INT PRIMARY KEY,
  customer_id   INT REFERENCES customers(customer_id),
  placed_at     TIMESTAMP NOT NULL,
  shipped_at    TIMESTAMP,
  total_amount  DECIMAL(10,2)
);

1. Getting the Current Date and Time

The most basic need: "what time is it right now?" Every major database supports these, but syntax varies slightly:

-- PostgreSQL
SELECT NOW();                  -- current timestamp with time zone
SELECT CURRENT_TIMESTAMP;      -- same as NOW(), SQL standard
SELECT CURRENT_DATE;           -- date only (no time)
SELECT CURRENT_TIME;           -- time only

-- MySQL
SELECT NOW();                  -- current datetime
SELECT CURDATE();              -- date only
SELECT CURTIME();              -- time only
SELECT UTC_TIMESTAMP();        -- current UTC datetime (very useful!)

Real-world use: Find all orders placed today.

-- PostgreSQL
SELECT order_id, placed_at, total_amount
FROM orders
WHERE placed_at::date = CURRENT_DATE;

-- MySQL
SELECT order_id, placed_at, total_amount
FROM orders
WHERE DATE(placed_at) = CURDATE();

Note: Casting a timestamp to date (or using DATE()) forces the database to discard the time component before comparing — otherwise placed_at = CURRENT_DATE would fail because a timestamp like 2026-04-28 14:33:00 is not equal to the date 2026-04-28.

2. Extracting Parts of a Date

Sometimes you need just the year, month, or hour from a timestamp. EXTRACT is the SQL-standard way to do this, supported by both PostgreSQL and MySQL:

-- Standard SQL — works in both PostgreSQL and MySQL
SELECT
  order_id,
  placed_at,
  EXTRACT(YEAR  FROM placed_at) AS order_year,
  EXTRACT(MONTH FROM placed_at) AS order_month,
  EXTRACT(DOW   FROM placed_at) AS day_of_week,  -- 0=Sunday in PostgreSQL
  EXTRACT(HOUR  FROM placed_at) AS order_hour
FROM orders;

Real-world use: Group orders by month to see monthly revenue trends.

SELECT
  EXTRACT(YEAR  FROM placed_at) AS yr,
  EXTRACT(MONTH FROM placed_at) AS mo,
  COUNT(*)                      AS order_count,
  SUM(total_amount)             AS revenue
FROM orders
GROUP BY yr, mo
ORDER BY yr, mo;

MySQL shorthand functions: MySQL also offers dedicated extraction functions that are a bit more readable:

-- MySQL-specific shortcuts
SELECT
  YEAR(placed_at)    AS order_year,
  MONTH(placed_at)   AS order_month,
  DAY(placed_at)     AS order_day,
  HOUR(placed_at)    AS order_hour,
  DAYNAME(placed_at) AS weekday_name   -- e.g. "Tuesday"
FROM orders;

3. Date Arithmetic with INTERVAL

Adding or subtracting time from a date is a common need: "all orders from the last 30 days," "subscriptions expiring in the next 7 days," etc.

-- PostgreSQL: use INTERVAL keyword
SELECT * FROM orders
WHERE placed_at >= NOW() - INTERVAL '30 days';

-- MySQL: use DATE_SUB() or INTERVAL notation
SELECT * FROM orders
WHERE placed_at >= NOW() - INTERVAL 30 DAY;

-- Also valid in MySQL:
SELECT * FROM orders
WHERE placed_at >= DATE_SUB(NOW(), INTERVAL 30 DAY);

INTERVAL supports a wide range of units:

-- PostgreSQL examples
NOW() + INTERVAL '1 year'
NOW() - INTERVAL '3 months'
NOW() + INTERVAL '2 hours 30 minutes'
'2026-01-01'::date + INTERVAL '90 days'

-- MySQL examples
DATE_ADD(NOW(), INTERVAL 1 YEAR)
DATE_ADD(NOW(), INTERVAL -3 MONTH)
DATE_ADD(shipped_at, INTERVAL 14 DAY)   -- estimated delivery date

Real-world use: Flag orders that shipped more than 14 days ago but never had a follow-up.

-- PostgreSQL
SELECT order_id, customer_id, shipped_at
FROM orders
WHERE shipped_at IS NOT NULL
  AND shipped_at < NOW() - INTERVAL '14 days';

4. Calculating Differences Between Dates

How many days between two dates? How long has a customer been with us? These questions require date difference functions.

DATEDIFF (MySQL)

-- MySQL: DATEDIFF returns difference in days
SELECT
  order_id,
  placed_at,
  shipped_at,
  DATEDIFF(shipped_at, placed_at) AS days_to_ship
FROM orders
WHERE shipped_at IS NOT NULL;

TIMESTAMPDIFF (MySQL)

TIMESTAMPDIFF is more flexible — you choose the unit:

-- MySQL: specify the unit you want
SELECT
  c.customer_id,
  c.name,
  c.joined_at,
  TIMESTAMPDIFF(YEAR,  c.joined_at, NOW()) AS years_as_customer,
  TIMESTAMPDIFF(MONTH, c.joined_at, NOW()) AS months_as_customer,
  TIMESTAMPDIFF(DAY,   c.joined_at, NOW()) AS days_as_customer
FROM customers c;

AGE (PostgreSQL)

PostgreSQL has the elegant AGE() function that returns a human-readable interval:

-- PostgreSQL
SELECT
  customer_id,
  name,
  joined_at,
  AGE(NOW(), joined_at)          AS customer_age,  -- e.g. "2 years 3 mons 12 days"
  EXTRACT(DAY FROM AGE(NOW(), joined_at))           AS days_as_customer
FROM customers;

Real-world use: Find customers who joined more than 1 year ago but have placed no order in the last 90 days (candidates for a win-back campaign).

-- PostgreSQL
SELECT
  c.customer_id,
  c.name,
  c.joined_at,
  MAX(o.placed_at) AS last_order_date
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE c.joined_at < NOW() - INTERVAL '1 year'
GROUP BY c.customer_id, c.name, c.joined_at
HAVING MAX(o.placed_at) < NOW() - INTERVAL '90 days'
    OR MAX(o.placed_at) IS NULL;

5. Truncating Dates with DATE_TRUNC (PostgreSQL)

DATE_TRUNC is a PostgreSQL superpower for grouping data by time periods. It rounds a timestamp down to the start of the given period:

SELECT DATE_TRUNC('month', NOW());
-- Result: 2026-04-01 00:00:00+00

SELECT DATE_TRUNC('week',  NOW());
-- Result: 2026-04-27 00:00:00+00 (start of current week, Monday)

SELECT DATE_TRUNC('year',  NOW());
-- Result: 2026-01-01 00:00:00+00

Supported precision values include: microseconds, milliseconds, second, minute, hour, day, week, month, quarter, year, decade, century.

Real-world use: Weekly revenue report with clean week boundaries:

-- PostgreSQL
SELECT
  DATE_TRUNC('week', placed_at)  AS week_start,
  COUNT(*)                        AS orders,
  SUM(total_amount)               AS revenue,
  AVG(total_amount)               AS avg_order_value
FROM orders
WHERE placed_at >= NOW() - INTERVAL '12 weeks'
GROUP BY DATE_TRUNC('week', placed_at)
ORDER BY week_start;

In MySQL, you can approximate this with DATE_FORMAT:

-- MySQL: group by week using DATE_FORMAT
SELECT
  DATE_FORMAT(placed_at, '%Y-%u')  AS year_week,  -- e.g. "2026-17"
  COUNT(*)                          AS orders,
  SUM(total_amount)                 AS revenue
FROM orders
WHERE placed_at >= DATE_SUB(NOW(), INTERVAL 12 WEEK)
GROUP BY DATE_FORMAT(placed_at, '%Y-%u')
ORDER BY year_week;

6. Formatting Dates for Display

Raw timestamps aren't user-friendly. Format them for reports and APIs:

-- PostgreSQL: TO_CHAR
SELECT TO_CHAR(placed_at, 'Month DD, YYYY')   AS display_date;  -- "April 28, 2026"
SELECT TO_CHAR(placed_at, 'YYYY-MM-DD HH24:MI') AS display_dt;   -- "2026-04-28 14:33"
SELECT TO_CHAR(placed_at, 'Day')               AS weekday;       -- "Tuesday"

-- MySQL: DATE_FORMAT
SELECT DATE_FORMAT(placed_at, '%M %d, %Y')     AS display_date;  -- "April 28, 2026"
SELECT DATE_FORMAT(placed_at, '%Y-%m-%d %H:%i') AS display_dt;   -- "2026-04-28 14:33"
SELECT DATE_FORMAT(placed_at, '%W')             AS weekday;       -- "Tuesday"

Common Mistakes and Gotchas

1. Applying a function to an indexed column kills performance

If you have an index on placed_at, this query can't use it:

-- ❌ Wrapping the column in a function prevents index usage
WHERE DATE(placed_at) = CURDATE()          -- MySQL
WHERE DATE_TRUNC('day', placed_at) = ...   -- PostgreSQL

Instead, use a range comparison that leaves the column untouched:

-- ✅ Range filter — the index on placed_at can be used
WHERE placed_at >= '2026-04-28 00:00:00'
  AND placed_at <  '2026-04-29 00:00:00'

-- Or dynamically:
WHERE placed_at >= CURRENT_DATE
  AND placed_at <  CURRENT_DATE + INTERVAL '1 day'

2. Ignoring time zones

NOW() in PostgreSQL returns timestamptz (with time zone). If your app stores timestamps in UTC but your NOW() uses a local session timezone, you can get wildly wrong results. Always be explicit:

-- PostgreSQL: force UTC
WHERE placed_at >= NOW() AT TIME ZONE 'UTC' - INTERVAL '24 hours'

In MySQL, use UTC_TIMESTAMP() instead of NOW() when you want UTC regardless of session settings.

3. NULL shipped_at breaks duration calculations

If shipped_at is NULL (order not yet shipped), a DATEDIFF or subtraction involving it returns NULL — not an error, just silent NULL propagation. Always handle this:

-- MySQL
SELECT
  order_id,
  COALESCE(DATEDIFF(shipped_at, placed_at), 'Not yet shipped') AS fulfillment_days
FROM orders;

4. DATEDIFF argument order differs by database

MySQL: DATEDIFF(end_date, start_date) → positive if end is after start
SQL Server: DATEDIFF(unit, start_date, end_date) → note the unit comes first!
PostgreSQL: use subtraction directly: end_date - start_date

This trips up developers switching between databases constantly.

Summary: Key Takeaways

Working with dates in SQL is a skill that pays dividends across virtually every query you'll write for analytics or reporting. The core ideas to remember:

Use NOW() / CURRENT_TIMESTAMP for the current moment; CURRENT_DATE for today's date
EXTRACT() pulls out specific date parts (year, month, day of week) — it's standard SQL
Use INTERVAL for date arithmetic — keep the indexed column bare on the left side of your WHERE clause
DATEDIFF / TIMESTAMPDIFF (MySQL) and AGE() / simple subtraction (PostgreSQL) handle duration calculations
DATE_TRUNC (PostgreSQL) is your best friend for grouping by week/month/quarter
DATE_FORMAT (MySQL) and TO_CHAR (PostgreSQL) format dates for display
Always consider NULLs and time zones — they're the source of most date-related bugs

What's Your Favorite Date Function?

Date functions are deceptively powerful. Once you're comfortable with DATE_TRUNC for time-series aggregation or TIMESTAMPDIFF for cohort analysis, your SQL queries go from useful to genuinely insightful.

Drop a comment below — what's the most useful date/time query pattern you use regularly? I'd love to see what real-world problems you're solving! And if you found this helpful, share it with a colleague who's wrestling with a "last 30 days" filter. 🗓️

SQL GROUP BY & HAVING: The Beginner's Guide to Summarizing Data Like a Pro

Vivek Kumar — Mon, 27 Apr 2026 05:23:51 +0000

If you've ever needed to answer questions like "How many orders did each customer place?" or "Which product categories generated more than $10,000 in revenue?", you've needed GROUP BY and HAVING. These two clauses are the backbone of summarizing and analyzing relational data in SQL.

In this guide, we'll go from zero to confident with both clauses. You'll understand what they do, how they differ from each other (and from WHERE), and walk away with real working examples you can adapt immediately.

What Is GROUP BY?

The GROUP BY clause collapses multiple rows that share the same value in one or more columns into a single summary row. You almost always pair it with an aggregate function — COUNT(), SUM(), AVG(), MIN(), or MAX() — to compute something meaningful about each group.

Syntax:

SELECT column_name, AGGREGATE_FUNCTION(other_column)
FROM table_name
GROUP BY column_name;

A Simple Example

Imagine an orders table:

order_id	customer_id	amount	status
1	101	50.00	completed
2	102	120.00	completed
3	101	30.00	completed
4	103	200.00	pending
5	102	75.00	completed

How many orders has each customer placed?

SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;

Result:

customer_id	order_count
101	2
102	2
103	1

SQL gathered all rows for customer 101 and counted them — giving you one result row per unique customer_id.

GROUP BY With Multiple Aggregate Functions

You're not limited to one aggregate per query. Here's how to get the total and average order amount per customer in one shot:

SELECT
  customer_id,
  COUNT(*)        AS order_count,
  SUM(amount)     AS total_spent,
  AVG(amount)     AS avg_order_value
FROM orders
GROUP BY customer_id;

Result:

customer_id	order_count	total_spent	avg_order_value
101	2	80.00	40.00
102	2	195.00	97.50
103	1	200.00	200.00

GROUP BY Multiple Columns

You can group by more than one column to create finer-grained buckets. Let's say you want to see order counts broken down by both customer_id and status:

SELECT
  customer_id,
  status,
  COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, status;

Result:

customer_id	status	order_count
101	completed	2
102	completed	2
103	pending	1

Each unique combination of customer_id + status becomes its own group.

Introducing HAVING: Filtering After Grouping

GROUP BY creates groups. HAVING lets you filter those groups based on the result of an aggregate function.

Think of HAVING as a WHERE clause that runs after grouping — it can reference aggregate results that don't exist until after grouping happens.

Syntax:

SELECT column_name, AGGREGATE_FUNCTION(other_column)
FROM table_name
GROUP BY column_name
HAVING AGGREGATE_FUNCTION(other_column) condition;

Example: Find High-Value Customers

Which customers have spent more than $100 in total?

SELECT
  customer_id,
  SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;

Result:

customer_id	total_spent
102	195.00
103	200.00

Customer 101 (who spent $80 total) is filtered out because their aggregate didn't meet the condition.

WHERE vs. HAVING: The Critical Difference

This trips up many beginners. Here's a clear mental model:

WHERE filters individual rows before grouping
HAVING filters groups after grouping

They can work together in the same query. Here's an example using both:

Find customers with more than one completed order:

SELECT
  customer_id,
  COUNT(*) AS completed_order_count
FROM orders
WHERE status = 'completed'        -- Filter rows BEFORE grouping
GROUP BY customer_id
HAVING COUNT(*) > 1;              -- Filter groups AFTER grouping

Result:

customer_id	completed_order_count
101	2
102	2

The WHERE status = 'completed' removes the pending order for customer 103 before any grouping occurs. Then HAVING COUNT(*) > 1 removes customer groups with only one order.

The Execution Order of a SQL Query

Understanding why WHERE and HAVING behave differently becomes much clearer when you know the logical order SQL processes a query:

FROM — identify the table(s)
WHERE — filter individual rows
GROUP BY — collapse rows into groups
HAVING — filter groups
SELECT — compute output columns
ORDER BY — sort
LIMIT/OFFSET — paginate

WHERE runs at step 2, before any grouping. HAVING runs at step 4, after groups exist. This is why you cannot use an aggregate function inside a WHERE clause — the aggregates simply don't exist yet at that stage.

Common Mistakes to Avoid

1. Selecting a non-grouped, non-aggregated column

-- ❌ This will fail (or return unpredictable results in MySQL)
SELECT customer_id, amount, COUNT(*)
FROM orders
GROUP BY customer_id;

amount is neither in GROUP BY nor wrapped in an aggregate function. The database doesn't know which amount to show for the group. Fix it: either add amount to GROUP BY, or aggregate it (e.g., SUM(amount)).

2. Using WHERE to filter aggregates

-- ❌ This will throw an error
SELECT customer_id, SUM(amount)
FROM orders
WHERE SUM(amount) > 100
GROUP BY customer_id;

SUM(amount) doesn't exist at the WHERE stage. Use HAVING instead:

-- ✅ Correct
SELECT customer_id, SUM(amount) AS total
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;

3. Forgetting that GROUP BY treats NULLs as a single group

If your grouped column has NULL values in multiple rows, all those NULL rows will be collapsed into one group. This is usually fine — just be aware that a NULL group will appear in your results unless you filter it out with WHERE column IS NOT NULL before grouping.

4. Confusing COUNT(*) and COUNT(column)

COUNT(*) counts all rows in the group, including those with NULL values
COUNT(column) counts only rows where that column is not NULL

SELECT
  customer_id,
  COUNT(*)           AS total_rows,
  COUNT(amount)      AS non_null_amounts
FROM orders
GROUP BY customer_id;

If amount is never NULL in your table, these will be equal. But in tables with optional fields, the difference matters.

A Real-World Example: Sales Report by Category

Let's put it all together with a slightly richer scenario. Here's a products table and a sales table:

-- Find product categories with more than 5 sales,
-- showing total revenue and average sale price,
-- sorted by total revenue descending
SELECT
  p.category,
  COUNT(s.sale_id)    AS total_sales,
  SUM(s.sale_price)   AS total_revenue,
  AVG(s.sale_price)   AS avg_price
FROM sales s
JOIN products p ON s.product_id = p.product_id
WHERE s.sale_date >= '2024-01-01'
GROUP BY p.category
HAVING COUNT(s.sale_id) > 5
ORDER BY total_revenue DESC;

This query:

Joins sales with products
Filters to sales from 2024 onward (WHERE)
Groups by product category
Keeps only categories with more than 5 sales (HAVING)
Sorts by revenue, highest first

Key Takeaways

GROUP BY collapses rows that share a value into summary groups; always pair it with an aggregate function
HAVING filters groups based on aggregate results — it's the post-grouping version of WHERE
WHERE and HAVING can coexist in the same query and serve complementary roles
Every column in your SELECT list must be either in GROUP BY or inside an aggregate function
SQL processes WHERE before grouping and HAVING after grouping — this is why you can't use aggregate functions in a WHERE clause

What's Next?

GROUP BY and HAVING open the door to powerful data analysis. Once you're comfortable here, great next steps include:

Window Functions — aggregate over groups without collapsing rows
CTEs — break complex GROUP BY queries into readable, step-by-step logic
Indexes on grouped columns — for performance when your tables get large

Have you run into a tricky GROUP BY or HAVING situation at work? Drop it in the comments — I'd love to see real-world examples from the community!

SQL Window Functions Explained: Stop Collapsing Your Data with GROUP BY

Vivek Kumar — Mon, 27 Apr 2026 05:23:42 +0000

You're writing a query to find each employee's salary alongside the average salary for their department. Your first instinct might be to reach for GROUP BY — but then you'd lose the individual employee rows. So you write a subquery, or a join against an aggregated CTE, and suddenly a simple question becomes a 20-line monster.

Window functions exist precisely to solve this. They let you perform calculations across a set of related rows without collapsing your result set. The row stays intact; the calculation rides alongside it. Once you understand them, you'll wonder how you ever lived without them.

This guide covers the fundamentals: what a window is, how OVER, PARTITION BY, and ORDER BY work inside it, and the most useful window functions you'll reach for every day.

What Is a Window Function?

A window function performs a calculation over a "window" — a defined set of rows related to the current row. Unlike GROUP BY, which aggregates many rows into one, a window function adds a new computed column to each row.

The syntax always follows this pattern:

function_name() OVER (
  [PARTITION BY column(s)]
  [ORDER BY column(s)]
  [ROWS/RANGE frame_clause]
)

The OVER clause is what tells SQL "this is a window function." Without it, SUM() is a regular aggregate; with it, SUM() OVER (...) is a window function.

The Setup: A Sample Schema

We'll use two tables throughout:

-- employees table
CREATE TABLE employees (
  id         INT,
  name       VARCHAR(100),
  department VARCHAR(50),
  salary     NUMERIC(10,2),
  hire_date  DATE
);

INSERT INTO employees VALUES
  (1,  'Alice',   'Engineering', 95000, '2019-03-15'),
  (2,  'Bob',     'Engineering', 88000, '2020-07-01'),
  (3,  'Carol',   'Engineering', 102000,'2018-11-20'),
  (4,  'Dave',    'Marketing',   72000, '2021-01-10'),
  (5,  'Eve',     'Marketing',   78000, '2020-05-22'),
  (6,  'Frank',   'Marketing',   68000, '2022-03-30'),
  (7,  'Grace',   'HR',          61000, '2021-09-14'),
  (8,  'Heidi',   'HR',          65000, '2019-06-01');

PARTITION BY: The Heart of Windowing

PARTITION BY divides your rows into groups — like GROUP BY, but the groups are only used to scope the calculation, not reduce the rows.

Example 1: Department average salary alongside each employee

SELECT
  name,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary
FROM employees
ORDER BY department, salary DESC;

Result:

name	department	salary	dept_avg_salary
Carol	Engineering	102000	95000.00
Alice	Engineering	95000	95000.00
Bob	Engineering	88000	95000.00
Eve	Marketing	78000	72666.67
Dave	Marketing	72000	72666.67
Frank	Marketing	68000	72666.67
Heidi	HR	65000	63000.00
Grace	HR	61000	63000.00

Every row is preserved. Each employee now has their department's average salary right next to their own. With a traditional GROUP BY, you'd need a join or subquery to get this result.

ORDER BY Inside OVER: Running Calculations

When you add ORDER BY inside OVER, SQL calculates the function cumulatively as it processes rows in order. This is perfect for running totals or running averages.

Example 2: Running total of salary by hire date within each department

SELECT
  name,
  department,
  hire_date,
  salary,
  SUM(salary) OVER (
    PARTITION BY department
    ORDER BY hire_date
  ) AS running_total
FROM employees
ORDER BY department, hire_date;

Result for Engineering:

name	department	hire_date	salary	running_total
Carol	Engineering	2018-11-20	102000	102000
Alice	Engineering	2019-03-15	95000	197000
Bob	Engineering	2020-07-01	88000	285000

The running_total grows as we move through hire dates — that's the ORDER BY hire_date doing its work.

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

Three of the most useful window functions are ranking functions. They all assign a number to each row, but they handle ties differently.

Example 3: Ranking employees by salary within their department

SELECT
  name,
  department,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees
ORDER BY department, salary DESC;

To see the difference between RANK and DENSE_RANK, let's imagine two Marketing employees with the same salary. Here's what happens:

name	salary	ROW_NUMBER	RANK	DENSE_RANK
Eve	78000	1	1	1
Dave	72000	2	2	2
Frank	68000	3	3	3

The differences become clear with ties:

ROW_NUMBER() — Always assigns a unique number, even to ties. Ties get arbitrary ordering (non-deterministic).
RANK() — Ties share the same rank, and the next rank skips numbers. Two employees tied at rank 2 means the next rank is 4.
DENSE_RANK() — Ties share the same rank, but the next rank is always consecutive. Two tied at rank 2 means the next is rank 3.

Use ROW_NUMBER() when you need exactly one row per partition (e.g., "get the single most recent order per customer"). Use DENSE_RANK() when the gap in RANK() would be confusing to end users.

A Practical Use Case: Top-N Per Group

One of the most common interview questions and real-world problems: "Give me the top 2 earners in each department."

WITH ranked_employees AS (
  SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (
      PARTITION BY department
      ORDER BY salary DESC
    ) AS salary_rank
  FROM employees
)
SELECT name, department, salary
FROM ranked_employees
WHERE salary_rank <= 2
ORDER BY department, salary DESC;

Result:

name	department	salary
Carol	Engineering	102000
Alice	Engineering	95000
Eve	Marketing	78000
Dave	Marketing	72000
Heidi	HR	65000
Grace	HR	61000

This pattern — window function in a CTE, filter in the outer query — is a workhorse of SQL analytics.

Common Mistakes and Gotchas

1. Filtering on a window function directly in WHERE

This doesn't work:

-- ❌ This will error
SELECT name, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees
WHERE rn <= 2;

Window functions are evaluated after WHERE, so you can't filter on them in the same query level. Always wrap them in a CTE or subquery first, then filter — as shown in the top-N example above.

2. Forgetting that ORDER BY inside OVER changes the frame

Adding ORDER BY to your OVER clause implicitly changes the window frame from "all rows in the partition" to "all rows up to and including the current row." This is usually what you want for running totals, but it can surprise you when you use SUM() or AVG():

-- These two behave differently!
AVG(salary) OVER (PARTITION BY department)                    -- whole partition
AVG(salary) OVER (PARTITION BY department ORDER BY hire_date) -- cumulative avg

If you want the whole-partition average but with a specific sort for row numbering, you may need to use separate window function expressions.

3. Mixing window functions and GROUP BY

Window functions and GROUP BY can coexist in the same query, but the window function operates on the already-grouped result set. This can lead to unexpected results if you're not careful about what rows exist after grouping.

4. Performance on large tables without proper indexes

Window functions with PARTITION BY and ORDER BY can be expensive. Make sure the columns you're partitioning and ordering by are indexed, especially on large tables. Use EXPLAIN ANALYZE to check the query plan.

Key Takeaways

Window functions are one of the most powerful tools in SQL for analytics and reporting:

The OVER() clause is what makes a function a window function — it defines the rows in scope for each calculation.
PARTITION BY divides rows into independent groups, like GROUP BY but without collapsing rows.
ORDER BY inside OVER enables running/cumulative calculations.
ROW_NUMBER(), RANK(), and DENSE_RANK() solve ranking and top-N problems elegantly.
You cannot filter on window function results in WHERE — use a CTE or subquery instead.

The next step after mastering these basics is exploring LAG() and LEAD() for comparing a row to previous/next rows, and frame clauses (ROWS BETWEEN) for precise control over the window range. Those are the power tools on top of this foundation.

Over to You

Window functions clicked for me the moment I stopped thinking of them as "fancy aggregates" and started thinking of them as "a calculation that knows its neighbors." What was the moment it clicked for you?

Drop your favorite window function use case in the comments — I'd love to see the creative ways people apply these in production. And if you're just getting started, try rewriting one of your correlated subqueries as a window function. You might never go back.