DEV Community: Vivek Kumar

Prompting AI for Complex Multi-Table SQL: A Practical Guide

Vivek Kumar — Fri, 26 Jun 2026 05:37:35 +0000

You paste your question into an AI tool: "Show me revenue by plan for customers who churned last quarter." The model returns a query. It looks plausible. You run it. Cartesian product. Forty million rows. Your database grinds to a halt.

The problem usually isn't the AI — it's the prompt. Multi-table SQL generation is where generic prompting falls apart. A single-table SELECT is forgiving; the model can guess. But the moment you need three tables, two JOINs, a date filter, and a GROUP BY, the model needs to know your schema precisely. Without that, it invents column names, picks the wrong join key, or misses a relationship entirely.

This guide covers the specific techniques that get AI to produce accurate, runnable multi-table SQL — the same results you'd expect from a purpose-built tool like Draxlr, but applicable wherever you're prompting an AI today.

Why Multi-Table Queries Break AI Prompts

A large language model generating SQL faces a core challenge: it doesn't know your schema unless you tell it. For simple queries it can make educated guesses — SELECT * FROM users WHERE id = 1 is hard to get wrong. But for multi-table queries, the model needs to know:

Which tables exist and what each column means
Which columns are foreign keys and what they reference
What "revenue" means in your system (is it in orders.total, invoices.amount_due, or subscription_events.mrr?)
Whether joins should be INNER or LEFT (which affects whether churned customers appear at all)

When this context is missing, the model fills the gaps with plausible-sounding guesses. That's how you get JOIN customers ON customers.id = orders.customer_id when your actual foreign key is orders.account_uuid.

Technique 1: Inject the Full Schema for Relevant Tables

The single biggest improvement you can make is including CREATE TABLE statements (or equivalent schema definitions) directly in your prompt. Don't summarize — paste the actual DDL.

Weak prompt:

I have users, orders, and subscriptions tables.
Write a query that shows revenue by plan for churned customers.

Strong prompt:

-- Schema context:
CREATE TABLE users (
  id UUID PRIMARY KEY,
  email TEXT NOT NULL,
  created_at TIMESTAMPTZ
);

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  plan_name TEXT,         -- 'starter', 'growth', 'enterprise'
  status TEXT,            -- 'active', 'canceled', 'paused'
  canceled_at TIMESTAMPTZ,
  mrr_cents INTEGER       -- monthly recurring revenue in cents
);

CREATE TABLE invoices (
  id UUID PRIMARY KEY,
  user_id UUID REFERENCES users(id),
  subscription_id UUID REFERENCES subscriptions(id),
  amount_cents INTEGER,
  paid_at TIMESTAMPTZ,
  period_start DATE,
  period_end DATE
);

-- Question: Show total revenue collected last quarter, grouped by plan,
-- for subscriptions that were canceled during that same quarter.

With this prompt, the model knows the join keys, the status values, where revenue lives, and what "churned" means. The output will be far more accurate.

Key rules for schema injection:

Include all tables involved in the query, even indirectly
Keep column comments — they're gold for business terminology
Include REFERENCES clauses so the model sees foreign key relationships explicitly
If you have an ENUM or constrained set of values, list them

Technique 2: Name the Join Path Explicitly

Even with full schema context, the model may choose an incorrect join path when multiple routes exist between tables. If invoices can be joined to users either directly via invoices.user_id or indirectly through subscriptions, tell the model which path to use.

Join invoices to subscriptions using invoices.subscription_id = subscriptions.id,
then join subscriptions to users using subscriptions.user_id = users.id.
Do NOT join invoices directly to users.

This eliminates the ambiguity that causes fan-out bugs — where joining along two paths simultaneously multiplies row counts unexpectedly.

Technique 3: Use Chain-of-Thought for Complex Logic

For queries involving subqueries, window functions, or multi-step aggregation, ask the model to reason before writing SQL. Chain-of-thought prompting produces dramatically fewer logic errors on hard queries.

Add this to your prompt:

Before writing the query, think through:
1. Which tables are needed and how they join
2. What filtering needs to happen before aggregation vs. after
3. Whether to use a subquery, CTE, or window function
Then write the final SQL.

The model's reasoning output often catches problems that would otherwise end up as bugs. For example, it might note: "Filtering on canceled_at in the WHERE clause will exclude nulls for active subscriptions — I should use HAVING or a subquery instead." That's the kind of insight that prevents incorrect results.

Here's what a well-structured CTE-based output looks like for a revenue-by-plan query:

-- Step 1: Identify subscriptions canceled in Q1 2026
WITH churned_subs AS (
  SELECT
    id,
    user_id,
    plan_name,
    canceled_at
  FROM subscriptions
  WHERE status = 'canceled'
    AND canceled_at >= '2026-01-01'
    AND canceled_at < '2026-04-01'
),

-- Step 2: Sum invoices paid during the same period for those subscriptions
revenue_by_sub AS (
  SELECT
    cs.plan_name,
    SUM(i.amount_cents) AS total_revenue_cents
  FROM churned_subs cs
  JOIN invoices i
    ON i.subscription_id = cs.id
  WHERE i.paid_at >= '2026-01-01'
    AND i.paid_at < '2026-04-01'
  GROUP BY cs.plan_name
)

SELECT
  plan_name,
  ROUND(total_revenue_cents / 100.0, 2) AS total_revenue_usd
FROM revenue_by_sub
ORDER BY total_revenue_usd DESC;

The CTE structure is a direct result of chain-of-thought reasoning — the model decomposed the problem into steps before writing a single line of SQL.

Technique 4: Provide a Few-Shot Example

If you repeatedly query the same database, including one working example in your prompt dramatically improves accuracy. Show the model a question it already got right, plus the SQL that produced the correct result.

Example:
Question: "How many active subscriptions does each user have?"
SQL:
  SELECT u.email, COUNT(s.id) AS active_subs
  FROM users u
  LEFT JOIN subscriptions s
    ON s.user_id = u.id AND s.status = 'active'
  GROUP BY u.email;

Now answer: "Show the most recent invoice date for each active subscriber."

The example teaches the model your naming conventions, your preferred JOIN style, and which table is the "primary" source of truth — without you having to explain it again.

Technique 5: Constrain the Output Format

AI tools often produce multiple variants, verbose explanations, or queries with placeholder values. If you're feeding output directly into an application or testing pipeline, add explicit output constraints:

Return only the SQL query. No explanations. No markdown fences.
Use aliases for all column names. Use CTEs instead of nested subqueries.
Do not use SELECT *.

This makes the output machine-readable and enforces the style your team has standardized on.

Common Mistakes and How to Avoid Them

Forgetting NULL semantics on LEFT JOINs. Ask the model to use LEFT JOIN when you want to include rows with no match (e.g., users with zero invoices). If you use INNER JOIN, those rows silently disappear.

Ambiguous column names across tables. If both users and subscriptions have a created_at, the model may reference the wrong one. Qualify everything: users.created_at, subscriptions.created_at.

Business terms without definitions. "Active customers" means different things in different systems. Does it mean status = 'active'? Logged in within 30 days? Has a paid invoice? Define it in the prompt: "Active means the subscription has status = 'active' and canceled_at IS NULL."

Assuming the model remembers prior context. Each new prompt is (usually) stateless. Re-inject your schema and any previously-established definitions every time.

Key Takeaways

Getting accurate multi-table SQL from AI isn't about picking the right tool — it's about giving any tool enough context to reason correctly. The techniques that matter most:

Paste your CREATE TABLE statements, including foreign keys and column comments
Name the join path explicitly when multiple routes exist
Use chain-of-thought prompting for queries with subqueries or complex aggregation
Include one working example query to anchor naming conventions
Constrain the output format for consistent, machine-readable results

The more context you give, the less the model has to guess — and guessing is where multi-table SQL generation goes wrong.

What's the most complex multi-table query you've successfully generated with AI? Did you find a prompting technique that made a big difference? Drop it in the comments — I'd love to see what's working in production.

AI SQL Assistants: What to Actually Look For Before You Commit

Vivek Kumar — Thu, 25 Jun 2026 05:33:55 +0000

You've seen the demos. An AI tool magically translates "show me which customers churned last month" into a tidy 15-line SQL query. Looks impressive. But then you paste it into your database client and get a column-not-found error — because the AI invented a column called users.full_name when your schema has first_name and last_name stored separately.

That gap between the demo and reality is exactly where most comparisons of AI SQL tools fall short. Benchmark scores and marketing copy tell you very little about whether a tool will hold up against your schema, your query complexity, and your team's workflow.

This article gives you the evaluation framework that actually matters. (If you're already shortlisting options, Draxlr's AI SQL features are worth adding to the list.)

Why AI SQL Tools Fail in the Real World

Before comparing tools, it helps to understand where they tend to break down.

General-purpose LLMs (ChatGPT, Claude, Gemini used raw) are trained on enormous amounts of SQL from the public internet. They know SQL syntax extremely well. What they don't know is your schema. Without that context, they guess — and they guess plausibly. Studies have found that up to 30% of intermediate queries generated by general-purpose models reference columns or tables that don't exist.

This is the hallucination problem, and it's the single most important axis to evaluate any AI SQL tool on.

The second failure mode is join logic. Multi-table queries are where even schema-aware tools stumble. Incorrect join conditions, missing bridge tables, wrong cardinality assumptions — these produce queries that run without errors but return wrong numbers. Silently wrong results are far more dangerous than errors.

With that in mind, here are the five things that actually separate a useful AI SQL assistant from an expensive autocomplete.

1. Schema Awareness: Does It Know Your Tables?

This is the most fundamental differentiator.

A schema-aware tool has access to your actual CREATE TABLE statements, column names, data types, and foreign key relationships before it generates a single character of SQL. A schema-agnostic tool (most chat-based LLMs used out of the box) is guessing based on your description.

The difference shows up immediately with non-obvious naming conventions:

-- What a schema-agnostic tool might generate
SELECT u.full_name, o.total_price
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed';

-- What schema-aware output looks like when your schema uses
-- first_name/last_name and amount_cents
SELECT u.first_name, u.last_name, o.amount_cents / 100.0 AS total_price
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'completed';

The first query throws an error. The second runs and returns the right data.

What to test: Give the tool a query that involves a column with a non-obvious name (e.g., arr_usd, mrr_delta_30d, is_churned_flag). Does it use the actual name, or does it invent something sensible-sounding?

2. Multi-Table Join Accuracy

Single-table queries are easy. The real test is how a tool handles schemas with 20+ tables, many-to-many relationships through bridge tables, and foreign keys that don't follow the {table}_id convention.

Consider a SaaS schema where you need to find all users on an active subscription who have submitted more than 3 support tickets in the last 30 days:

-- This requires four tables and two join conditions
-- that aren't obvious from column names alone

SELECT
  u.id,
  u.email,
  s.plan_name,
  COUNT(t.id) AS ticket_count
