10 Query SQL Wajib untuk Analytics SaaS

1. MRR (Monthly Recurring Revenue) berjalan#

SELECT
  date_trunc('month', s.start_date) AS month,
  SUM(s.monthly_amount) AS mrr
FROM subscriptions s
WHERE s.status = 'active'
GROUP BY 1
ORDER BY 1;

2. Cohort retention (12 bulan)#

WITH first_payment AS (
  SELECT user_id, date_trunc('month', MIN(paid_at)) AS cohort
  FROM payments GROUP BY 1
),
retention AS (
  SELECT
    fp.cohort,
    EXTRACT(MONTH FROM age(p.paid_at, fp.cohort))::int AS months_after,
    COUNT(DISTINCT p.user_id) AS retained
  FROM first_payment fp
  JOIN payments p ON p.user_id = fp.user_id
  GROUP BY 1, 2
)
SELECT cohort, months_after, retained
FROM retention
ORDER BY 1, 2;

3. Daily Active Users dengan window function#

SELECT
  date_trunc('day', last_seen_at) AS day,
  COUNT(DISTINCT user_id) AS dau,
  AVG(COUNT(DISTINCT user_id)) OVER (
    ORDER BY date_trunc('day', last_seen_at)
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS rolling_7d_avg
FROM events
GROUP BY 1
ORDER BY 1;

4. Top spending customers#

SELECT
  u.email,
  SUM(p.amount) AS lifetime_value,
  COUNT(p.id) AS total_transactions,
  MAX(p.paid_at) AS last_payment
FROM users u
JOIN payments p ON p.user_id = u.id
GROUP BY u.id, u.email
ORDER BY lifetime_value DESC
LIMIT 50;

5. Funnel conversion#

WITH funnel AS (
  SELECT
    user_id,
    MAX(CASE WHEN event = 'signup' THEN 1 ELSE 0 END) AS step_signup,
    MAX(CASE WHEN event = 'first_action' THEN 1 ELSE 0 END) AS step_action,
    MAX(CASE WHEN event = 'paid' THEN 1 ELSE 0 END) AS step_paid
  FROM events
  WHERE created_at >= NOW() - INTERVAL '30 days'
  GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (WHERE step_signup = 1) AS signups,
  COUNT(*) FILTER (WHERE step_action = 1) AS actions,
  COUNT(*) FILTER (WHERE step_paid = 1) AS paid,
  ROUND(100.0 * COUNT(*) FILTER (WHERE step_paid = 1) /
        NULLIF(COUNT(*) FILTER (WHERE step_signup = 1), 0), 2) AS conversion_pct
FROM funnel;

6. Churn detection#

SELECT u.id, u.email, MAX(s.last_active_at) AS last_active
FROM users u
JOIN sessions s ON s.user_id = u.id
WHERE u.subscription_status = 'active'
GROUP BY u.id, u.email
HAVING MAX(s.last_active_at) < NOW() - INTERVAL '14 days'
ORDER BY last_active ASC;

User aktif berlangganan tapi tidak buka app 14+ hari = churn risk.

7. Time bucket dengan generate_series#

SELECT
  d::date AS day,
  COALESCE(COUNT(p.id), 0) AS payments
FROM generate_series(NOW() - INTERVAL '30 days', NOW(), '1 day') d
LEFT JOIN payments p ON date_trunc('day', p.paid_at) = d::date
GROUP BY d
ORDER BY d;

Jaminan tidak ada hari yang missing.

8. Top errors per day#

SELECT
  date_trunc('day', timestamp) AS day,
  error_type,
  COUNT(*) AS occurrences
FROM error_logs
WHERE timestamp >= NOW() - INTERVAL '7 days'
GROUP BY 1, 2
ORDER BY 1 DESC, 3 DESC
LIMIT 20;

9. Concurrent users dengan tsrange#

SELECT
  generate_series(NOW() - INTERVAL '1 hour', NOW(), '1 minute'::interval) AS minute,
  (SELECT COUNT(*) FROM sessions
   WHERE tstzrange(started_at, ended_at) @> minute) AS concurrent
FROM generate_series(1, 60);

10. Slow query investigation#

SELECT
  query,
  calls,
  ROUND(total_exec_time::numeric, 2) AS total_ms,
  ROUND(mean_exec_time::numeric, 2) AS avg_ms,
  ROUND((100 * total_exec_time / SUM(total_exec_time) OVER ())::numeric, 2) AS pct_total
FROM pg_stat_statements
WHERE query NOT ILIKE '%pg_stat%'
ORDER BY total_exec_time DESC
LIMIT 20;

Aktifkan pg_stat_statements di postgresql.conf.

Tips#

1. Index untuk query analytics — biasanya ke created_at, user_id, dan composite index untuk filter umum.
2. Materialized view untuk query mahal yang dipanggil sering — refresh setiap jam atau saat data update.
3. Read replica untuk analytics agar tidak ganggu OLTP.
4. **Avoid SELECT *** di production query.

Setup SQL analytics dashboard untuk SaaS Anda.