DEV Community: Vivek Kumar

Kill the Monday Spreadsheet: Replacing Manual Reports with Live SQL Dashboards

Vivek Kumar — Fri, 24 Jul 2026 14:23:37 +0000

Every team has one. The Monday-morning report. Someone opens the database, runs a few queries, exports the results to CSV, pastes them into a spreadsheet, tidies up the formatting, drags a formula down a column, builds a chart, and emails it around. Two hours gone. And by Tuesday afternoon, the numbers are already stale.

If that ritual sounds familiar, you already know the deeper problem: the spreadsheet isn't the source of truth, it's a snapshot. It captures what the database looked like at 9:14 a.m. on Monday and then slowly drifts out of date while everyone keeps making decisions off it. Worse, the moment you have three people editing three copies named report_final, report_final_v2, and report_FINAL_actually, nobody knows which numbers are real anymore.

This post is about escaping that loop. We'll look at why the spreadsheet report is fundamentally broken as a workflow, then walk through how to replace it with a live SQL dashboard that queries your database directly and updates itself. You'll see the actual SQL that powers the metrics teams care about most, plus the gotchas that bite people when they make the switch.

Why the spreadsheet report keeps failing you

The spreadsheet isn't the villain. The manual pipeline around it is. Every step between your database and the chart is a place where time leaks and errors sneak in.

Think about what actually happens on a typical export-and-paste workflow:

Step	What goes wrong
Run query, export CSV	Data is frozen at export time — outdated instantly
Copy/paste into sheet	Off-by-one pastes, wrong columns, silent truncation
Apply formulas	A dragged formula misses the last 40 rows
Share the file	Five versions in five inboxes, no single truth
Repeat next week	The whole thing happens again by hand

None of these are exotic failures. A single typo, a dragged-down formula error, or a copy-paste slip can quietly derail a report — and because spreadsheets don't track who changed what or when, you often don't catch it until an executive makes a decision on the wrong number. When two people edit at once, only the last save survives, and the other person's work just vanishes.

The core issue is that a spreadsheet report answers "what was true at export time?" when the business is asking "what is true right now?" A live SQL dashboard flips that around. Instead of pushing a snapshot out to a file, you point a dashboard at the database and let it pull fresh data every time someone looks at it.

What "live" actually means

A live SQL dashboard is just a saved query (or a set of them) plus a visualization that re-runs on a schedule or on page load. There's no export, no paste, no formula-dragging. The query is the report. When the underlying orders table gets a new row, the next refresh reflects it automatically.

That shift has three immediate payoffs: reports are always current, there's exactly one version everyone sees, and the two hours you spent assembling the sheet drop to zero after the first setup.

Let's make it concrete with the metrics teams rebuild in spreadsheets every single week.

The queries that replace your weekly sheet

1. Revenue this month vs. last month

The classic top-of-report number. In a spreadsheet you'd export two date ranges and compute the delta by hand. In SQL it's one query that's always accurate:

SELECT
  date_trunc('month', created_at) AS month,
  SUM(amount) AS revenue,
  COUNT(*)    AS order_count
FROM orders
WHERE status = 'paid'
  AND created_at >= date_trunc('month', CURRENT_DATE) - INTERVAL '1 month'
GROUP BY 1
ORDER BY 1;

Output:

month	revenue	order_count
2026-06-01	184200.00	1042
2026-07-01	96350.00	548

Put that on a dashboard and the month-over-month comparison recalculates itself as July fills in. No re-export.

2. New signups per day (the trend line)

Founders love a signup chart, and it's the most-copy-pasted metric there is. Here's the whole thing:

SELECT
  created_at::date AS signup_day,
  COUNT(*)         AS new_users
FROM users
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;

Feed that into a line chart widget and you've replaced the manual "export users, pivot by day, insert chart" dance permanently.

3. Active subscriptions and MRR

Recurring revenue is where spreadsheet math gets dangerous, because a missed filter silently inflates the number. Keep it in SQL where the logic lives in one place:

