DEV Community: Vivek Kumar

Real-Time Metrics Dashboards with SQL: Patterns That Actually Scale

Vivek Kumar — Mon, 15 Jun 2026 09:03:05 +0000

You ship a SaaS app. A customer asks for a dashboard. "Just show us live numbers," they say. Easy.

Three weeks later, your SELECT COUNT(*) FROM events WHERE created_at > now() - interval '24 hours' query is taking eight seconds, your database CPU is pegged, and every dashboard auto-refresh slows down the rest of the app. Sound familiar?

Real-time metrics dashboards look trivial on the product roadmap and turn into a swamp the moment real data shows up. The good news: most of the hard parts have well-understood SQL patterns. You don't need a separate streaming database, a Kafka cluster, or a six-figure analytics vendor to ship a fast, live dashboard. You just need to stop treating "real-time" as one problem.

This post walks through the SQL patterns that hold up when traffic grows: pre-aggregation, materialized views, change streaming with LISTEN/NOTIFY, and how to pick the right refresh strategy for each tile. Examples use PostgreSQL, but the patterns work in MySQL, SQL Server, or any modern relational database.

What "real-time" actually means

Before writing a single query, ask: how fresh does this number need to be?

A lot of "real-time" requirements melt away under that question. A signup funnel doesn't need sub-second updates. An admin status page might need true streaming. A revenue chart for last quarter doesn't need any freshness at all. Group the tiles on your dashboard into three buckets:

Bucket	Freshness	Typical pattern
Historical	Hours to days	Pre-aggregated rollup tables, refreshed nightly
Near real-time	Seconds to minutes	Materialized views refreshed on a schedule
True real-time	Sub-second	LISTEN/NOTIFY or change-data capture pushed to clients

Most dashboards are 80% historical, 15% near real-time, and 5% true real-time. The reason your dashboard is slow is almost always that you put a tile in the wrong bucket.

Pattern 1: Pre-aggregation for historical tiles

The cheapest query is one you've already run. For anything that doesn't need to change minute-to-minute, roll it up into a separate table.

Say you have a 200 million row events table and you want a chart of daily active users for the last 90 days. Running the raw query every page load is malpractice:

-- DON'T DO THIS on every dashboard refresh
SELECT date_trunc('day', created_at) AS day,
       COUNT(DISTINCT user_id) AS dau
FROM events
WHERE created_at >= now() - interval '90 days'
GROUP BY 1
ORDER BY 1;

Instead, build a rollup table and refresh it once a day:

