CatchCase

Write SQL queries from plain English

Describe your tables and ask a business question in plain words, like top customers by revenue last month. Get a working SQL query, explained.

How to use it

  1. Paste your database schema Copy the CREATE TABLE statements for the relevant tables, or export the schema from your BI tool or ORM. More context gives a more accurate query.
  2. Describe the query in plain English Write exactly what you want to know, for example: users who signed up in May 2026, made at least two purchases and have not churned. Be specific about filters, groupings and the time window.
  3. Review the output The query comes with comments explaining each clause. Check that the logic matches your intent before running it on production.
  4. Test on a subset of data first Add LIMIT 100 before running a new query in full. Check the row count and spot-check a few rows, then remove the LIMIT.
  5. Ask for optimisation Once the logic is right, ask in the chat to optimise the query for performance and suggest indexes, with the reasons.

The prompt

You are a senior data engineer. I will give you a database schema and a plain-English description of what I want to retrieve. Write a clean, production-ready SQL query. Rules: - Use standard SQL (PostgreSQL dialect unless specified otherwise) - Add a comment above each major clause explaining what it does - Use CTEs (WITH clauses) for complex multi-step logic, not nested subqueries - Name aliases descriptively (not just "a", "b", "t") - Flag any assumptions you make in a note at the bottom DATABASE SCHEMA: [PASTE YOUR CREATE TABLE STATEMENTS HERE] QUERY REQUEST: [DESCRIBE WHAT YOU WANT IN PLAIN ENGLISH]

Example result

-- Revenue per paid order in the last 90 days
WITH recent_paid_orders AS (
    SELECT
        o.id          AS order_id,
        o.customer_id,
        o.created_at,
        SUM(oi.quantity * oi.unit_price) AS order_revenue
    FROM orders AS o
    JOIN order_items AS oi
        ON oi.order_id = o.id
    WHERE o.status = 'paid'
      AND o.created_at >= now() - interval '90 days'
    GROUP BY o.id, o.customer_id, o.created_at
),

-- Totals per customer
customer_totals AS (
    SELECT
        customer_id,
        SUM(order_revenue)  AS revenue,
        COUNT(*)            AS order_count,
        MAX(created_at)     AS last_order_at
    FROM recent_paid_orders
    GROUP BY customer_id
)

-- Keep NSW customers and rank by revenue
SELECT
    c.id,
    c.name,
    c.email,
    ct.revenue,
    ct.order_count,
    ct.last_order_at::date AS last_order_date
FROM customer_totals AS ct
JOIN customers AS c
    ON c.id = ct.customer_id
WHERE c.state = 'NSW'
ORDER BY ct.revenue DESC
LIMIT 10;

Assumptions

  • Revenue = quantity × unit_price summed over order items, before any discounts, shipping or GST that might live elsewhere. If you store an order total, use it instead.
  • Last 90 days means the 90 days up to now, including today, based on created_at in the database's time zone. For calendar days in Sydney time, compare created_at AT TIME ZONE 'Australia/Sydney'.
  • Revenue is summed per order first, so order_count counts orders, not items.
  • state is char(3), so 'NSW' matches exactly. Values like 'nsw' would be missed; use upper(c.state) = 'NSW' if the data isn't clean.
  • Ties at 10th place are cut arbitrarily. Use FETCH FIRST 10 ROWS WITH TIES to keep them all.

Related cases