SELECT
  COUNT(*)                    AS active_subscriptions,
  SUM(monthly_price)          AS mrr,
  ROUND(AVG(monthly_price),2) AS avg_price
FROM subscriptions
WHERE status = 'active'
  AND canceled_at IS NULL;

That canceled_at IS NULL guard is exactly the kind of condition people forget when they eyeball a spreadsheet. Encoded in the query, it's applied every time, forever.

4. Top products by revenue

A join that would be a genuine pain to reproduce with spreadsheet lookups:

SELECT
  p.name                 AS product,
  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
JOIN orders   o ON o.id = oi.order_id
WHERE o.status = 'paid'
  AND o.created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY p.name
ORDER BY revenue DESC
LIMIT 10;

VLOOKUP across three sheets to get this? No thanks. One query, ranked and limited, refreshed automatically.

5. Week-over-week growth with a window function

This is the one that really shows off the ceiling. Try building a self-updating growth percentage in a spreadsheet and you'll be fighting $ references all afternoon:

WITH weekly AS (
  SELECT
    date_trunc('week', created_at) AS week,
    SUM(amount)                    AS revenue
  FROM orders
  WHERE status = 'paid'
  GROUP BY 1
)
SELECT
  week,
  revenue,
  LAG(revenue) OVER (ORDER BY week) AS prev_week,
  ROUND(
    100.0 * (revenue - LAG(revenue) OVER (ORDER BY week))
    / NULLIF(LAG(revenue) OVER (ORDER BY week), 0), 1
  ) AS growth_pct
FROM weekly
ORDER BY week DESC
LIMIT 8;

The LAG() window function pulls the previous week's revenue onto the same row so you can compute growth without a self-join. The NULLIF(..., 0) prevents a divide-by-zero on your first week. Once, in a saved query — not every week, by hand.

Common mistakes when you make the switch

Moving off spreadsheets is mostly a win, but a few things trip people up.

Refreshing a heavy query too aggressively. A dashboard that re-runs a five-table join every 10 seconds will hammer your production database. Match the refresh interval to how fast the data actually changes — signups per day don't need second-by-second updates. For genuinely expensive queries, refresh a materialized view on a schedule and point the dashboard at that.

Running analytics against your primary database. Big aggregate queries can compete with live app traffic. If your dashboards get heavy, point them at a read replica so reporting load never touches the database serving your users.

Forgetting timezones. created_at is probably stored in UTC. If your team is in New York, created_at::date will split days at 8 p.m. local time and your "daily" numbers will look subtly wrong. Convert explicitly: (created_at AT TIME ZONE 'America/New_York')::date.

Letting NULLs skew aggregates. AVG() and SUM() skip NULLs, and COUNT(column) ignores them too — which is sometimes what you want and sometimes a silent bug. Be deliberate about whether a missing value should count as zero.

Rebuilding logic in five places. The whole point is one source of truth. If three dashboards each define "active customer" slightly differently, you've recreated the spreadsheet chaos in SQL. Push shared definitions into a database view so every dashboard reads the same logic.

Key takeaways

The manual spreadsheet report fails for structural reasons: it's a snapshot pretending to be live, it multiplies into untrustworthy versions, and every hand-off is a chance to introduce an error nobody can trace. A live SQL dashboard fixes all three by making the query itself the report — always current, always singular, and set up once instead of rebuilt weekly.

Start small. Pick the one report your team rebuilds most often, write the SQL that produces it, and put it on a dashboard that refreshes on its own. When Monday comes and the numbers are already there and already right, you won't miss the copy-paste.

If you'd rather not wire up refresh schedules and charting yourself, tools like Draxlr let you connect your database, save queries, and share live dashboards without building a BI stack from scratch — but the SQL above is the real engine either way.

What's the one spreadsheet report your team would kill to never build again? Drop it in the comments with the metric you're tracking — I'll help you sketch the query.

Sources: Atlassian — How to Create Real-Time SQL Dashboards, Oracle — 10 Common Spreadsheet Risks, Explo — How to Create Real-Time SQL Dashboards

