SQL Queries Every SaaS Founder Should Know

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

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

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

The Schema We'll Work With

Most of these queries assume three tables:

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

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

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

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

1. Signups Over Time

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

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

Sample output:

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

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

2. Daily / Weekly / Monthly Active Users

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

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

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

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

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

3. Conversion Funnel

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

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

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

Sample output:

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

4. Monthly Churn Rate

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

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

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

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

5. Monthly Recurring Revenue (MRR)

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

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

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

Want to add average revenue per user (ARPU)?

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

6. Cohort Retention

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

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

Sample output:

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

Common Mistakes to Avoid

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

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

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

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

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

Key Takeaways

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

What's Your Go-To Query?

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