FROM users u
JOIN subscriptions s ON s.account_id = u.account_id
JOIN accounts a ON a.id = u.account_id
JOIN support_tickets t ON t.submitted_by = u.id
WHERE
  s.status = 'active'
  AND t.created_at >= NOW() - INTERVAL '30 days'
GROUP BY u.id, u.email, s.plan_name
HAVING COUNT(t.id) > 3
ORDER BY ticket_count DESC;

An AI tool that doesn't know your schema might join support_tickets on user_id instead of submitted_by, or miss the accounts table entirely and produce a query with a Cartesian product hiding inside it.

What to test: Ask for a query that requires 3+ tables. Check join conditions manually before running anything in production.

3. Explainability: Does It Show Its Work?

A good AI SQL assistant doesn't just return a query — it explains what it generated and why. This matters for two reasons:

First, you can catch mistakes before they run. If the explanation says "I joined on users.id = orders.customer_id" and you know the actual foreign key is orders.user_id, you've caught a bug before it runs.

Second, you learn from it. Developers who use AI SQL tools effectively treat them as a pair programmer, not a magic box. When the tool explains a window function or a lateral join, you build intuition you can apply next time.

Tools that return SQL with no explanation give you no surface area to catch errors. Treat explainability as a first-class feature, not a nice-to-have.

4. Workflow Integration: Where Do You Actually Work?

The best AI SQL tool is the one you'll actually use. That depends on where your SQL lives.

If you write SQL inside application code (Django ORM layers, Rails migrations, service objects), a tool that integrates with your IDE (GitHub Copilot, Cursor) is more useful than a standalone web app. It sees your ORM models and migration files, which serve as implicit schema context.

If you're writing SQL in a query editor (Metabase, DBeaver, psql, Redash), you want a tool that integrates with that environment or at minimum makes it easy to paste your schema in. Standalone text-to-SQL web apps like AI2SQL fall into this category — you provide the schema, they produce the query.

If your team needs to query the database directly without writing SQL (non-technical stakeholders, ops teams), you want a tool that handles the schema management automatically and requires no prompt engineering from end users.

The tool category matters as much as the tool itself.

5. Accuracy on Your Specific Query Patterns

Industry benchmarks quote 85–95% accuracy for modern AI SQL tools. But "accuracy" in these benchmarks typically means "syntactically valid and logically correct on a standardized test schema." Your schema is not standardized.

Run your own benchmark before you commit. Take 10–15 representative queries that your team runs frequently — the ones that matter most for your business logic — and test each tool against them. Score each result:

Query	Schema-aware tool	General-purpose LLM	Notes
Monthly active users by plan	✅ Correct	⚠️ Wrong join	LLM missed bridge table
Churn rate last 90 days	✅ Correct	❌ Column not found	Invented column name
Revenue by cohort (monthly)	⚠️ Close	❌ Wrong result	Both missed date truncation
Avg time to first value	✅ Correct	✅ Correct	Simple enough for both

This takes an hour and tells you more than any published benchmark.

Common Mistakes When Evaluating AI SQL Tools

Testing on toy schemas. A tool that handles users and orders may fail on your real schema with 40 tables, legacy naming conventions, and non-obvious relationships. Always test with your actual schema.

Trusting results without checking. AI tools that return SQL with no errors can still return logically wrong results. Until you've built trust in a tool's output, always sanity-check against known data before using output in production.

Optimizing for speed over correctness. The fastest tool that generates wrong SQL is worse than a slower tool that generates correct SQL. For complex analytics queries, speed matters much less than accuracy.

Ignoring context window limits. Large schemas can exceed the context window of some tools, causing them to silently drop tables from consideration. Check whether your tool handles large schemas gracefully or truncates them.

Key Takeaways

The most important feature in an AI SQL assistant isn't the interface or the model — it's schema awareness. A tool that knows your actual table structure will outperform a smarter model that's guessing.

Before you choose:

Test with your real schema, not a toy example
Specifically probe multi-table joins and non-obvious column names
Check that the tool explains its output so you can catch errors
Match the tool category to where your SQL actually lives
Run your own accuracy benchmark on queries that matter to your business

AI SQL tools are genuinely useful. They save real time on boilerplate queries, help you remember syntax for infrequent operations, and make it faster to prototype analytics. But the ones that save you time are the ones that know your schema — the rest can quietly make things slower by generating plausible-looking queries that don't actually work.

Have you evaluated AI SQL tools for your team? What criteria mattered most? Drop your experience in the comments — especially if you've found a tool that handles large or unconventional schemas well.

The Fastest Way to Add Reporting to Your Internal Tool

Vivek Kumar — Wed, 24 Jun 2026 04:36:02 +0000

You built a solid internal tool. It manages customers, tracks orders, handles operations. Your team lives in it every day.

Then someone in leadership asks: "Can we get a report showing monthly revenue by plan type, broken down by region?"

And you realize — your tool has no reporting layer. No charts. No exports that make sense. Just raw data sitting in a database, and a Slack message you're trying to figure out how to answer.

This is one of the most common pain points for developers building internal tools. The app works great. But reporting gets bolted on as an afterthought, and you end up spending a week building a half-baked export feature, or worse — someone starts maintaining a spreadsheet manually synced from the database.

There's a faster path. Tools like Draxlr connect directly to your database and can have a working dashboard up in minutes. But whether you use a tool or build it yourself, the approach is the same — let's walk through it.