When to Trust AI-Generated SQL (And When Not To)

Vivek Kumar — Wed, 22 Jul 2026 07:02:21 +0000

You paste "show me monthly revenue by plan for the last year" into an AI assistant, and three seconds later you have a 20-line query with a CTE, a date filter, and a GROUP BY. It runs. It returns numbers. The numbers look reasonable.

So you ship it.

That last step is where things go wrong. AI is genuinely good at writing SQL now — good enough that the real skill is no longer "can it write the query" but "should I trust this query." Because AI-generated SQL doesn't fail like a compiler error that screams at you. It fails silently, returning a perfectly plausible result that happens to be wrong.

This article isn't a validation checklist you run on every query. It's a mental model for triage: which AI-generated queries are safe to trust at a glance, which need a second look, and which you should never ship without understanding line by line.

The core problem: SQL fails quietly

When AI hallucinates a Python function name, you get an error. When AI hallucinates SQL logic, you get a number. A revenue column that's silently doubled looks identical to correct revenue — until finance asks why the dashboard disagrees with the accounting system.

There was a widely-cited finding that developers shipping AI-generated code without review introduced markedly more security and correctness issues than those who reviewed it first. The lesson isn't "don't use AI." It's that the output looks finished, which makes it easy to skip the review that the code still needs.

So instead of trusting or distrusting AI wholesale, sort queries by how catastrophically they can fail quietly.

The trust spectrum

Here's the framework I use. Every AI-generated query falls into one of three zones based on structure, not vibes.

Zone	Query shape	How to treat it
Green — trust	Single table, simple filters and aggregates	Skim and ship
Yellow — verify	Joins, subqueries, date logic, window functions	Read carefully, spot-check output
Red — scrutinize	Multi-table joins with aggregates, anything that writes data, anything touching money or auth	Understand every line before running

Let's walk through each.

Green zone: trust it

Single-table queries with straightforward filtering and aggregation are the sweet spot for AI. These patterns are everywhere in training data, and there's almost nowhere for the logic to hide a mistake.

-- "How many users signed up last month?"
SELECT COUNT(*)
FROM users
WHERE created_at >= '2026-06-01'
  AND created_at <  '2026-07-01';

-- "Total revenue by plan"
SELECT plan, SUM(amount) AS total_revenue
FROM subscriptions
GROUP BY plan
ORDER BY total_revenue DESC;

The only thing that can really go wrong here is the AI picking the wrong column (created_at vs signed_up_at) or a fuzzy date boundary. A glance at the column names and the date range is enough. Ship it.

Yellow zone: verify before you trust

The moment a query adds a join, a subquery, or window logic, the surface area for silent error grows. The query will still run. The result will still look fine. But now you need to actually read it.

Take date-bucketing, a place AI often gets almost right:

-- "Weekly active users for Q2"
SELECT date_trunc('week', event_time) AS week,
       COUNT(DISTINCT user_id) AS wau
FROM events
WHERE event_time >= '2026-04-01'
  AND event_time <  '2026-07-01'
GROUP BY 1
ORDER BY 1;

This is correct — but notice the DISTINCT. Ask for "weekly active users" and some models will hand you COUNT(*), which counts events, not users. Both return a tidy number per week. Only one answers the question you asked. The tell isn't in whether it runs; it's in whether the aggregate matches the metric's definition.

For yellow-zone queries, the fastest verification is a spot check against a number you already trust. If your admin panel says 1,240 signups last week and the query says 1,240, the shape is probably right.

Red zone: never trust blindly

Multi-table joins that feed an aggregate are the single most dangerous thing AI writes, because of fan-out. When you join a table to another that has a one-to-many relationship, rows multiply — and any SUM or COUNT on top of that join silently inflates.

-- "Total revenue by customer"  -- looks fine, is wrong
SELECT c.name, SUM(o.amount) AS revenue
FROM customers c
JOIN orders       o  ON o.customer_id = c.id
JOIN order_items  oi ON oi.order_id   = o.id
GROUP BY c.name;