CREATE TABLE daily_active_users (
  day        DATE PRIMARY KEY,
  dau        INTEGER NOT NULL,
  computed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Run nightly via pg_cron, your job runner, or a scheduled task
INSERT INTO daily_active_users (day, dau)
SELECT date_trunc('day', created_at)::date AS day,
       COUNT(DISTINCT user_id)
FROM events
WHERE created_at >= current_date - interval '2 days'
  AND created_at <  current_date
GROUP BY 1
ON CONFLICT (day) DO UPDATE
  SET dau = EXCLUDED.dau,
      computed_at = now();

Your dashboard query becomes a trivial scan of 90 rows:

SELECT day, dau
FROM daily_active_users
WHERE day >= current_date - 90
ORDER BY day;

Latency drops from seconds to under a millisecond. This is the same trick used inside almost every analytics product you've ever paid for.

Pattern 2: Materialized views for near real-time

For tiles that need to be a few minutes fresh — open tickets, signups today, MRR this hour — a materialized view gives you the same precompute benefit with less plumbing.

CREATE MATERIALIZED VIEW mv_signups_today AS
SELECT plan,
       COUNT(*)                          AS signups,
       COUNT(*) FILTER (WHERE paid)      AS paid_signups,
       MAX(created_at)                   AS last_signup_at
FROM users
WHERE created_at >= current_date
GROUP BY plan;

CREATE UNIQUE INDEX ON mv_signups_today (plan);

Refresh it on a schedule:

REFRESH MATERIALIZED VIEW CONCURRENTLY mv_signups_today;

CONCURRENTLY is the magic word — it lets readers keep querying the old data while the new snapshot is built. Without it, your dashboard locks every time the view refreshes. And CONCURRENTLY requires a unique index on the view, which is why we created one above.

How often to refresh depends on the tile. A pg_cron job that runs every minute is fine for most "live" SaaS metrics:

SELECT cron.schedule('refresh-signups', '* * * * *',
  $$REFRESH MATERIALIZED VIEW CONCURRENTLY mv_signups_today$$);

Vanilla PostgreSQL materialized views always do a full rebuild — there's no built-in incremental mode. For big tables you'll want incremental view maintenance via an extension like pg_ivm, or a streaming database that maintains views incrementally for you.

Pattern 3: True real-time with LISTEN/NOTIFY

Some tiles genuinely need to update the instant something happens — an order placed, a build finished, a user joined a call. Polling for those is wasteful, especially across many concurrent dashboard users. PostgreSQL has a built-in pub/sub mechanism that's perfect for this.

A trigger emits a notification whenever a row changes:

CREATE OR REPLACE FUNCTION notify_order_change()
RETURNS TRIGGER AS $$
BEGIN
  PERFORM pg_notify(
    'orders_channel',
    json_build_object(
      'op',     TG_OP,
      'id',     NEW.id,
      'amount', NEW.amount,
      'status', NEW.status
    )::text
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_notify
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW EXECUTE FUNCTION notify_order_change();

Your app subscribes once per process and fans the update out to every dashboard client over Server-Sent Events or WebSockets:

# pseudo-Python with asyncpg
async with pool.acquire() as conn:
    await conn.add_listener('orders_channel', on_order_change)
    await asyncio.Future()  # keep the listener alive

def on_order_change(conn, pid, channel, payload):
    data = json.loads(payload)
    broadcast_to_dashboard_clients(data)

Now a dashboard with 50 active viewers makes one database connection for the listener, instead of 50 clients each polling every few seconds. This is the same idea Supabase Realtime productizes through a managed WebSocket layer.

Pattern 4: Time-bucketed aggregations that match the chart

Even with rollups in place, the SQL that drives a chart should match the chart's resolution. A line chart for the last 24 hours needs about 1,440 data points at minute granularity — anything finer just slows things down and looks worse.

SELECT date_trunc('minute', created_at) AS bucket,
       COUNT(*)                          AS event_count,
       COUNT(DISTINCT user_id)           AS unique_users
FROM events
WHERE created_at >= now() - interval '24 hours'
GROUP BY 1
ORDER BY 1;

For longer windows, drop the resolution. A monthly chart at minute granularity is 43,200 rows — your browser will choke. Hour buckets give 720 rows, daily buckets give 30. Match the bucket size to the window:

Window	Bucket	Approximate rows
Last hour	10 seconds	360
Last 24 hours	1 minute	1,440
Last 7 days	1 hour	168
Last 30 days	1 day	30
Last year	1 week	52

Pair this with the right index — usually a B-tree on created_at, and a BRIN index if the table is append-mostly and large — and these queries stay under 100 ms even on billions of rows.

Common mistakes that kill dashboards

A few patterns to avoid:

The first is counting everything on every load. SELECT COUNT(*) FROM orders on a 50 million row table will scan the table and ruin your day. Either keep a running counter in a small summary table, or use pg_class.reltuples for an estimate when exact accuracy doesn't matter.

The second is SELECT * in dashboard queries. Every extra column is bytes over the wire and memory pressure on the database. Pick the columns you need.

The third is N+1 dashboard queries. A dashboard with 12 tiles often issues 12 separate queries. With a 50 ms round trip, that's 600 ms of latency before anything renders. Batch related tiles into a single query using CTEs, or run them concurrently from the application.

The fourth is refreshing a materialized view without CONCURRENTLY. The first time it happens during business hours, you'll know. The view takes an ACCESS EXCLUSIVE lock and every other query waits.

The fifth is forgetting time zones. date_trunc('day', created_at) uses the session time zone, so a customer in Mumbai and one in Berlin will see different "today" numbers. Always be explicit: date_trunc('day', created_at AT TIME ZONE 'UTC').

Putting it together

A practical dashboard on PostgreSQL alone looks like this: historical tiles (last 90 days revenue, all-time signups) query nightly rollup tables; near real-time tiles (signups today, open tickets, MRR this week) read from materialized views refreshed every minute; the one or two true real-time tiles (active users right now, last order) subscribe to a LISTEN/NOTIFY channel and update via SSE.

That stack handles thousands of concurrent dashboard users on commodity hardware. When you outgrow it — usually around tens of millions of events per day — you can graduate to TimescaleDB continuous aggregates, ClickHouse, or a streaming database without rewriting your dashboard logic.

Key takeaways

Real-time is a spectrum, not a setting. Sort your tiles by freshness requirement first, and most of your performance problems disappear. Pre-aggregate anything historical. Use materialized views with CONCURRENTLY for near real-time. Reach for LISTEN/NOTIFY only when you genuinely need sub-second updates. Match bucket granularity to chart resolution. And test with realistic data volumes — a dashboard that's fast on 10,000 rows tells you nothing about how it behaves on 100 million.

Over to you

What's the slowest dashboard query you've ever shipped, and what fixed it? Did you end up with a rollup table, a materialized view, a streaming database, or did you just throw more hardware at it? Drop a comment — especially if you've battle-tested one of these patterns at scale, or if you use a different one entirely.

If you're building this inside your own SaaS product, the patterns above will get you far. When you want to stop hand-rolling chart components and SQL editors, tools like Draxlr, Metabase, or Cube can handle the embedding layer so you can focus on the queries.

Row-Level Security for Embedded Dashboards: A Practical Postgres Guide

Vivek Kumar — Mon, 01 Jun 2026 07:07:59 +0000

You just shipped your first embedded dashboard. Customers can log into your SaaS, click "Analytics," and see live charts of their data. Great. Then the support emails roll in.

"Why can my finance lead see the executive comp report?"
"Our intern can see everyone's pipeline."
"This customer can see another customer's invoice in a CSV export — please fix immediately."

Embedded analytics has a security model that is subtly different from your normal app authorization. Dashboards run many queries you didn't write — exports, drill-downs, ad-hoc filters, AI-generated SQL. The "just add a WHERE clause" trick that works in your handcrafted API endpoints does not work here, because there is no single endpoint anymore. Every query is a potential leak.

This article is about how to use Postgres Row-Level Security (RLS) to make embedded dashboards safe by default. We'll cover the embed-token-to-session-context flow, per-user roles within a tenant, column masking, performance, and the connection pooler gotcha that quietly breaks RLS in production.

Why "WHERE tenant_id = ?" isn't enough

The classic pattern is to stamp every row with a tenant_id and add WHERE tenant_id = :current_tenant to every query. That works when you write every query.

But in an embedded dashboard:

Users can build their own queries through the UI.
An AI assistant might generate SQL on the fly.
CSV exports run separate queries.
A drill-down chart may join three tables, any one of which could be missing the filter.
Power users might paste raw SQL into a query console.

You don't want the safety of your customers' data to depend on every developer (and every LLM) remembering the right filter. RLS moves that enforcement into the database itself — the storage layer literally refuses to return rows the current session isn't allowed to see.

The model: enable RLS, write a policy, set context per request

The three moving parts:

Enable RLS on the table.
Write a policy that uses a session variable to decide which rows are visible.
At request time, your backend sets that session variable from the verified embed token (JWT) before running any dashboard SQL.

Here's the minimum viable setup for a SaaS with an orders table:

-- 1. Enable RLS on the table
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- 2. Create a policy keyed on a session-local setting
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- 3. (Optional but recommended) force RLS even for table owners
ALTER TABLE orders FORCE ROW LEVEL SECURITY;

The variable name must contain a period — app.current_tenant, jwt.claims.user_id, etc. — so Postgres knows it's a custom setting and not a system one.

Then, in your backend, before running any dashboard query for that request:

SET LOCAL app.current_tenant = '8c1c...e9a3';

SET LOCAL scopes the value to the current transaction, which is exactly what you want — it auto-clears when the transaction ends, so a stray query later in the request can't leak it.

Wiring it to the embed JWT

The embed flow most teams use looks like this. The dashboard tool (or your own iframe) receives a signed JWT minted by your backend. That JWT carries the user's tenant and role claims. Before your backend opens a query, it verifies the JWT and pushes the claims into the session:

# Pseudocode — runs at the start of every dashboard request
claims = verify_jwt(request.token)             # tenant_id, user_id, role

with db.transaction() as tx:
    tx.execute(
        "SELECT set_config('app.current_tenant', %s, true)",
        [claims["tenant_id"]],
    )
    tx.execute(
        "SELECT set_config('app.current_user', %s, true)",
        [claims["user_id"]],
    )
    tx.execute(
        "SELECT set_config('app.current_role', %s, true)",
        [claims["role"]],
    )
    # Now run the dashboard query — RLS does the rest
    result = tx.execute(dashboard_query)

The third argument true to set_config is the transaction-local flag, equivalent to SET LOCAL. From this point on, every query the dashboard fires — including ones written by an end user or an AI — runs under the right identity.

Going beyond multi-tenancy: per-user roles inside a tenant

Most teams start with tenant_id filtering and stop there. But the real questions show up inside a tenant:

A sales rep should see only their own pipeline.
A regional manager should see their region.
The CRO should see everything in the company.
Finance should see revenue but not employee salaries.

You can express all of that in a single policy:

CREATE POLICY orders_visibility ON orders
  USING (
    tenant_id = current_setting('app.current_tenant')::uuid
    AND (
      current_setting('app.current_role') IN ('admin', 'cro')
      OR (current_setting('app.current_role') = 'manager'
          AND region = current_setting('app.current_region'))
      OR owner_user_id = current_setting('app.current_user')::uuid
    )
  );

Now an embedded chart that runs SELECT region, SUM(amount) FROM orders GROUP BY region returns different rows for the intern, the manager, and the CRO — without a single line of application code changing.

Column-level masking for sensitive fields

RLS hides rows. Sometimes you need to hide columns — show the row, but mask one field. A common pattern is a view that wraps the base table:

CREATE VIEW orders_safe AS
SELECT
  id,
  tenant_id,
  amount,
  region,
  CASE
    WHEN current_setting('app.current_role') IN ('admin', 'finance')
      THEN customer_email
    ELSE regexp_replace(customer_email, '(^.).*(@.*$)', '\1***\2')
  END AS customer_email,
  created_at
FROM orders;

Point your embedded dashboard at orders_safe instead of orders and the masking happens for free. Combine with RLS on the underlying table and you get row + column protection in one model.

A heads-up: views run with the privileges of their creator by default, which can bypass RLS unexpectedly. Either create the view as a non-superuser, or in modern Postgres set security_invoker = true on the view so it runs as the calling user.

Real-world example: a customer-facing usage dashboard

Say you're building a usage dashboard for an API product. Each customer should see their own request counts, latency, and errors. Inside the customer, only org admins should see per-user breakdowns:

ALTER TABLE api_events ENABLE ROW LEVEL SECURITY;

CREATE POLICY api_events_read ON api_events
  FOR SELECT
  USING (
    tenant_id = current_setting('app.current_tenant')::uuid
    AND (
      current_setting('app.current_role') = 'org_admin'
      OR user_id = current_setting('app.current_user')::uuid
    )
  );

CREATE INDEX api_events_tenant_user_idx
  ON api_events (tenant_id, user_id, created_at DESC);

A regular user's dashboard query like:

SELECT date_trunc('hour', created_at) AS hour,
       COUNT(*) AS requests,
       AVG(latency_ms) AS avg_latency
FROM api_events
WHERE created_at > now() - interval '24 hours'
GROUP BY 1
ORDER BY 1;

…silently returns only that user's events. An org admin gets the org-wide view from the same SQL. Your dashboard code, your AI SQL assistant, and your CSV exporter all stay identical.

Performance: don't blow up your dashboards

RLS is fast when the policy predicate is indexable. The policy tenant_id = current_setting(...) becomes part of every query plan; if tenant_id is the leading column of your indexes, you're fine. Things that quietly tank performance:

A current_setting() call that isn't marked STABLE will be re-evaluated per row. Wrap it in a STABLE SQL function and call that in the policy:

CREATE FUNCTION current_tenant() RETURNS uuid
LANGUAGE sql STABLE AS
$$ SELECT current_setting('app.current_tenant')::uuid $$;

CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_tenant());

Subquery-style policies that join other tables can be expensive. Prefer denormalising a tenant_id or owner_id onto the table over a policy like EXISTS (SELECT 1 FROM memberships WHERE ...).

Make sure your composite indexes lead with the column the RLS policy filters on — that's almost always tenant_id.

Common mistakes and gotchas

The connection pooler trap is the one that bites everyone. If you use PgBouncer in transaction or statement mode, SET (without LOCAL) persists across requests on the same backend connection, leaking context between tenants. Always use SET LOCAL (or set_config(..., true)) inside an explicit transaction.

Superusers and BYPASSRLS ignore policies entirely. Your application role must not be a superuser and must not have BYPASSRLS. Test as a normal application role, not as postgres.

Forgetting FORCE ROW LEVEL SECURITY means the table owner still sees everything. If your migrations or background jobs run as the owner, they bypass policies — sometimes that's what you want, but it's worth being explicit.

USING vs WITH CHECK. USING filters what's read, WITH CHECK validates what's written. If you only specify USING on an INSERT/UPDATE policy, a user could write rows tagged with another tenant's id. Specify both for write operations.

Views inherit creator privileges. If you create a view as postgres, queries against it can see everything. Either create views as a non-superuser, or set security_invoker = true (Postgres 15+).

No fallback policy. If no policy matches, the default behavior is to deny — which is good for security but can look like "the dashboard is empty" in dev. Add a clear deny-by-default policy and a verbose error path so missing context fails loudly during development, not silently in prod.

Key takeaways

Embedded dashboards run too many queries — including ones written by users and LLMs — for application-level filtering to be safe. Row-Level Security pushes the rules into the database, where every query is filtered automatically.

The pattern is small: enable RLS on the table, write a policy that reads current_setting('app.something'), and have your backend set those settings inside a SET LOCAL transaction after verifying the embed JWT. From there, you can layer per-user roles, column masking, and view-level transforms without touching dashboard code.

Get the connection-pool detail right, keep your indexes aligned with your policy predicates, and test as a non-superuser role. Do that and your embedded analytics layer becomes one of the safest parts of your stack instead of the scariest.

Over to you

If you're shipping embedded dashboards today, how are you handling tenant + per-user isolation? Are you using RLS, application-layer filtering, schema-per-tenant, or a BI tool's own permission model? Drop a comment with the pattern (and the gotchas) you've hit — especially around AI-generated SQL, where the "the LLM might forget the filter" problem makes RLS feel inevitable.

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. 🗓️