Why Developers Put Off Reporting (Until It's Urgent)

Reporting feels deceptively simple — "just run a query and show a chart." But in practice, you're solving multiple problems at once:

Data access: Who should see what? Should your CS team see revenue numbers?
Query complexity: Business metrics rarely map to a single clean table. They require joins, aggregations, date bucketing, and edge-case handling.
Freshness: Should the report be live, or cached? How often does it need to update?
Presentation: A raw SQL result set isn't a report. Charts, filters, and formatting all take time.
Maintenance: Metrics change. The query you write today will need updates next quarter.

Most developers underestimate this surface area and either overbuild (a full custom analytics module) or underbuild (a CSV export button). Neither serves the team well.

The Two Paths — and Why One Is Much Faster

Path 1: Build it yourself. You write query logic, wire up a charting library (Chart.js, Recharts, etc.), build filter controls, handle date ranges, add pagination, and manage permissions. For a non-trivial set of reports, this is 2–4 weeks of engineering work, minimum.

Path 2: Connect your database to a SQL dashboard tool. Your database already has the data. You write the queries (the part only you can do), and a tool handles the rest — visualization, sharing, access control, and filters.

Path 2 is almost always faster, and for internal tooling it's almost always good enough.

The key is picking the right queries to expose, and structuring them cleanly. Let's do that.

Step 1: Identify the 5–8 Metrics That Actually Matter

Don't build reporting for everything. Talk to the actual users of your internal tool and find out what they check daily, weekly, or monthly. This is usually a short list:

New signups this week vs. last week
Revenue by plan / subscription tier
Churn rate this month
Support tickets opened vs. closed
Active users by feature

Resist the urge to build an all-encompassing analytics suite. Start with 5–8 metrics, ship them, and iterate. Your team will tell you what's missing.

Step 2: Write Clean, Reusable SQL Queries

Here's where you add the most value. Write the queries carefully — they're the foundation everything else will sit on.

Example: New signups per week

SELECT
  DATE_TRUNC('week', created_at) AS week,
  COUNT(*) AS new_signups
FROM users
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

Example: Monthly recurring revenue by plan

SELECT
  DATE_TRUNC('month', s.started_at) AS month,
  s.plan_name,
  SUM(s.monthly_amount) AS mrr
FROM subscriptions s
WHERE s.status = 'active'
GROUP BY 1, 2
ORDER BY 1, 2;

Example: Churn rate this month

SELECT
  COUNT(*) FILTER (WHERE canceled_at >= DATE_TRUNC('month', NOW())) AS churned_this_month,
  COUNT(*) FILTER (WHERE started_at < DATE_TRUNC('month', NOW())) AS active_start_of_month,
  ROUND(
    100.0 * COUNT(*) FILTER (WHERE canceled_at >= DATE_TRUNC('month', NOW())) /
    NULLIF(COUNT(*) FILTER (WHERE started_at < DATE_TRUNC('month', NOW())), 0),
    2
  ) AS churn_rate_pct
FROM subscriptions;

Example: Feature usage over the last 30 days

SELECT
  event_name,
  COUNT(DISTINCT user_id) AS unique_users,
  COUNT(*) AS total_events
FROM events
WHERE created_at >= NOW() - INTERVAL '30 days'
  AND event_type = 'feature_used'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 20;

Write these in a place where you can share and version them — even a reports/ directory in your repo works fine.

Step 3: Connect Your Database to a Dashboard Tool

Once your queries are solid, point a SQL dashboard tool at your database. Most support direct connections to PostgreSQL, MySQL, or SQLite. You paste in the query, set up the visualization, and you're done.

Here's what a setup typically looks like end-to-end:

Step	What You Do	Time
Connect DB	Add host, port, credentials, SSL cert	5 minutes
Add queries	Paste in your pre-written SQL, test it	10–15 minutes per metric
Choose visualizations	Pick chart type (line, bar, number card)	2–3 minutes per chart
Build dashboard	Arrange charts, add titles and filters	20–30 minutes
Set permissions	Share link or restrict by role	5 minutes

Total time to a working internal report: a few hours, not a few weeks.

Step 4: Add a Date Range Filter (Don't Skip This)

The most common thing users will want to do is change the time window — "show me last month" or "show me Q1." Hardcoding dates in your queries makes this painful.

A better pattern is to parameterize the date range at the dashboard level. Most SQL dashboard tools support query parameters:

-- With parameterized date range (Draxlr / Metabase style)
SELECT
  DATE_TRUNC('day', created_at) AS day,
  COUNT(*) AS signups
FROM users
WHERE created_at BETWEEN {{start_date}} AND {{end_date}}
GROUP BY 1
ORDER BY 1;

The tool injects user-selected values at query time. You get a filter for free without any frontend code.

Common Mistakes to Avoid

Mistake 1: Including test accounts in your metrics

Your internal test@yourcompany.com users will skew signup counts and event metrics. Always filter them out:

WHERE email NOT LIKE '%@yourcompany.com'
  AND is_test = false

Mistake 2: Not handling soft-deleted records

If your app uses soft deletes (a deleted_at column), every query needs to account for them. Missing this makes your user counts and revenue figures wrong:

-- Wrong: counts deleted users
SELECT COUNT(*) FROM users;

-- Right: only active users
SELECT COUNT(*) FROM users WHERE deleted_at IS NULL;

Mistake 3: Displaying stale data without saying so

If your dashboard queries run on demand against a production replica, it's fine. But if you're caching results, show the last-refreshed timestamp. Nothing erodes trust faster than someone making a decision on data that's 3 days old without knowing it.

Mistake 4: Treating the dashboard as done

Business metrics evolve. Plan names change. New features get added. The query that was accurate in January might be wrong by June. Schedule a monthly review of your key reports to verify the numbers still make sense.

Mistake 5: One giant query that does everything

It's tempting to write one mega-query that produces a wide result set for multiple charts. Don't. Separate concerns into separate queries. They're easier to debug, easier to update, and run more efficiently.

Key Takeaways

Most internal tools need reporting far sooner than developers plan for it.
The fastest path is: write good SQL queries → connect them to a dashboard tool → share with your team.
Start with 5–8 metrics, not a full analytics platform.
Parameterize date ranges so non-technical users can explore the data themselves.
Always filter test accounts, handle soft deletes, and schedule periodic query reviews.

The engineering work worth doing here is the SQL — understanding your data model well enough to write accurate, meaningful queries. The rest (visualization, sharing, filters, permissions) is where a good SQL dashboard tool earns its place.

What does your internal reporting stack look like? Are you building it yourself, using a tool, or still on the "forward it to Slack" system? Drop a comment — I'm curious how other teams handle this.

Building an Analytics Layer on Top of Your Existing Database

Vivek Kumar — Tue, 23 Jun 2026 04:23:34 +0000

You're six months into your SaaS product. The app works. Users are signing up, data is flowing, and someone on the team says, "Can we get a dashboard showing churn by plan tier?" So you write a query. It touches five tables, scans 2 million rows, and takes 14 seconds to run. You slap it on a dashboard. Now every page load is slow and your database CPU spikes to 90% every five minutes.

This is the moment most teams realize their production database and their analytics needs are fundamentally at odds. Transactional databases are optimized for fast writes and point lookups. Analytics workloads are the opposite — broad scans, heavy aggregations, multiple joins. Running them on the same database, without any abstraction, will hurt both.

The fix isn't a full data warehouse migration (not yet). It's building a lightweight analytics layer on top of what you already have — and once that layer is in place, a tool like Draxlr can sit on top of it to turn your SQL into shareable dashboards without extra infrastructure.

What Is an "Analytics Layer"?

It's not a separate product. It's a set of architectural patterns and SQL structures that sit between your raw application data and your reporting queries. The goal is to:

Isolate analytical queries from production traffic
Pre-compute expensive aggregations
Give consistent, fast query surfaces to dashboards and reports

You can implement most of this with three tools you probably already have access to: read replicas, materialized views, and summary tables.

Pattern 1: Route Analytics Traffic to a Read Replica

The simplest first step. If you're on Postgres, MySQL, or most managed databases (RDS, Supabase, PlanetScale), you can spin up a read replica in minutes.

Send all analytics queries there. Your primary database handles writes and critical application reads. Your replica handles reporting.

-- On the replica: safe to run this heavy aggregation
SELECT
  DATE_TRUNC('week', created_at) AS week,
  plan_tier,
  COUNT(*) AS new_subscriptions,
  SUM(mrr_cents) / 100.0 AS weekly_mrr
FROM subscriptions
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC;

The replication lag is usually under a second. For dashboards showing weekly or monthly trends, that's completely acceptable. You don't need real-time accuracy for a chart that resets daily.

When this alone isn't enough: the replica still has to scan the same unindexed columns, and if your reporting queries are very expensive, they'll slow the replica down. That's where materialized views come in.

Pattern 2: Materialize Your Expensive Aggregations

A materialized view is a stored query result — it looks like a table, reads like a table, but is populated by running a query. You refresh it on a schedule, not on every read.

-- Create once
CREATE MATERIALIZED VIEW daily_revenue_summary AS
SELECT
  DATE_TRUNC('day', charged_at) AS day,
  plan_tier,
  COUNT(DISTINCT user_id) AS paying_users,
  SUM(amount_cents) / 100.0 AS revenue
FROM charges
WHERE status = 'succeeded'
GROUP BY 1, 2;

-- Index it like a normal table
CREATE INDEX ON daily_revenue_summary (day DESC);

-- Refresh on a schedule (e.g., via pg_cron every hour)
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_revenue_summary;

Now your dashboard query becomes:

SELECT * FROM daily_revenue_summary
WHERE day >= CURRENT_DATE - INTERVAL '30 days'
ORDER BY day DESC;

That query runs in milliseconds instead of seconds. The expensive aggregation happens offline, during the refresh, not on every dashboard load.

CONCURRENTLY is key — it lets Postgres refresh the view without locking reads, so dashboards stay live while the data updates.

Pattern 3: Build Summary Tables for Cross-Tenant Reporting

If you have a multi-tenant app, you often need aggregations at the tenant level — "how many events did each account trigger this week?" Doing this live on a large events table is brutal.

Instead, maintain a tenant_daily_stats table that your background jobs write to:

CREATE TABLE tenant_daily_stats (
  tenant_id UUID NOT NULL,
  stat_date DATE NOT NULL,
  event_count INTEGER DEFAULT 0,
  active_users INTEGER DEFAULT 0,
  api_calls INTEGER DEFAULT 0,
  PRIMARY KEY (tenant_id, stat_date)
);

Your nightly job (or a scheduled SQL function) computes and upserts:

INSERT INTO tenant_daily_stats (tenant_id, stat_date, event_count, active_users, api_calls)
SELECT
  tenant_id,
  CURRENT_DATE - 1 AS stat_date,
  COUNT(*) AS event_count,
  COUNT(DISTINCT user_id) AS active_users,
  SUM(CASE WHEN source = 'api' THEN 1 ELSE 0 END) AS api_calls
FROM events
WHERE created_at >= CURRENT_DATE - 1
  AND created_at < CURRENT_DATE
GROUP BY tenant_id
ON CONFLICT (tenant_id, stat_date)
DO UPDATE SET
  event_count = EXCLUDED.event_count,
  active_users = EXCLUDED.active_users,
  api_calls = EXCLUDED.api_calls;

This table becomes the backbone of your usage dashboards — fast reads, narrow scans, always indexed on tenant_id and stat_date.

Pattern 4: Use CTEs to Build Reusable Query Layers

Even without materializing anything, you can structure your analytics SQL to be more maintainable by using CTEs as named intermediate layers.

WITH
active_users AS (
  SELECT DISTINCT user_id
  FROM sessions
  WHERE started_at >= CURRENT_DATE - INTERVAL '30 days'
),

paid_users AS (
  SELECT user_id
  FROM subscriptions
  WHERE status = 'active'
    AND plan_tier != 'free'
),

churned_last_month AS (
  SELECT user_id
  FROM subscriptions
  WHERE status = 'cancelled'
    AND cancelled_at >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '1 month')
    AND cancelled_at < DATE_TRUNC('month', CURRENT_DATE)
)

SELECT
  COUNT(DISTINCT au.user_id) AS mau,
  COUNT(DISTINCT pu.user_id) AS paying_mau,
  COUNT(DISTINCT cl.user_id) AS churned_last_month
FROM active_users au
LEFT JOIN paid_users pu USING (user_id)
LEFT JOIN churned_last_month cl USING (user_id);

This is much easier to debug and hand off to teammates than a single nested query. And when performance becomes a concern, CTEs that are used multiple times are good candidates for materialization.

Common Mistakes to Avoid

Running analytics queries on the primary without indexes. Analytics queries tend to filter and group on columns that aren't indexed for write performance — things like DATE_TRUNC(created_at) or plan_tier. Add indexes on your replica or summary tables for the columns your dashboards actually filter on.

Refreshing materialized views too aggressively. Refreshing a large materialized view every minute defeats the purpose. Match the refresh frequency to the business need — most SaaS metrics dashboards are fine with hourly or even daily refresh.

Not setting query timeouts. An accidental cross-join on a large table can take down a database. Set statement_timeout for your analytics connection:

SET statement_timeout = '30s';

This is especially important if you're giving any external access to your analytics layer.

Mixing OLTP and analytics in the same connection pool. Use a separate connection string (pointing to your replica or analytics DB) for reporting workloads. This prevents a slow report from consuming connections needed for user-facing requests.

Building too much before you need it. Start with a read replica and one or two materialized views. You don't need a full analytics database or a data warehouse until your raw tables are genuinely too large to query reasonably. Most SaaS apps under 50M rows don't.

Key Takeaways

Your analytics layer doesn't have to be complicated:

Read replica for routing analytics traffic away from production
Materialized views for expensive aggregations that don't need to be real-time
Summary tables for per-tenant or per-dimension stats you need fast and often
CTEs for organizing complex analytical SQL into readable, maintainable stages
Timeouts and indexes to protect the database and keep queries fast

Most teams can get 80% of the way there with just materialized views and a read replica — no new infrastructure, no new tools, no migration. Just SQL used deliberately.

What's your current setup for separating analytics from production traffic? Are you using a replica, a separate analytics database, or still querying production directly? Drop your approach in the comments — I'd love to know what's working.

Product Analytics with SQL: Tracking What Actually Matters

Vivek Kumar — Sun, 21 Jun 2026 16:48:55 +0000

Most product teams have the opposite of an analytics problem. They have too many metrics. Page views, session counts, click-through rates, bounce rates — dashboards full of numbers that don't clearly connect to whether the product is actually working.

SQL changes this. When you query your own event data directly, you stop asking "what does the tool show me?" and start asking "what do I actually need to know?" Tools like Draxlr let you run these queries as live dashboards your team can revisit without re-running anything manually. That shift in framing is worth more than any analytics SaaS subscription.

This article covers the SQL queries that genuinely move the needle: active users, conversion funnels, cohort retention, and feature adoption. All with realistic table structures you can adapt to your own schema.

Your Starting Schema

Most product databases have some version of these three tables. The queries below assume this structure:

-- Who signed up and when
CREATE TABLE users (
  id          SERIAL PRIMARY KEY,
  email       TEXT,
  plan        TEXT,        -- 'free', 'pro', 'enterprise'
  created_at  TIMESTAMPTZ
);

-- Every meaningful action a user takes
CREATE TABLE events (
  id          SERIAL PRIMARY KEY,
  user_id     INT REFERENCES users(id),
  event_name  TEXT,        -- 'signed_up', 'invited_team', 'created_dashboard', etc.
  properties  JSONB,
  occurred_at TIMESTAMPTZ
);

-- Revenue events
CREATE TABLE subscriptions (
  id            SERIAL PRIMARY KEY,
  user_id       INT REFERENCES users(id),
  plan          TEXT,
  started_at    TIMESTAMPTZ,
  cancelled_at  TIMESTAMPTZ
);

If your schema looks different, the patterns still apply — you'll just swap column names.

1. Daily and Monthly Active Users (DAU / MAU)

The most fundamental health metric. An "active" user is one who triggered at least one event in the time window.

-- Daily Active Users for the last 30 days
SELECT
  DATE_TRUNC('day', occurred_at)::DATE AS day,
  COUNT(DISTINCT user_id)              AS dau
FROM events
WHERE occurred_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

-- Monthly Active Users, last 6 months
SELECT
  DATE_TRUNC('month', occurred_at)::DATE AS month,
  COUNT(DISTINCT user_id)                AS mau
FROM events
WHERE occurred_at >= NOW() - INTERVAL '6 months'
GROUP BY 1
ORDER BY 1;

DAU/MAU ratio is a sticky-product signal. A ratio above 0.2 (20% of monthly users are active on any given day) is generally considered healthy for a SaaS product.

-- DAU/MAU ratio for current month
WITH dau AS (
  SELECT
    DATE_TRUNC('day', occurred_at)::DATE AS day,
    COUNT(DISTINCT user_id) AS daily_users
  FROM events
  WHERE occurred_at >= DATE_TRUNC('month', NOW())
  GROUP BY 1
),
mau AS (
  SELECT COUNT(DISTINCT user_id) AS monthly_users
  FROM events
  WHERE occurred_at >= DATE_TRUNC('month', NOW())
)
SELECT
  AVG(daily_users)::NUMERIC(5,2)                    AS avg_dau,
  mau.monthly_users,
  (AVG(daily_users) / mau.monthly_users)::NUMERIC(4,3) AS dau_mau_ratio
FROM dau, mau
GROUP BY mau.monthly_users;

2. Conversion Funnel

Funnels answer: "how many users make it from step A to step B?" The classic case is signup-to-activation.

-- Signup → first dashboard created → invited a team member
WITH step1 AS (
  SELECT DISTINCT user_id
  FROM events
  WHERE event_name = 'signed_up'
    AND occurred_at >= NOW() - INTERVAL '90 days'
),
step2 AS (
  SELECT DISTINCT e.user_id
  FROM events e
  JOIN step1 s ON e.user_id = s.user_id
  WHERE e.event_name = 'created_dashboard'
    AND e.occurred_at > (
      SELECT MIN(occurred_at) FROM events
      WHERE user_id = e.user_id AND event_name = 'signed_up'
    )
),
step3 AS (
  SELECT DISTINCT e.user_id
  FROM events e
  JOIN step2 s ON e.user_id = s.user_id
  WHERE e.event_name = 'invited_team_member'
)
SELECT
  COUNT(DISTINCT step1.user_id)                              AS signed_up,
  COUNT(DISTINCT step2.user_id)                              AS created_dashboard,
  COUNT(DISTINCT step3.user_id)                              AS invited_team,
  ROUND(COUNT(DISTINCT step2.user_id)::NUMERIC /
        NULLIF(COUNT(DISTINCT step1.user_id), 0) * 100, 1)  AS pct_to_step2,
  ROUND(COUNT(DISTINCT step3.user_id)::NUMERIC /
        NULLIF(COUNT(DISTINCT step2.user_id), 0) * 100, 1)  AS pct_to_step3
FROM step1
LEFT JOIN step2 ON TRUE
LEFT JOIN step3 ON TRUE;

Example output:

signed_up	created_dashboard	invited_team	pct_to_step2	pct_to_step3
1200	684	291	57.0%	42.5%

That 57% to first dashboard tells you: four in ten users who sign up never even create a dashboard. That's your biggest lever, and you wouldn't have known it from page view analytics.

3. Cohort Retention

Funnel analysis shows a snapshot. Cohort retention shows whether you're getting better over time. You group users by signup month, then track what percentage return each month after.

WITH cohorts AS (
  -- Month each user signed up
  SELECT
    user_id,
    DATE_TRUNC('month', created_at) AS cohort_month
  FROM users
),
activity AS (
  -- Every month each user was active
  SELECT
    user_id,
    DATE_TRUNC('month', occurred_at) AS active_month
  FROM events
  GROUP BY 1, 2
),
retention AS (
  SELECT
    c.cohort_month,
    EXTRACT(EPOCH FROM (a.active_month - c.cohort_month)) / 2592000 AS months_since_signup,
    COUNT(DISTINCT c.user_id) AS retained_users
  FROM cohorts c
  JOIN activity a ON c.user_id = a.user_id
  GROUP BY 1, 2
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(*) AS cohort_size
  FROM cohorts
  GROUP BY 1
)
SELECT
  r.cohort_month,
  r.months_since_signup::INT AS month_n,
  r.retained_users,
  cs.cohort_size,
  ROUND(r.retained_users::NUMERIC / cs.cohort_size * 100, 1) AS retention_pct
FROM retention r
JOIN cohort_sizes cs ON r.cohort_month = cs.cohort_month
WHERE r.months_since_signup >= 0
ORDER BY 1, 2;

Run this and you get a cohort table. Month 0 is always 100% (everyone is "active" the month they signed up). Month 1 is typically where you see the steepest drop. If you see month-3 retention improving across successive cohorts, your product changes are working.

4. Feature Adoption

Not all events are equal. The ones that correlate with retention and revenue are your "power features." Here's how to find which features your most-retained users actually use:

-- Feature usage rate among users still active after 60 days
WITH retained_users AS (
  SELECT DISTINCT u.id AS user_id
  FROM users u
  JOIN events e ON u.id = e.user_id
  WHERE u.created_at <= NOW() - INTERVAL '60 days'
    AND e.occurred_at >= u.created_at + INTERVAL '60 days'
),
all_new_users AS (
  SELECT id AS user_id
  FROM users
  WHERE created_at <= NOW() - INTERVAL '60 days'
)
SELECT
  e.event_name,
  COUNT(DISTINCT CASE WHEN r.user_id IS NOT NULL THEN e.user_id END) AS retained_users_used,
  COUNT(DISTINCT a.user_id)                                          AS total_cohort,
  COUNT(DISTINCT e.user_id)                                          AS total_users_used,
  ROUND(
    COUNT(DISTINCT CASE WHEN r.user_id IS NOT NULL THEN e.user_id END)::NUMERIC /
    NULLIF(COUNT(DISTINCT e.user_id), 0) * 100, 1
  ) AS adoption_among_retained_pct
FROM events e
JOIN all_new_users a ON e.user_id = a.user_id
LEFT JOIN retained_users r ON e.user_id = r.user_id
WHERE e.event_name NOT IN ('signed_up', 'logged_in', 'page_viewed')
GROUP BY 1
ORDER BY adoption_among_retained_pct DESC
LIMIT 20;

Features where adoption_among_retained_pct is significantly higher than the overall adoption rate are your stickiest features — the ones worth investing in and putting in front of new users faster.

5. Time-to-Activation

How long does it take a new user to reach your "aha moment"? Shorter is almost always better.

-- Average and median time from signup to first key action
SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM (first_action.occurred_at - u.created_at)) / 3600
  ) AS median_hours_to_activation,
  AVG(
    EXTRACT(EPOCH FROM (first_action.occurred_at - u.created_at)) / 3600
  )::NUMERIC(8,1)         AS avg_hours_to_activation,
  COUNT(*)                AS activated_users