Spot the bug: joining order_items multiplies each order row by its number of line items, so SUM(o.amount) counts each order's amount once per item. An order of $100 with 3 items now contributes $300. The total looks like healthy revenue growth. It's an artifact of the join.

The fix is to aggregate at the right grain — don't join a table you're not aggregating from:

SELECT c.name, SUM(o.amount) AS revenue
FROM customers c
JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

The detection trick that catches almost every fan-out: check row counts before aggregating. If you have 50,000 orders and the pre-aggregation join returns 200,000 rows, something is multiplying your data.

-- run this first, before trusting any aggregate over a join
SELECT COUNT(*)
FROM customers c
JOIN orders       o  ON o.customer_id = c.id
JOIN order_items  oi ON oi.order_id   = o.id;
-- 200k rows for 50k orders? your SUM is inflated 4x.

Also firmly in the red zone: anything with UPDATE, DELETE, or INSERT, and anything gating access by tenant or user. An AI-written DELETE FROM sessions WHERE ... with a subtly wrong WHERE clause doesn't return a wrong number — it removes the wrong rows, permanently. Never run AI-generated write statements without reading them, and wrap them in a transaction so you can ROLLBACK.

Common gotchas that cut across zones

A few silent-failure patterns show up regardless of query complexity, worth keeping in your head:

INNER JOIN where you needed LEFT JOIN. Ask for "all users and their order counts" and an inner join quietly drops every user with zero orders — exactly the users you might care about. Your "total users" shrinks and nobody notices.

Schema hallucination. The model invents a plausible column (users.last_login) that doesn't exist, or pulls the right column from the wrong table. Syntax errors here are the good case — you catch them. The bad case is a real column that means something different than you assumed.

NULLs in aggregates and filters. WHERE status != 'cancelled' silently excludes rows where status IS NULL, because NULL != 'cancelled' is not true. AI rarely accounts for this unless prompted.

Timezone and boundary drift. >= '2026-06-01' behaves differently depending on whether the column is a date, a naive timestamp, or a timestamptz. AI can't see your session timezone, so it guesses.

How to make AI-generated SQL more trustworthy

You can move queries toward the green zone by how you prompt and where you run them:

Give the AI your real schema. Column names, types, and relationships eliminate most hallucination and grain mistakes.
State the grain explicitly. "Revenue per customer, one row per customer" is much harder to get wrong than "revenue by customer."
Ask it to explain its joins. If the model can't justify why a join won't fan out, that's your signal to scrutinize.
Run reads against a replica or a read-only role. Trust is cheaper when a wrong query can't cost you anything.

Key takeaways

AI writes SQL fast, but speed is not correctness. Sort every query by blast radius: single-table aggregates are safe to skim; joins, subqueries, and window functions deserve a careful read and a spot check; multi-table aggregates, writes, and anything touching money or access control demand full understanding before you run them. The killer failure mode is fan-out inflating aggregates — check pre-aggregation row counts and you'll catch most of it. Treat AI as a fast first draft of the query, never the final answer, and keep your own understanding of the data model as the thing you actually trust.

Your turn

What's the worst silently-wrong query AI has handed you — a doubled revenue number, a dropped cohort, a DELETE that went too far? Drop it in the comments. And if you're building dashboards or reporting on top of your database, tools like Draxlr let you generate SQL with AI and keep a human in the loop with visual query building and row counts you can sanity-check before anything ships.

Text-to-SQL Explained: How AI Turns Plain English Into SQL Queries

Vivek Kumar — Tue, 21 Jul 2026 14:36:09 +0000

Text-to-SQL Explained: How AI Turns Plain English Into SQL Queries

You've probably seen the demo by now. Someone types "show me the top 5 customers by revenue last month" into a chat box, and a fully-formed SQL query appears, runs, and returns a table. It feels like magic — or like the death of the WHERE clause you spent years mastering.

