Text-to-SQL Agents: Multi-step Database Reasoning

Generated SQL

-- Step 1: confirm the drop
SELECT
  date_trunc('day', created_at) AS day,
  COUNT(*)                       AS signups
FROM users
WHERE created_at >= NOW() - INTERVAL '14 days'
GROUP BY day
ORDER BY day;

-- Step 2 (after observing drop on day X): segment by source
SELECT
  date_trunc('day', created_at) AS day,
  signup_source,
  COUNT(*) AS signups
FROM users
WHERE created_at >= NOW() - INTERVAL '14 days'
GROUP BY day, signup_source
ORDER BY day, signups DESC;

-- Step 3 (if one source dominates the drop): drill into device / utm
SELECT
  device_type,
  COUNT(*) AS signups
FROM users
WHERE created_at >= NOW() - INTERVAL '7 days'
  AND signup_source = '<the dominant source>'
GROUP BY device_type
ORDER BY signups DESC;

Generated SQL

-- Step 1: enumerate signal tables
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

-- Step 2: for each signal table, check feature cardinality
-- (sessions, events, support_tickets, billing, feature_usage)
SELECT
  COUNT(DISTINCT user_id)        AS users_with_signal,
  AVG(events_per_user)           AS avg_signals_per_user,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY events_per_user)
                                 AS p50_signals_per_user
FROM (
  SELECT user_id, COUNT(*) AS events_per_user
  FROM events
  WHERE occurred_at >= NOW() - INTERVAL '90 days'
  GROUP BY user_id
) t;

-- Step 3: validate label availability — do we have ground-truth churn?
SELECT
  status,
  COUNT(*) AS users
FROM subscriptions
WHERE created_at <= NOW() - INTERVAL '90 days'
GROUP BY status;

Generated SQL

-- Step 1: enumerate tables matching the pattern
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name LIKE '%event%';

-- Step 2: for each event table, compute per-column null rate
SELECT
  '<table>' AS table_name,
  column_name,
  null_count,
  total_rows,
  ROUND(100.0 * null_count / NULLIF(total_rows, 0), 2) AS null_pct
FROM (
  SELECT
    'user_id' AS column_name,
    COUNT(*) FILTER (WHERE user_id IS NULL) AS null_count,
    COUNT(*)                                AS total_rows
  FROM <table>
  -- (one block per column, generated for each table)
) t;

-- Step 3: detect schema drift — compare current columns to a saved baseline
SELECT
  current_columns.table_name,
  current_columns.column_name,
  CASE WHEN baseline_columns.column_name IS NULL
       THEN 'added'
       ELSE 'unchanged'
  END AS drift_status
FROM information_schema.columns AS current_columns
LEFT JOIN baseline_columns USING (table_name, column_name);

Generated SQL

-- Step 1: starting and ending MRR
SELECT
  date_trunc('month', as_of_date)        AS month,
  SUM(monthly_revenue_cents) / 100.0     AS mrr_usd
FROM subscription_snapshots
WHERE as_of_date IN (
  date_trunc('month', NOW())              -- current
, date_trunc('month', NOW()) - INTERVAL '1 month'   -- prior
)
GROUP BY month;

-- Step 2: new MRR (subscriptions created this month)
SELECT
  SUM(monthly_revenue_cents) / 100.0 AS new_mrr_usd
FROM subscriptions
WHERE created_at >= date_trunc('month', NOW())
  AND status     = 'active';

-- Step 3: expansion / contraction (existing customers who changed plans)
SELECT
  customer_id,
  prior_plan_amount,
  current_plan_amount,
  current_plan_amount - prior_plan_amount AS delta_cents
FROM plan_changes
WHERE changed_at >= date_trunc('month', NOW())
  AND prior_plan_amount IS NOT NULL;

-- Step 4: churn (subscriptions ended)
SELECT
  SUM(monthly_revenue_cents) / 100.0 AS churned_mrr_usd
FROM subscriptions
WHERE churned_at >= date_trunc('month', NOW())
  AND churned_at <  date_trunc('month', NOW()) + INTERVAL '1 month';