FROM users u
JOIN LATERAL (
  SELECT MIN(occurred_at) AS occurred_at
  FROM events
  WHERE user_id = u.id
    AND event_name = 'created_dashboard'
) first_action ON first_action.occurred_at IS NOT NULL
WHERE u.created_at >= NOW() - INTERVAL '90 days';

If median time-to-activation is 4 days but you have a 7-day trial, that's a problem hiding in plain sight.

Common Mistakes and Gotchas

Counting events instead of users. COUNT(*) on an events table counts actions, not people. Use COUNT(DISTINCT user_id) whenever you're measuring engagement or adoption.

Off-by-one in funnel ordering. A funnel step should only count if the user did it after the previous step — not just at any point in history. The subquery pattern in the funnel example above handles this, but hand-rolled funnels often miss it.

Using NOW() in cohort queries. The current month's cohort is always incomplete. Filter to only full months (e.g., < DATE_TRUNC('month', NOW())) to avoid misleading drop-offs in your retention chart.

Conflating activity with value. A logged_in event proves presence, not engagement. Filter out noise events from your feature adoption analysis or you'll see "logging in" as your top retained-user behavior.

Not accounting for timezone. DATE_TRUNC('day', occurred_at) on a TIMESTAMPTZ column uses UTC. If your users are in New York or London, a Monday event might land in Sunday's bucket. Cast to your local timezone: DATE_TRUNC('day', occurred_at AT TIME ZONE 'America/New_York').

Key Takeaways

The five queries above cover the vast majority of product analytics questions a team will actually act on:

DAU/MAU tells you whether the product is used habitually
Funnels show you where users drop off on the path to value
Cohort retention shows whether you're improving month over month
Feature adoption points to what actually causes users to stick
Time-to-activation reveals friction in your onboarding

Start with your real event table, pick one query, and run it. The numbers will immediately suggest the next question. That's how SQL-driven product analytics works — not a dashboard someone built once, but a direct line to your data.

What's Your Approach?

Do you run analytics directly in SQL or pipe events to a warehouse first? Are there queries you've found indispensable that aren't on this list? Drop them in the comments — always curious what patterns other teams rely on.

If you're looking for a way to turn these queries into live dashboards without writing frontend code, tools like Draxlr let you connect your database and embed these charts directly in your app.

Customer-Facing Analytics: What Your SaaS App Is Missing (And How to Add It)

Vivek Kumar — Sat, 20 Jun 2026 05:05:24 +0000

Your app is collecting data. Lots of it. Every user action, every transaction, every event — it's all landing in your database. But when your customers log in, what do they actually see?

A table of rows. Maybe a count. If you're generous, a bar chart baked into a static page that you wrote six months ago and haven't touched since.

This is the analytics gap — and it's one of the most common ways SaaS products quietly lose customers to competitors who figured out that giving users insight into their own data is a product feature, not a nice-to-have.

