Data, Prompts & Code Workbench

SQL Query Builder & Explainer

Draft readable, production-safe SQL queries from your table schema and business question. Built for data analysts, product managers, and backend developers working in PostgreSQL, MySQL, BigQuery, or SQLite, this workbench structures queries with clean common table expressions (CTEs), explains join and aggregation choices, and flags NULL traps or indexing bottlenecks before you run them.

1. Configure Inputs

Stateful DO Session
Quick-fill sample:

2. Workbench Output & Refinements

Sample preview (#1) — click Run Agent for live output

SQL Query

WITH product_revenue AS (
  SELECT
    p.category,
    p.id AS product_id,
    p.name AS product_name,
    SUM(oi.qty * oi.unit_price) AS net_revenue
  FROM orders AS o
  INNER JOIN order_items AS oi
    ON oi.order_id = o.id
  INNER JOIN products AS p
    ON p.id = oi.product_id
  WHERE o.status NOT IN ('refunded', 'cancelled')
    AND o.created_at >= CURRENT_TIMESTAMP - INTERVAL '90 days'
  GROUP BY
    p.category,
    p.id,
    p.name
),
ranked_products AS (
  SELECT
    category,
    product_id,
    product_name,
    net_revenue,
    DENSE_RANK() OVER (
      PARTITION BY category
      ORDER BY net_revenue DESC
    ) AS category_rank
  FROM product_revenue
)
SELECT
  category,
  category_rank,
  product_id,
  product_name,
  ROUND(net_revenue, 2) AS net_revenue
FROM ranked_products
WHERE category_rank <= 3
ORDER BY category ASC, category_rank ASC;

Query Walkthrough

  • product_revenue CTE: Joins orders to line items and products, filters out refunded or cancelled orders within the rolling 90-day window, and aggregates total revenue per product.
  • ranked_products CTE: Applies DENSE_RANK() partitioned by category so tied products both appear without skipping rank numbers (use ROW_NUMBER() if you strictly want at most 3 rows per category).
  • Final SELECT: Filters to the top 3 ranks per category and sorts cleanly for reporting.

Correctness & Performance Checks

  • NULL status values: In SQL, o.status NOT IN ('refunded', 'cancelled') excludes rows where status IS NULL. Use o.status IS DISTINCT FROM 'refunded' if valid orders can have NULL status.
  • Recommended index: An index on orders (created_at, status) INCLUDE (id) and order_items (order_id, product_id) prevents full table scans on historical order tables.
You will get a formatted dialect-specific SQL query, a logical walkthrough of joins and CTEs, and correctness and indexing checks.

Multi-Turn Refinement Chips (Same Session)

3 Curated Worked Examples

Inspect real sample inputs and structured deliverables for the SQL Query Builder & Explainer. Click Try in Workbench on any example to load its parameters into the form above.

Example 1: Top 3 products per category by 90-day net revenue

What data do you want to query or debug?: Find the top 3 products by net revenue in each category over the last 90 days, excludin... Tables, key columns, and data types: orders(id, status, created_at TIMESTAMPTZ), order_items(order_id, product_id, qty INT, ... SQL dialect: PostgreSQL Tone: Clean & Analytical

SQL Query

WITH product_revenue AS (
  SELECT
    p.category,
    p.id AS product_id,
    p.name AS product_name,
    SUM(oi.qty * oi.unit_price) AS net_revenue
  FROM orders AS o
  INNER JOIN order_items AS oi
    ON oi.order_id = o.id
  INNER JOIN products AS p
    ON p.id = oi.product_id
  WHERE o.status NOT IN ('refunded', 'cancelled')
    AND o.created_at >= CURRENT_TIMESTAMP - INTERVAL '90 days'
  GROUP BY
    p.category,
    p.id,
    p.name
),
ranked_products AS (
  SELECT
    category,
    product_id,
    product_name,
    net_revenue,
    DENSE_RANK() OVER (
      PARTITION BY category
      ORDER BY net_revenue DESC
    ) AS category_rank
  FROM product_revenue
)
SELECT
  category,
  category_rank,
  product_id,
  product_name,
  ROUND(net_revenue, 2) AS net_revenue
FROM ranked_products
WHERE category_rank <= 3
ORDER BY category ASC, category_rank ASC;

Query Walkthrough

  • product_revenue CTE: Joins orders to line items and products, filters out refunded or cancelled orders within the rolling 90-day window, and aggregates total revenue per product.
  • ranked_products CTE: Applies DENSE_RANK() partitioned by category so tied products both appear without skipping rank numbers (use ROW_NUMBER() if you strictly want at most 3 rows per category).
  • Final SELECT: Filters to the top 3 ranks per category and sorts cleanly for reporting.

Correctness & Performance Checks

  • NULL status values: In SQL, o.status NOT IN ('refunded', 'cancelled') excludes rows where status IS NULL. Use o.status IS DISTINCT FROM 'refunded' if valid orders can have NULL status.
  • Recommended index: An index on orders (created_at, status) INCLUDE (id) and order_items (order_id, product_id) prevents full table scans on historical order tables.

Example 2: Monthly customer cohort retention rate in BigQuery

What data do you want to query or debug?: Calculate how many users placed their first order in each month of 2026 and what percen... Tables, key columns, and data types: dataset.orders (order_id STRING, user_id STRING, order_date DATE, total_amount FLOAT64) SQL dialect: Google BigQuery Tone: Performance-Focused

SQL Query

WITH user_months AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC(order_date, MONTH) AS active_month
  FROM `dataset.orders`
  WHERE order_date >= DATE '2026-01-01'
),
cohorts AS (
  SELECT
    user_id,
    MIN(active_month) AS cohort_month
  FROM user_months
  GROUP BY user_id
)
SELECT
  c.cohort_month,
  COUNT(DISTINCT c.user_id) AS cohort_size,
  COUNT(DISTINCT next_m.user_id) AS month_1_retained,
  ROUND(
    SAFE_DIVIDE(COUNT(DISTINCT next_m.user_id), COUNT(DISTINCT c.user_id)) * 100,
    2
  ) AS month_1_retention_pct
FROM cohorts AS c
LEFT JOIN user_months AS next_m
  ON next_m.user_id = c.user_id
  AND next_m.active_month = DATE_ADD(c.cohort_month, INTERVAL 1 MONTH)
GROUP BY c.cohort_month
ORDER BY c.cohort_month ASC;

Query Walkthrough

  • user_months CTE: Deduplicates activity to one row per user_id per calendar month using DATE_TRUNC(order_date, MONTH), keeping downstream joins compact.
  • cohorts CTE: Identifies each customer's earliest active month within the filtered window.
  • LEFT JOIN with DATE_ADD: Checks whether each cohort member appears in user_months exactly one month after their cohort_month and uses BigQuery's SAFE_DIVIDE to compute percentages safely.

Correctness & Performance Checks

  • Historical lookback boundary: Filtering user_months to order_date >= '2026-01-01' treats prior customers who first buy in 2026 as new. Remove the date filter inside user_months and filter WHERE c.cohort_month >= '2026-01-01' in the outer query to find true lifetime first orders.
  • Partition pruning: Partition dataset.orders by order_date and cluster by user_id to minimize bytes scanned.

Example 3: Detect duplicate customer emails with latest record winner

What data do you want to query or debug?: Find all email addresses that appear more than once in the customers table (ignoring up... Tables, key columns, and data types: customers(customer_id INT, email VARCHAR(255), updated_at DATETIME, is_active TINYINT) SQL dialect: MySQL 8+ Tone: Mentor & Explainer

SQL Query

WITH normalized_customers AS (
  SELECT
    customer_id,
    LOWER(TRIM(email)) AS clean_email,
    updated_at,
    is_active,
    COUNT(*) OVER (
      PARTITION BY LOWER(TRIM(email))
    ) AS duplicate_count,
    ROW_NUMBER() OVER (
      PARTITION BY LOWER(TRIM(email))
      ORDER BY is_active DESC, updated_at DESC, customer_id DESC
    ) AS recency_rank
  FROM customers
  WHERE email IS NOT NULL
    AND TRIM(email) <> ''
)
SELECT
  clean_email,
  duplicate_count,
  customer_id AS winning_customer_id,
  updated_at AS latest_updated_at
FROM normalized_customers
WHERE duplicate_count > 1
  AND recency_rank = 1
ORDER BY duplicate_count DESC, clean_email ASC;

Query Walkthrough

  • LOWER(TRIM(email)): Standardizes casing and strips accidental whitespace so "Alex@Example.com " and "alex@example.com" group together even under case-sensitive collations.
  • *COUNT() OVER (...)**: Counts total rows sharing the normalized email without collapsing individual customer attributes yet.
  • ROW_NUMBER() OVER (...): Orders duplicate profiles by active status first, then most recent updated_at, and uses customer_id DESC as a deterministic tie-breaker so recency_rank = 1 picks one canonical winner.

Correctness & Performance Checks

  • Functional index: Standard indexes on email are bypassed by LOWER(TRIM(email)). In MySQL 8.0.13+, add a functional index CREATE INDEX idx_clean_email ON customers ((LOWER(TRIM(email)))) if this table has hundreds of thousands of rows.
  • Merge safety: Always verify foreign key references in child tables (such as orders.customer_id) before deactivating or merging the losing recency_rank > 1 rows.

Practical Workflow Tips

How to get sharper, more reliable results from this workbench and verify output before publishing or shipping.

  • Include primary keys and relationship cardinality (for example, one order has many order_items) in the schema box so the agent avoids accidental fan-out duplicates when summing.
  • Always specify your exact SQL dialect because date math syntax differs sharply across PostgreSQL, MySQL, BigQuery, and SQLite.
  • If you are debugging a slow query, paste the current SQL into the question box along with row count estimates for your largest tables.
  • Test window function queries on a small slice (such as a single customer_id or a 7-day window) to verify tie-breaking behavior between ROW_NUMBER, RANK, and DENSE_RANK.

Frequently Asked Questions

Why did my SUM() total double after adding a LEFT JOIN?

When you join a parent table (like orders) to a child table with multiple matching rows (like shipments or order_items), each parent row repeats once per child row. Aggregate the child table inside its own CTE first so it has one row per order_id, then join that CTE to the parent table.

What is the difference between WHERE and HAVING in SQL?

WHERE filters individual table rows before any GROUP BY aggregation or window function runs. HAVING filters the grouped summary rows after GROUP BY has already computed aggregates like SUM() or COUNT(). Pushing raw column filters into WHERE reduces the number of rows the database engine has to group.

Is it safe to paste my schema into this SQL query builder?

Yes, pasting table and column definitions is safe and recommended. You only need structural metadata—table names, column names, and data types like INT or TIMESTAMP—never real customer records, passwords, or production API keys.