It's neither. Text-to-SQL is a real, useful capability, but it's also a leaky abstraction. If you're building a product that lets users ask questions of a database — or you're just tired of writing the same reporting queries — it pays to understand what actually happens between the English and the SQL. Because when it goes wrong (and it will), knowing the pipeline is the difference between a five-minute fix and a support ticket that says "the numbers are wrong" with no other detail.

This post walks through how a text-to-SQL system actually works, from the moment a question arrives to the moment a query runs, and where each stage tends to fail.

The four stages of a text-to-SQL system

Under the hood, almost every text-to-SQL system — whether it's a homegrown wrapper around an LLM or a polished analytics product — moves through the same four stages:

Schema context injection — the model is told what tables and columns exist.
Natural language parsing — the model interprets what the user is actually asking.
SQL generation — the model writes a query.
Execution and result delivery — the query runs and results come back.

The interesting part is that the LLM only owns stages two and three. Stages one and four are plumbing you control, and that plumbing is usually what determines whether the whole thing works.

Stage 1: The model doesn't know your database

Here's the thing people miss: an LLM has never seen your database. It doesn't know you have an orders table, that status can be 'refunded', or that revenue lives in subscriptions.mrr_cents and not orders.total. Out of the box, it knows SQL syntax — not your schema.

So before the model writes a single line, the system injects schema context into the prompt: table names, column names, data types, and the foreign-key relationships that connect them. A simplified version of what the model receives looks like this:

Table: orders
  id (int, primary key)
  user_id (int, foreign key -> users.id)
  amount_cents (int)
  status (text: 'paid', 'refunded', 'pending')
  created_at (timestamp)

Table: users
  id (int, primary key)
  email (text)
  plan (text: 'free', 'pro', 'enterprise')
  signup_date (date)

Given that context and the question "How much revenue did we make from pro users last month?", the model can now produce:

SELECT SUM(o.amount_cents) / 100.0 AS revenue_dollars
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE u.plan = 'pro'
  AND o.status = 'paid'
  AND o.created_at >= date_trunc('month', current_date - interval '1 month')
  AND o.created_at <  date_trunc('month', current_date);

Notice how much of that correctness depends on the schema hints. The model knew to filter status = 'paid' (and exclude refunds) and to divide by 100 only because the schema told it amounts are stored in cents and status is an enum. Strip that context out and you get a plausible-looking query that quietly double-counts refunds.

This is also why schema linking — matching words in the question to the right tables and columns — is the single biggest source of failures. Research on text-to-SQL systems consistently finds schema-linking errors driving well over half of all mistakes. When a database has hundreds of columns, dumping the entire schema into the prompt actually hurts: the irrelevant tables become noise that pulls the model toward the wrong join. The better systems retrieve only the handful of tables likely relevant to the question before generating anything.

Stage 2 & 3: From ambiguous English to precise SQL

This is where the fundamental tension lives. Human language is ambiguous and context-dependent. SQL is precise and unforgiving. "Last month" could mean the previous calendar month or the trailing 30 days. "Active users" could mean anyone who logged in, anyone with a paid plan, or anyone who wasn't soft-deleted. The model has to collapse that ambiguity into one exact interpretation — and it won't tell you which one it picked unless you make it.

Consider "Show me our best customers." There's no single correct query. The model might reach for:

SELECT u.email, SUM(o.amount_cents) / 100.0 AS lifetime_value
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE o.status = 'paid'
GROUP BY u.email
ORDER BY lifetime_value DESC
LIMIT 10;

That's a reasonable guess — "best" mapped to lifetime spend. But maybe your business defines "best" as most frequent, or most recent, or highest retention. The SQL is valid. The logic might be wrong. This is the failure mode that hurts most, because nothing errors out — you just get confidently incorrect numbers.

Two techniques meaningfully improve this stage. The first is few-shot examples: showing the model a handful of question/query pairs from your domain. Studies report execution-accuracy gains in the 9–12% range just from good examples, because they teach the model your conventions ("revenue always excludes refunds," "we filter deleted_at IS NULL"). The second is giving the model room to reason before it writes SQL — letting it decompose a complex question into steps rather than emitting a query in one shot. Non-reasoning models are notorious for producing valid SQL that's logically wrong: missing a GROUP BY column, selecting from the wrong table, or dropping a filter.