In this article, we'll look at what customer-facing analytics actually means, why it's hard to get right, and what the SQL layer underneath it needs to look like. (If you're looking for a tool built specifically for this problem, Draxlr is worth a look.)

What "Customer-Facing Analytics" Actually Means

Customer-facing analytics (sometimes called user-facing or embedded analytics) is any reporting, chart, or data summary you expose to your end users — not your internal team, but your customers.

Examples:

A project management tool showing a team their task completion rate by week
An e-commerce platform giving merchants a revenue breakdown by product category
A fintech app letting users track their spending trends over time

The key insight: your customers are making decisions based on the data that lives in your database. If you're not surfacing that data in a useful way, they're either exporting CSVs and building spreadsheets — or switching to a product that does it for them.

The SQL Foundation: Designing for Analytics Queries

The difference between a database designed for writes and one that performs well for analytics reads is significant. Most SaaS apps optimize for OLTP (transactional workloads), which means fast inserts and row lookups. Analytics queries are the opposite: they scan large ranges, aggregate across many rows, and join multiple tables.

Here's a typical transactional query:

SELECT * FROM orders WHERE id = 12345;

Here's what an analytics query looks like:

SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS total_orders,
  SUM(amount_cents) / 100.0 AS revenue,
  AVG(amount_cents) / 100.0 AS avg_order_value
FROM orders
WHERE
  tenant_id = 'acme-corp'
  AND created_at >= NOW() - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

This query will scan potentially millions of rows. Without the right indexes, it'll be slow — and when hundreds of customers run queries like this simultaneously, your database will feel it.

What helps:

Index on (tenant_id, created_at) for time-range queries scoped to a tenant
Partial indexes for common filters (e.g., WHERE status = 'completed')
Materialized views for expensive aggregations you compute once and refresh periodically

-- Materialized view for monthly revenue per tenant
CREATE MATERIALIZED VIEW monthly_revenue AS
SELECT
  tenant_id,
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS order_count,
  SUM(amount_cents) / 100.0 AS revenue
FROM orders
GROUP BY 1, 2;

-- Refresh nightly or on demand
REFRESH MATERIALIZED VIEW monthly_revenue;

Now your dashboard query becomes a fast lookup against a pre-aggregated table instead of a full scan.

The Multi-Tenancy Problem You Can't Ignore

Every customer-facing analytics feature lives under a hard constraint: Customer A must never see Customer B's data.

This sounds obvious, but it's surprisingly easy to get wrong when you're under pressure to ship. A missing WHERE tenant_id = ? clause in one query, and you have a data leak.

The safest pattern is to enforce tenant scoping at the query level, not the application level. In PostgreSQL, Row Level Security (RLS) is built for exactly this:

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

-- Policy: users only see rows for their own tenant
CREATE POLICY tenant_isolation ON orders
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

Then, when your app opens a database connection for a given user session, it sets the tenant context:

SET app.current_tenant_id = 'acme-corp-uuid-here';

Every query after that automatically filters to that tenant's rows, no matter what SQL gets executed. It's a database-level safety net that protects you even if application code has a bug.

Common Analytics Queries Your Customers Actually Want

Here are the patterns that show up in almost every SaaS product's analytics layer.

Usage over time:

SELECT
  DATE_TRUNC('week', created_at) AS week,
  COUNT(*) AS events
FROM user_events
WHERE tenant_id = $1
  AND event_type = 'page_view'
  AND created_at >= NOW() - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

Top items by a metric:

SELECT
  p.name AS product_name,
  SUM(oi.quantity) AS units_sold,
  SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
WHERE oi.tenant_id = $1
  AND oi.created_at >= $2
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 10;

Cohort retention (month 0 vs. month 1 still active):

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(created_at)) AS cohort_month
  FROM subscriptions
  WHERE tenant_id = $1
  GROUP BY user_id
)
SELECT
  c.cohort_month,
  COUNT(DISTINCT c.user_id) AS cohort_size,
  COUNT(DISTINCT s.user_id) AS retained_month_1
FROM cohorts c
LEFT JOIN subscriptions s
  ON s.user_id = c.user_id
  AND DATE_TRUNC('month', s.created_at) = c.cohort_month + INTERVAL '1 month'
GROUP BY 1
ORDER BY 1;

These are the queries that turn raw transactional data into insight. The SQL is rarely the hard part — the hard part is building the infrastructure around it.

Common Mistakes (And How to Avoid Them)

1. Running analytics queries on your production OLTP database

Your primary database is handling writes, your API reads, and now heavy analytic scans too. Under load, these long-running queries block connection slots and degrade the entire app. Use a read replica for analytics traffic, or move to a separate analytics database for larger datasets.

2. Skipping query caching entirely

Most analytics queries are expensive and most users look at the same date ranges. Cache the results (Redis, Postgres materialized views, or your analytics tool's built-in cache) and refresh on a schedule. A customer viewing "last 30 days revenue" at 9am and again at 10am doesn't need two full scans.

3. Giving customers raw table data instead of aggregated insight

Customers don't want to read a 50,000-row table. They want answers. Build queries that aggregate, summarize, and rank — then build charts on top of those summaries.

4. Not indexing on tenant_id

If your tables have no index on tenant_id, every query scans the full table before filtering. A composite index like (tenant_id, created_at DESC) covers most analytics query patterns efficiently.

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

5. Building custom reports for every request

Teams that skip building self-service analytics end up fielding a constant stream of "can you add a chart for X?" requests. Embedding a configurable dashboard — even a simple one — cuts this dramatically.

The Build vs. Buy Decision

At some point every engineering team faces this question: do we build our own analytics UI, or embed an existing tool?

Building in-house gives you full control over the look and feel, but it's expensive. Charts, filters, date pickers, export functionality, and access controls — each one is a non-trivial feature. Most teams underestimate the ongoing maintenance cost.

The alternative is an embedded analytics tool that sits on top of your existing SQL database, renders charts in an iframe or web component, and handles multi-tenancy and auth for you. You write SQL, the tool handles the UI.

The right choice depends on how central analytics is to your product. If "analytics" is a secondary feature that customers occasionally check, embedded tools are almost always the faster path. If analytics is your core product differentiation, building custom makes sense.

Key Takeaways

Your customers want insight into their own data — if you're not providing it, you're leaving retention on the table
Design your SQL queries and indexes for analytics workloads, not just transactional ones
Enforce tenant isolation at the database level (Row Level Security) to prevent data leaks by design
Use materialized views and read replicas to keep analytics fast without hammering your production database
Cache aggressively — most analytics queries return the same results for many users within a short window
Don't wait until you have a perfect analytics infrastructure; a few well-designed SQL queries exposed through a simple dashboard will get you further than you think

What does your analytics setup look like? Are you building custom charts, embedding a tool, or still at the "CSV export" stage? Drop a comment below — I'm curious how teams at different stages are tackling this.

To people doing their own thing

Vivek Kumar — Fri, 19 Jun 2026 18:27:49 +0000

Whether you are building SaaS, running an agency, some shop, and struggling with your hard work - you have my highest respect.

The ups and downs of working on your own is a killer man, it feels great on some days and just crazy on others. It's not for every one - if someone told me about this side of building startups 12 years ago (when I started my agency), I would have given it a bit more thought tbh.

Keep building and shipping, and hopefully all your work will be rewarded in a way that feels rewarding to you.

How to Embed a SQL Dashboard into Your SaaS App (Without Building Everything from Scratch)

Vivek Kumar — Fri, 19 Jun 2026 05:26:34 +0000

Your SaaS app manages customer data. A lot of it. Orders, events, usage logs, revenue transactions — all living in your database. But when customers want to see that data, what do you offer them? A CSV export? A table with 15 columns and no way to filter?

If you're nodding uncomfortably, you're not alone. Analytics is often the last thing SaaS teams build — and the first thing customers ask for. The good news is that embedding a SQL-backed dashboard into your app is more straightforward than it sounds. Tools like Draxlr can shortcut a lot of the work. But even if you're rolling your own, you don't need a dedicated BI team. You just need the right architecture.

This guide walks through exactly how to do it: from the database queries that power your charts to the authentication layer that keeps each customer's data isolated.

What "Embedded Dashboard" Actually Means

An embedded dashboard renders analytics inside your application — same URL, same nav, same branding. Your users never know they're looking at a separate system.

Contrast this with:

Exporting to CSV — a workaround, not a feature
Linking to Metabase/Looker — visible context switch, authentication headaches
Building charts in your frontend — viable, but you're now maintaining a BI system

Embedded analytics sits between "roll it all yourself" and "send them to a separate tool." The goal is data that feels native.

Step 1: Know What You're Querying

Before you touch a frontend component, get your SQL right. Let's use a SaaS app with these tables:

-- Core tables
users (id, tenant_id, email, created_at, plan)
events (id, tenant_id, user_id, event_name, occurred_at)
subscriptions (id, tenant_id, status, mrr, started_at, cancelled_at)
invoices (id, tenant_id, amount, status, created_at)

The most common dashboard queries for customer-facing analytics:

Monthly Active Users

SELECT
  DATE_TRUNC('month', occurred_at) AS month,
  COUNT(DISTINCT user_id) AS mau
FROM events
WHERE tenant_id = $1
  AND occurred_at >= NOW() - INTERVAL '6 months'
GROUP BY 1
ORDER BY 1;

Revenue Over Time

SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(amount) AS total_revenue
FROM invoices
WHERE tenant_id = $1
  AND status = 'paid'
GROUP BY 1
ORDER BY 1;

Feature Adoption Breakdown

SELECT
  event_name,
  COUNT(*) AS total_events,
  COUNT(DISTINCT user_id) AS unique_users
FROM events
WHERE tenant_id = $1
  AND occurred_at >= NOW() - INTERVAL '30 days'
GROUP BY 1
ORDER BY 2 DESC
LIMIT 10;

Notice WHERE tenant_id = $1 everywhere. That parameter is non-negotiable — it's your data isolation. We'll come back to this.

Step 2: Build Your Data API Layer

Your frontend shouldn't query the database directly. You need an API layer that:

Authenticates the request
Injects the correct tenant_id
Runs the query
Returns the result as JSON

Here's a simple Node.js/Express example:

// GET /api/analytics/mau
app.get('/api/analytics/mau', requireAuth, async (req, res) => {
  const tenantId = req.user.tenantId; // from your auth middleware

  const result = await db.query(`
    SELECT
      DATE_TRUNC('month', occurred_at) AS month,
      COUNT(DISTINCT user_id) AS mau
    FROM events
    WHERE tenant_id = $1
      AND occurred_at >= NOW() - INTERVAL '6 months'
    GROUP BY 1
    ORDER BY 1
  `, [tenantId]);

  res.json(result.rows);
});

The critical thing here: tenantId comes from the authenticated session, not the request body. Never trust the client to tell you which tenant's data to return.

Step 3: Render Charts in Your Frontend

With a clean data API, your frontend just needs to call endpoints and render charts. Chart.js and Recharts are popular choices that don't require a separate BI platform.

Using Recharts in React:

import { LineChart, Line, XAxis, YAxis, Tooltip } from 'recharts';
import { useEffect, useState } from 'react';

export function MAUChart() {
  const [data, setData] = useState([]);

  useEffect(() => {
    fetch('/api/analytics/mau')
      .then(r => r.json())
      .then(rows => setData(rows.map(r => ({
        month: new Date(r.month).toLocaleDateString('en-US', { month: 'short', year: '2-digit' }),
        mau: r.mau
      }))));
  }, []);

  return (
    <LineChart width={600} height={300} data={data}>
      <XAxis dataKey="month" />
      <YAxis />
      <Tooltip />
      <Line type="monotone" dataKey="mau" stroke="#6366f1" strokeWidth={2} />
    </LineChart>
  );
}

This approach gives you full control over styling, but you're responsible for every chart you build.

Step 4: Handle Multi-Tenancy Correctly

If your app serves multiple customers, each customer must see only their data. There are two ways to enforce this — and one of them is wrong.

Wrong approach: filter in the application layer only

// ❌ Don't rely solely on this
const data = await db.query('SELECT * FROM events WHERE tenant_id = $1', [tenantId]);

If a bug slips through — a missing middleware, a copied endpoint that forgets to inject tenantId — a customer could see another customer's data. This is a catastrophic breach.

Right approach: enforce at the database layer with Row-Level Security (PostgreSQL)

-- Create a policy so the DB itself enforces isolation
ALTER TABLE events ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON events
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

Then in your connection setup:

await db.query(`SET app.current_tenant_id = '${tenantId}'`);

Now even a buggy query that forgets the WHERE tenant_id filter will return empty results instead of leaking data. Defense in depth.

Step 5: Authentication for Embedded Views

If you're embedding a dashboard in an iframe (using a third-party tool like Metabase, Draxlr, or similar), you'll need signed embed tokens. The flow looks like this:

1. User logs into your app
2. Your backend generates a signed JWT containing tenant_id and permitted dashboard IDs
3. Frontend receives the signed URL and renders the iframe
4. The embedded platform validates the JWT and scopes all queries to that tenant

A simple signed URL generation in Node.js:

const jwt = require('jsonwebtoken');

function generateEmbedToken(tenantId, dashboardId) {
  return jwt.sign(
    {
      tenant_id: tenantId,
      resource: { dashboard: dashboardId },
      exp: Math.round(Date.now() / 1000) + (60 * 60) // 1 hour
    },
    process.env.EMBED_SECRET
  );
}

// In your route handler:
app.get('/dashboard-embed-url', requireAuth, (req, res) => {
  const token = generateEmbedToken(req.user.tenantId, req.query.dashboardId);
  res.json({ url: `https://analytics.yourplatform.com/embed/dashboard?token=${token}` });
});

Never expose the embed secret on the client side. Tokens should be short-lived and generated server-side.

Build vs. Buy: When Each Makes Sense

This is the question every SaaS team eventually faces. Here's a practical breakdown:

Scenario	Build	Buy
2–3 fixed dashboard views	✅ Chart.js / Recharts	Overkill
Customers want custom reports	Very expensive	✅ Embedded BI tool
Need white-labeling	✅ Full control	✅ Most tools support it
10+ metrics, drill-downs	Months of eng time	✅ Ship faster
Tight performance requirements	✅ Optimize your own queries	Depends on platform

The rule of thumb: if you can enumerate all the charts your customers will ever want, build them yourself. If customers will want to explore their data in ways you can't predict, buy an embedded analytics tool.

Common Mistakes (And How to Avoid Them)

1. Not caching query results

Analytics queries are often expensive. Running a 6-month revenue aggregation on every page load will bring your database to its knees under load. Cache results for 5–15 minutes — your customers won't notice, and your database will thank you.

// Simple cache with node-cache
const cache = new NodeCache({ stdTTL: 300 }); // 5 minutes

app.get('/api/analytics/revenue', requireAuth, async (req, res) => {
  const cacheKey = `revenue:${req.user.tenantId}`;
  const cached = cache.get(cacheKey);
  if (cached) return res.json(cached);

  const result = await db.query(revenueQuery, [req.user.tenantId]);
  cache.set(cacheKey, result.rows);
  res.json(result.rows);
});

2. Overloading your production database

Analytics queries are read-heavy and slow. Running them against your primary write database adds latency for your real-time operations. Ideally: use a read replica for all analytics queries. At minimum: schedule expensive aggregations to run during off-peak hours and store the results.

3. Forgetting empty states

New customers have no data. Your charts will render as empty white boxes and look broken. Always handle the empty case:

if (data.length === 0) {
  return <EmptyState message="No events recorded yet. Data will appear here once your users start engaging." />;
}

4. No date filtering

A chart showing all-time data is rarely what customers want. Add a date range picker from day one. Retrofitting it later is painful.

Key Takeaways

Scope all analytics queries to tenant_id — both in your application layer and at the database level with row-level security policies
Build a clean data API layer between your database and frontend; never expose database credentials or queries to the client
Cache expensive aggregation queries — 5 to 15 minutes of staleness is acceptable for most dashboard metrics
Use read replicas for analytics to protect your primary database's write performance
Build your own charts when you have a fixed, small set of metrics; use an embedded analytics tool when customers need self-service exploration

Analytics is increasingly table-stakes for SaaS. Customers expect to see their data in your product — not in a spreadsheet they export on Fridays.

What's your current approach to embedded dashboards? Are you building with chart libraries, using an embedded BI tool, or still on the "CSV export for now" plan? Share your setup in the comments — especially if you've found a clever solution to multi-tenant data isolation.

Embedding Live Charts in Your App Without a Full BI Tool

Vivek Kumar — Thu, 18 Jun 2026 04:43:55 +0000

Your users want charts. They want to see their revenue over time, their top customers by order value, their support ticket trends — all inside your app, not exported to a spreadsheet.

So you open up the docs for some enterprise BI platform and realize: this is overkill. You don't need a full data warehouse, a semantic layer, a drag-and-drop report builder, and a six-figure annual contract. You just want to run a SQL query and show the result as a line chart.

The good news: you absolutely can, and it's less work than you think. If you want a ready-made path, tools like Draxlr handle this out of the box. But this article walks through the practical patterns for embedding live SQL-backed charts in your app without buying or building a full BI stack.

Why Developers Reach for BI Tools Prematurely

The instinct makes sense. You need charts → you Google "analytics for apps" → you land on Tableau Embedded, Looker, or PowerBI Embedded. These are great tools, but they come with real overhead:

Weeks of setup and data modeling
Per-seat or per-embed pricing that balloons as you grow
A separate data pipeline or semantic layer requirement
Complex SDKs to integrate and maintain

For many apps, the actual requirement is simpler: run a SQL query, transform the result into [{x, y}] shaped data, pass it to a charting library. That's it. No warehouse, no pipeline, no vendor lock-in.

The Core Pattern: SQL → JSON → Chart

The fundamental pattern looks like this:

User loads dashboard
  → Your API runs a SQL query against your DB
  → Returns JSON array
  → Frontend renders it with a charting library

Let's make it concrete. Suppose you have a SaaS app and want to show each customer their monthly revenue.

The Query

SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(amount_cents) / 100.0       AS revenue
FROM orders
WHERE
  account_id = $1
  AND status = 'paid'
  AND created_at >= NOW() - INTERVAL '12 months'
GROUP BY 1
ORDER BY 1;

This returns rows like:

month	revenue
2025-07-01	4820.00
2025-08-01	5210.50
2025-09-01	6340.00

The API Endpoint

// Express / Node example
app.get('/api/charts/revenue', requireAuth, async (req, res) => {
  const { rows } = await db.query(`
    SELECT
      DATE_TRUNC('month', created_at) AS month,
      SUM(amount_cents) / 100.0       AS revenue
    FROM orders
    WHERE account_id = $1
      AND status = 'paid'
      AND created_at >= NOW() - INTERVAL '12 months'
    GROUP BY 1
    ORDER BY 1
  `, [req.user.accountId]);

  res.json(rows);
});

The Frontend Chart

Using Chart.js (a lightweight option, ~60kb):

const data = await fetch('/api/charts/revenue').then(r => r.json());

new Chart(ctx, {
  type: 'line',
  data: {
    labels: data.map(r => r.month),
    datasets: [{
      label: 'Monthly Revenue',
      data: data.map(r => r.revenue),
    }]
  }
});

That's a real, live, customer-scoped chart with about 30 lines of code.

A Few Charts Worth Having by Default

Once you have the pattern down, adding charts is fast. Here are three that cover 80% of what customers ask for.

1. Monthly Active Users

SELECT
  DATE_TRUNC('month', event_time) AS month,
  COUNT(DISTINCT user_id)          AS active_users
FROM events
WHERE account_id = $1
  AND event_time >= NOW() - INTERVAL '6 months'
GROUP BY 1
ORDER BY 1;

2. Top 10 Customers by Spend

SELECT
  c.name,
  SUM(o.amount_cents) / 100.0 AS total_spend
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE o.account_id = $1
  AND o.status = 'paid'
GROUP BY c.name
ORDER BY total_spend DESC
LIMIT 10;

3. Funnel Conversion by Step

SELECT
  step_name,
  COUNT(DISTINCT user_id) AS users
FROM funnel_events
WHERE account_id = $1
  AND created_at >= NOW() - INTERVAL '30 days'
GROUP BY step_name
ORDER BY MIN(step_order);

Each of these maps cleanly to a bar chart, line chart, or horizontal bar chart with a one-line frontend binding.

The One Thing You Must Get Right: Tenant Isolation

The most dangerous mistake when embedding charts is forgetting that every query runs in a multi-tenant context. A bug that lets one customer's data leak into another customer's chart is a serious incident.

Always scope every query to the authenticated account:

-- ✅ Safe: account_id scoped in WHERE clause
SELECT DATE_TRUNC('month', created_at), SUM(amount)
FROM orders
WHERE account_id = $1  -- $1 comes from your auth session
GROUP BY 1;

-- ❌ Dangerous: no tenant scope
SELECT DATE_TRUNC('month', created_at), SUM(amount)
FROM orders
GROUP BY 1;

If you use an ORM, make sure your base query scope always injects the tenant filter. If you're writing raw SQL, enforce a code review rule: every chart query must reference account_id = $1 (or equivalent).

A deeper safety layer is PostgreSQL row-level security (RLS), which enforces tenant isolation at the database level even if a query forgets the filter — but even without RLS, disciplined query scoping is non-negotiable.

Caching Matters More Than You Think

Live charts that hit your production database on every page load can become a problem fast. For charts that aggregate over large tables (revenue over 2 years, MAU trends), even a 5-minute cache dramatically reduces load.

Simple approach with Redis or Postgres:

async function getCachedChartData(key, ttlSeconds, queryFn) {
  const cached = await redis.get(key);
  if (cached) return JSON.parse(cached);

  const data = await queryFn();
  await redis.set(key, JSON.stringify(data), 'EX', ttlSeconds);
  return data;
}

// Usage
const data = await getCachedChartData(
  `revenue:${accountId}`,
  300, // 5 minutes
  () => db.query(revenueQuery, [accountId])
);

For most customer-facing dashboards, 5–15 minute cache TTLs are invisible to users and meaningfully protect your DB under load.

Common Mistakes

1. Returning too many rows to the frontend. If your query returns 50,000 rows and you send all of them to the browser, you'll crash the chart render. Always aggregate in SQL — let the database do the grouping and summarizing, not JavaScript.

2. Using client-side GROUP BY instead of SQL. Fetching raw events and grouping in the browser is slow, wasteful on bandwidth, and exposes row-level data you probably shouldn't be sending.

3. Hardcoding date ranges. Make time windows configurable so users can toggle between 7 days, 30 days, 90 days. A single $2 parameter for the interval handles this cleanly.

4. Not handling empty data. When a new customer signs up, all chart queries return zero rows. Make sure your frontend gracefully shows an empty state rather than crashing.

5. Forgetting indexes on your date columns. A created_at index (or a partial index scoped to the most recent months) is the difference between a 12ms chart query and a 4-second one.

-- Add this if you don't have it
CREATE INDEX idx_orders_account_created
  ON orders (account_id, created_at DESC);

When to Actually Buy a Tool

The DIY approach works well until it doesn't. Consider an off-the-shelf solution when:

You need users to build their own custom reports (not just view pre-built ones)
You're aggregating across multiple databases or data sources
Your compliance requirements demand audit logging of every data access
Your engineering team is spending more than a sprint per quarter maintaining chart code

At that point, tools like Draxlr, Holistics, or Embeddable let you wrap your SQL queries in a managed layer with embedding, auth, and caching handled for you — without the full cost and complexity of an enterprise BI platform.

Key Takeaways

You don't need a BI tool to ship useful charts — the pattern is: SQL query → API endpoint → charting library
Always scope every query to the authenticated tenant; missing this is a data leak waiting to happen
Aggregate data in SQL, not JavaScript — let the database do what it's good at
Cache aggressively for large aggregations; 5 minutes of caching is invisible to users
Add indexes on (account_id, created_at) before you go to production, not after

Have you built embedded charts the DIY way, or did you reach for a tool? Drop your approach in the comments — always curious what stacks teams are using in practice.

SQL Queries Every SaaS Founder Should Know

Vivek Kumar — Wed, 17 Jun 2026 06:08:51 +0000

You've got thousands of rows in your users, subscriptions, and events tables. Your investors want a retention curve. Your product manager wants to know which step of the onboarding funnel is leaking. Your co-founder asks "are we growing?" — and nobody can answer confidently in under five minutes.

The good news: if you have a relational database, you already have everything you need. You don't need a dedicated BI tool on day one — though if you want these queries surfaced as live dashboards, a tool like Draxlr can get you there without building the UI yourself. For now, you need six queries.

This article walks through the SQL every SaaS founder should be able to run against their own database: active users, signup trends, conversion funnels, churn, MRR, and cohort retention. Each example uses realistic table structures you likely already have or can adapt in minutes.

The Schema We'll Work With

Most of these queries assume three tables:

-- users
CREATE TABLE users (
  id          BIGSERIAL PRIMARY KEY,
  email       TEXT NOT NULL,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  plan        TEXT,          -- 'free', 'pro', 'enterprise'
  cancelled_at TIMESTAMPTZ
);

-- events (user activity)
CREATE TABLE events (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT REFERENCES users(id),
  event_name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- subscriptions
CREATE TABLE subscriptions (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT REFERENCES users(id),
  mrr        NUMERIC(10, 2) NOT NULL,
  status     TEXT NOT NULL,  -- 'active', 'cancelled'
  started_at TIMESTAMPTZ NOT NULL,
  ended_at   TIMESTAMPTZ
);

Adjust column names to match yours — the logic transfers directly.

1. Signups Over Time

The simplest question with the most signal: are more people signing up this week than last week?

SELECT
  DATE_TRUNC('week', created_at) AS week,
  COUNT(*)                        AS new_signups
FROM users
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY 1
ORDER BY 1;

Sample output:

week	new_signups
2026-03-09	42
2026-03-16	57
2026-03-23	61
2026-03-30	88

Want to break it down by plan (free vs. paid)? Add plan to the SELECT and GROUP BY:

SELECT
  DATE_TRUNC('week', created_at) AS week,
  plan,
  COUNT(*)                        AS new_signups
FROM users
WHERE created_at >= NOW() - INTERVAL '90 days'
GROUP BY 1, 2
ORDER BY 1, 2;

2. Daily / Weekly / Monthly Active Users

Active users (DAU/WAU/MAU) tell you whether people are actually using your product after they sign up.

-- Monthly Active Users (MAU) for the last 6 months
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(DISTINCT user_id)          AS mau
FROM events
WHERE created_at >= NOW() - INTERVAL '6 months'
GROUP BY 1
ORDER BY 1;

Replace 'month' with 'week' or 'day' for WAU/DAU. You can also filter to a specific event type to count only users who completed a meaningful action (e.g., event_name = 'document_created'):

SELECT
  DATE_TRUNC('week', created_at) AS week,
  COUNT(DISTINCT user_id)         AS active_users
FROM events
WHERE event_name = 'document_created'
  AND created_at >= NOW() - INTERVAL '12 weeks'
GROUP BY 1
ORDER BY 1;

The DAU/MAU ratio (stickiness) is a key product health metric. If you're above 20%, you're doing well.

3. Conversion Funnel

Where exactly are people dropping off between signup and their first paid subscription? This query calculates conversion at each step.

Suppose your activation funnel is: signed up → completed onboarding → created first project → upgraded to paid.

WITH funnel AS (
  SELECT
    u.id AS user_id,
    MAX(CASE WHEN e.event_name = 'onboarding_completed' THEN 1 ELSE 0 END) AS completed_onboarding,
    MAX(CASE WHEN e.event_name = 'project_created'      THEN 1 ELSE 0 END) AS created_project,
    MAX(CASE WHEN u.plan != 'free'                       THEN 1 ELSE 0 END) AS upgraded
  FROM users u
  LEFT JOIN events e ON e.user_id = u.id
  WHERE u.created_at >= NOW() - INTERVAL '30 days'
  GROUP BY u.id
)
SELECT
  COUNT(*)                                          AS signed_up,
  SUM(completed_onboarding)                         AS completed_onboarding,
  SUM(created_project)                              AS created_project,
  SUM(upgraded)                                     AS upgraded,
  ROUND(100.0 * SUM(completed_onboarding) / COUNT(*), 1) AS pct_onboarded,
  ROUND(100.0 * SUM(created_project) / COUNT(*), 1)      AS pct_created_project,
  ROUND(100.0 * SUM(upgraded) / COUNT(*), 1)             AS pct_upgraded
FROM funnel;

Sample output:

signed_up	completed_onboarding	created_project	upgraded	pct_onboarded	pct_created_project	pct_upgraded
420	310	198	67	73.8%	47.1%	16.0%

This immediately shows you that 47% of signups create a project but only 16% upgrade — so the conversion problem is between "created project" and "paid", not at onboarding.

4. Monthly Churn Rate

Churn rate is the percentage of paying customers who cancelled in a given month.

WITH monthly_base AS (
  SELECT
    DATE_TRUNC('month', started_at) AS cohort_month,
    COUNT(*)                         AS customers_at_start
  FROM subscriptions
  WHERE status IN ('active', 'cancelled')
  GROUP BY 1
),
monthly_churned AS (
  SELECT
    DATE_TRUNC('month', ended_at)    AS churn_month,
    COUNT(*)                          AS churned
  FROM subscriptions
  WHERE status = 'cancelled'
    AND ended_at IS NOT NULL
  GROUP BY 1
)
SELECT
  mc.churn_month,
  mc.churned,
  mb.customers_at_start,
  ROUND(100.0 * mc.churned / NULLIF(mb.customers_at_start, 0), 2) AS churn_rate_pct
FROM monthly_churned mc
JOIN monthly_base mb ON mc.churn_month = mb.cohort_month
ORDER BY mc.churn_month;

Why NULLIF? It prevents a division-by-zero error when a month has no starting customers — a common SQL gotcha when dealing with sparse data.

A cleaner alternative that avoids the join is to use a window function to look at active customers at the start of the churn month, but this version is easier to adapt quickly.

5. Monthly Recurring Revenue (MRR)

Total active MRR by month — your growth curve in one query.

SELECT
  DATE_TRUNC('month', s.started_at) AS month,
  SUM(s.mrr)                         AS total_mrr
FROM subscriptions s
WHERE s.status = 'active'
   OR (s.status = 'cancelled' AND s.ended_at > DATE_TRUNC('month', s.started_at))
GROUP BY 1
ORDER BY 1;

To see new vs. expansion vs. churned MRR (the full waterfall), you need a more involved query — but just having total MRR by month is often the first thing you need to establish a baseline.

Want to add average revenue per user (ARPU)?

SELECT
  DATE_TRUNC('month', started_at) AS month,
  SUM(mrr)                         AS total_mrr,
  COUNT(DISTINCT user_id)          AS paying_customers,
  ROUND(SUM(mrr) / NULLIF(COUNT(DISTINCT user_id), 0), 2) AS arpu
FROM subscriptions
WHERE status = 'active'
GROUP BY 1
ORDER BY 1;

6. Cohort Retention

This is the queen of SaaS analytics queries. It shows, for each signup cohort, what percentage are still active N months later.

WITH cohorts AS (
  SELECT
    user_id,
    DATE_TRUNC('month', MIN(created_at)) AS cohort_month
  FROM events
  GROUP BY user_id
),
activity AS (
  SELECT
    e.user_id,
    DATE_TRUNC('month', e.created_at) AS activity_month
  FROM events e
  GROUP BY e.user_id, DATE_TRUNC('month', e.created_at)
),
cohort_activity AS (
  SELECT
    c.cohort_month,
    EXTRACT(YEAR FROM AGE(a.activity_month, c.cohort_month)) * 12 +
    EXTRACT(MONTH FROM AGE(a.activity_month, c.cohort_month)) AS months_since_signup,
    COUNT(DISTINCT a.user_id) AS active_users
  FROM cohorts c
  JOIN activity a ON a.user_id = c.user_id
  GROUP BY c.cohort_month, months_since_signup
),
cohort_sizes AS (
  SELECT cohort_month, COUNT(DISTINCT user_id) AS cohort_size
  FROM cohorts
  GROUP BY cohort_month
)
SELECT
  ca.cohort_month,
  cs.cohort_size,
  ca.months_since_signup AS month_n,
  ca.active_users,
  ROUND(100.0 * ca.active_users / cs.cohort_size, 1) AS retention_pct
FROM cohort_activity ca
JOIN cohort_sizes cs ON cs.cohort_month = ca.cohort_month
WHERE ca.months_since_signup <= 6
ORDER BY ca.cohort_month, ca.months_since_signup;

Sample output:

cohort_month	cohort_size	month_n	active_users	retention_pct
2026-01	200	0	200	100.0%
2026-01	200	1	142	71.0%
2026-01	200	2	110	55.0%
2026-01	200	3	94	47.0%
2026-02	185	0	185	100.0%
2026-02	185	1	139	75.1%

If retention flattens above 30-40% at month 3+, you likely have product-market fit among a meaningful subset of users. If it keeps declining toward zero, that's the most important problem to fix.

Common Mistakes to Avoid

1. Counting users instead of distinct users. Always use COUNT(DISTINCT user_id) in activity queries, or a single user logging 50 events inflates your MAU number.

2. Forgetting time zones. TIMESTAMPTZ stores time zone info, but NOW() uses your session's zone. For global products, make sure you're consistent — use AT TIME ZONE 'UTC' explicitly if needed.

3. Using contract close dates for MRR. MRR should be recognized from started_at, not when someone signed the contract. Using the wrong date shifts your growth curve and overstates early revenue.

4. Defining churn differently across queries. Pick one definition — "cancelled subscription within 30 days" or "no activity for 60 days" — and stick to it. Mixing definitions across reports creates confusion about whether growth is real.

5. Ignoring NULL-safe division. Always wrap denominators in NULLIF(value, 0) when calculating percentages on dynamic data. Without it, a zero-row month will crash your query.

Key Takeaways

You don't need a BI tool to get meaningful SaaS metrics — raw SQL gets you most of the way.
Signups + MAU + funnel answer "are people using this?" and "where are they dropping off?"
Churn + MRR + cohort retention answer "is this business sustainable?" and "is the product improving over time?"
Run these queries weekly. Paste them into a shared doc. Make the numbers everyone's problem.

What's Your Go-To Query?

Do you have a metric you check every Monday morning that's not on this list? Drop it in the comments — I'd love to see what others are tracking. And if you're looking for a faster way to get these queries running without writing them from scratch, tools like Draxlr let you generate SQL from natural language and pin the results as live dashboards your whole team can see.

Building a Natural Language Query Interface for Your Database: A Developer's Blueprint

Vivek Kumar — Tue, 16 Jun 2026 04:23:07 +0000

Every product team eventually hits the same wall. Marketing wants to know which signups came from last week's campaign. Support wants a list of customers on the Pro plan who opened a ticket in the last 48 hours. The founder wants MRR by cohort, broken down by acquisition channel, by yesterday at 9am.

And every time, someone pings the nearest engineer to write yet another one-off SQL query.

The dream is obvious: let people ask questions in plain English and get answers from the database. For years, that dream lived in expensive enterprise BI tools. In 2026, with capable LLMs available behind an API call, you can build a credible natural language query interface yourself — or use something like Draxlr that ships with AI-powered SQL generation already built in. If you want to understand how it works under the hood, or roll your own, this post walks through what such a system actually looks like: the architecture, the SQL plumbing, the prompt design, and the production failure modes you'll want to plan for before your first user types a question.

What "Natural Language to SQL" actually means

At its simplest, a text-to-SQL system is a function:

question (string) -> SQL query (string) -> result (rows)

The naive implementation is one prompt to GPT-style model: "Here's my schema, here's the question, write a SQL query." That works for toy demos and falls apart the moment you point it at a real database with 80 tables, 14 of which are named some variation of users.

A production-grade interface looks more like this:

question
   |
   v
[ schema retrieval ]  <-- pull only relevant tables
   |
   v
[ prompt assembly ]   <-- schema + examples + guardrails
   |
   v
[ SQL generation ]    <-- LLM call
   |
   v
[ validation ]        <-- parse, lint, dry-run
   |
   v
[ safe execution ]    <-- read-only role, row limits, timeouts
   |
   v
result + the SQL it ran (always show this)

Each of those stages is a thing you build. Let's walk through them.

Step 1: Schema retrieval (don't dump everything)

The single biggest accuracy lever is the schema context you feed the model. Dumping your entire schema into the prompt sounds tempting and is almost always wrong: it blows your context window, costs more, and — counterintuitively — makes the model less accurate because it has to pick the right tables out of a haystack.

The fix is retrieval. Treat your schema like a knowledge base, embed it, and pull only what's relevant to the question.

Start by capturing a clean description of every table:

SELECT
  c.table_name,
  c.column_name,
  c.data_type,
  pgd.description AS column_comment
FROM information_schema.columns c
LEFT JOIN pg_catalog.pg_statio_all_tables st
  ON st.schemaname = c.table_schema AND st.relname = c.table_name
LEFT JOIN pg_catalog.pg_description pgd
  ON pgd.objoid = st.relid AND pgd.objsubid = c.ordinal_position
WHERE c.table_schema = 'public'
ORDER BY c.table_name, c.ordinal_position;

For each table, build a small text document that the LLM can actually read:

Table: subscriptions
Description: One row per customer subscription. A user can have at most one
active subscription at a time. `status` is one of active, paused, cancelled.
Columns:
  - id              (uuid, PK)
  - user_id         (uuid, FK -> users.id)
  - plan_id         (uuid, FK -> plans.id)
  - status          (text)
  - mrr_cents       (integer) -- monthly recurring revenue in cents
  - started_at      (timestamptz)
  - cancelled_at    (timestamptz, nullable)
Sample rows:
  id=a1.., user_id=u3.., plan_id=p_pro, status=active, mrr_cents=4900

Embed each of those documents with any embedding model, store the vectors, and at query time embed the user's question and pull the top 5–10 most similar tables. This is plain RAG, applied to schema instead of documents.

The payoff is dramatic. A question like "how many users upgraded to Pro last month?" will retrieve users, subscriptions, and plans — and leave the 70 other tables out of the prompt entirely.

Step 2: Prompt assembly

Once you have the relevant tables, the prompt itself follows a predictable shape:

A system message defining the role and the dialect.
The retrieved schema documents.
A few worked examples (few-shot).
Hard rules ("read-only", "always LIMIT 1000", "use ISO dates").
The user's question.

Here's a stripped-down version in pseudocode:

def build_prompt(question, tables, examples):
    return f"""You are a PostgreSQL expert. Generate a single SELECT query
to answer the user's question. Rules:
- Use only the tables and columns shown below.
- Always include LIMIT 1000.
- Use ISO date literals (e.g. '2026-05-01').
- Never write INSERT, UPDATE, DELETE, DROP, or DDL.
- If the question is ambiguous, return a JSON object
  {{"clarify": "..."}} instead of SQL.

## Schema
{format_tables(tables)}

## Examples
{format_examples(examples)}

## Question
{question}

Return only the SQL, no explanation."""

The two underrated pieces here are the examples and the clarification escape hatch.

A handful of question/SQL pairs from your domain teaches the model your conventions — that mrr_cents is in cents, that "active users" means status = 'active' AND last_seen_at > now() - interval '30 days', that you always join on tenant_id. Three good examples often beat a thousand words of instruction.

The clarification hatch is the difference between a tool that hallucinates confidently and one that admits when a question is too vague. Ambiguous natural language is the #1 source of bad SQL, and giving the model a way to ask back is far better than letting it guess.

Step 3: Validation before execution

Never run an LLM-generated query straight against your database. There are three layers worth wiring up.

Parse it. Run the SQL through a parser like sqlglot or pgsql-parser. If it doesn't parse, you have a clean signal to either retry or report the error — no need to wait for the database to reject it.

Lint it. Walk the parsed AST and reject anything that isn't a SELECT. Reject queries that reference tables outside your allowed list. Reject queries without a LIMIT. This is your defence against a creative model that decides DELETE FROM users is a reasonable answer.

from sqlglot import parse_one, exp

def is_safe(sql, allowed_tables):
    tree = parse_one(sql, read="postgres")
    if not isinstance(tree, exp.Select):
        return False, "only SELECT allowed"
    for t in tree.find_all(exp.Table):
        if t.name not in allowed_tables:
            return False, f"table {t.name} not allowed"
    if not tree.args.get("limit"):
        return False, "missing LIMIT clause"
    return True, None

Dry-run it. PostgreSQL has EXPLAIN. Run the query under EXPLAIN (not EXPLAIN ANALYZE — that executes it) to confirm the planner accepts it. If EXPLAIN returns an estimated cost above some threshold, refuse to run it or warn the user.

Step 4: Safe execution

The query has parsed, linted, and been planned. Now run it — but not as your application's regular DB user.

Create a dedicated read-only role with the minimum privileges needed:

CREATE ROLE nl_query_runner LOGIN PASSWORD '...';

REVOKE ALL ON ALL TABLES IN SCHEMA public FROM nl_query_runner;
GRANT SELECT ON users, subscriptions, plans, events TO nl_query_runner;

ALTER ROLE nl_query_runner SET statement_timeout = '10s';
ALTER ROLE nl_query_runner SET default_transaction_read_only = on;

Statement timeouts catch runaway queries. default_transaction_read_only is belt-and-braces protection in case your lint layer ever has a bug. Granting SELECT only on the specific tables you've exposed means even a perfectly-crafted injection can't touch your secrets table.

For multi-tenant apps, layer Postgres row-level security on top, so even a query like SELECT * FROM subscriptions only sees the calling tenant's rows. (I wrote about this in a previous post on row-level security for embedded dashboards.)

A realistic end-to-end example

Imagine a SaaS analytics app where a user types:

"What was our MRR from Pro customers in April?"

Here's what happens:

Retrieval pulls subscriptions, plans, and users.
Prompt assembles those tables plus three example queries.
Generation produces:

   SELECT SUM(s.mrr_cents) / 100.0 AS mrr_dollars
   FROM subscriptions s
   JOIN plans p ON p.id = s.plan_id
   WHERE p.name = 'Pro'
     AND s.status = 'active'
     AND s.started_at <= '2026-04-30'
     AND (s.cancelled_at IS NULL OR s.cancelled_at > '2026-04-30')
   LIMIT 1000;

Validation parses cleanly, only references allowed tables, has a LIMIT.
Execution runs under the read-only role and returns:

mrr_dollars
48,372.00

The UI shows the answer and the SQL that produced it. Always show the SQL. Users learn to trust the system faster when they can see its work, and your power users will start tweaking the SQL directly.

Common mistakes and gotchas

A few traps you'll hit if you don't plan for them:

Trusting the model on dates. LLMs are weirdly bad at "last week" vs. "the last 7 days" vs. "the previous calendar week". Resolve relative time expressions in your code before generating SQL, and inject explicit dates into the prompt.

Ambiguous column names. If you have users.created_at and subscriptions.created_at, a question like "how many created this month?" is genuinely ambiguous. Detect this and fall back to the clarification hatch instead of guessing.

Pre-aggregated columns. If you have a daily_metrics rollup table, the model may not know whether to query it or recompute from raw events. Document this explicitly in the table description: "Prefer this table for date-based aggregations; events table only for event-level analysis."

Joins that explode. A model can produce a cartesian join with a missing condition and pull back a billion rows. The EXPLAIN cost check and the statement_timeout are your safety net.

Currency, units, and time zones. mrr_cents is not mrr_dollars. started_at might be UTC; the user's "April" might mean Pacific Time. Encode these in your schema documentation and your few-shot examples, or expect surprising answers.

Showing only the answer, not the SQL. This destroys trust. The first time a user gets a number that looks off and has no way to inspect it, they'll stop using your tool. Always show the query.

Key takeaways

A practical natural-language-to-SQL interface is not a single LLM call. It's a small pipeline: retrieve the relevant schema, assemble a tight prompt with examples and rules, generate the SQL, validate it before it ever touches the database, and run it under a tightly-scoped role. The LLM is the interesting part, but the boring infrastructure around it — schema retrieval, parsing, linting, read-only roles, timeouts — is what separates a demo from a product.

Build for the failure cases from day one. Add a clarification path for ambiguous questions. Always show the generated SQL. Log every question and query so you can mine them for new few-shot examples. The systems that work in production are the ones that treat the LLM as a junior analyst whose output always gets reviewed, not as an oracle.

Over to you

Have you built a natural language interface for your own database, or are you using one in a product? What broke first when real users started typing into it? Drop your war stories — and the tools you reached for — in the comments. I'm especially curious which retrieval strategies have worked for people with very large or very messy schemas.

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.