Stage 4: Running it (carefully)

Once SQL exists, it has to run. The naive approach — execute whatever the model produced — is how you end up with a DELETE in a reporting tool. Production systems wrap this stage in guardrails:

Run everything as a read-only database role so INSERT, UPDATE, and DELETE simply can't execute.
Validate before executing — parse the SQL and reject anything that isn't a SELECT, or that touches tables the user shouldn't see.
Enforce a LIMIT and a statement timeout so an accidental cross join doesn't take down your database.

The better systems also add a self-correction loop: if the query throws an error, feed the error message back to the model and let it fix its own SQL. A query that references a nonexistent column revenue gets a column does not exist error, and the model retries — often landing on amount_cents the second time.

Here's a minimal version of that loop in pseudocode:

def text_to_sql(question, schema, max_retries=2):
    prompt = build_prompt(schema, question, few_shot_examples)
    for attempt in range(max_retries + 1):
        sql = llm.generate(prompt)
        if not is_read_only(sql):        # guardrail
            raise UnsafeQueryError(sql)
        try:
            return db.execute(sql, timeout=10)
        except DatabaseError as e:
            prompt += f"\nThat query failed: {e}\nFix it."
    raise CouldNotGenerateValidSQL(question)

Common gotchas developers run into

A few patterns show up again and again once you ship one of these:

Gotcha	What happens	Mitigation
Schema overload	Dumping 300 columns into the prompt confuses the model and inflates cost	Retrieve only relevant tables per question
Silent logic errors	Valid SQL, wrong definition of "active" or "revenue"	Few-shot examples encoding your business rules
Ambiguous time ranges	"Last month" interpreted differently than the user meant	Show the generated SQL to the user before trusting results
Hallucinated columns	Model invents a plausible column name that doesn't exist	Self-correction loop on execution errors
No guardrails	A destructive or runaway query executes	Read-only role, statement timeout, forced LIMIT

The meta-lesson: data and schema quality matter more than clever prompting. Clean, well-named tables with sensible constraints produce better SQL than a table full of col1, col2, flag_3 no matter how good your prompt is. If your schema is confusing to a new engineer, it's confusing to the model too.

Key takeaways

Text-to-SQL isn't magic and it isn't a black box. It's a four-stage pipeline: inject schema context, parse the question, generate SQL, execute with guardrails. The LLM only owns the middle two stages — the schema you feed it and the safety net you wrap around execution are the parts you control, and the parts that decide whether it works.

The two biggest failure modes are schema linking (the model joins the wrong tables) and silent logic errors (valid SQL, wrong business definition). You fight the first with focused schema retrieval and the second with domain-specific examples. And you always, always run the generated SQL as a read-only role with a timeout — because the model will eventually surprise you.

Used this way, text-to-SQL doesn't replace people who know SQL. It gives non-technical teammates a safe on-ramp to the data, and gives the rest of us a faster path from question to answer. This is exactly the pattern tools like Draxlr lean on — pairing AI-generated SQL with a schema-aware layer and read-only guardrails so the query that runs is one you can actually trust.

Your turn

Have you shipped a natural-language query feature? What broke first — schema linking, ambiguous questions, or something you didn't see coming? And if you've found a prompting trick that reliably nails your business definitions, drop it in the comments — I'd love to compare notes.

Sources: AWS: Build a robust text-to-SQL solution, Oracle: Natural Language to SQL Generation, RSL-SQL: Robust Schema Linking in Text-to-SQL, SQL-of-Thought: Multi-agentic Text-to-SQL.

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

Vivek Kumar — Tue, 21 Jul 2026 14:33:29 +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.

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

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

Why Multi-Table Queries Break AI Prompts

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)

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

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

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.

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

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

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

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.

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.