SQL Query Explainer

{
  "sql": "SELECT u.id, u.name, COUNT(o.id) as order_count, SUM(o.amount) as total_spent FROM users u LEFT JOIN orders o ON u.id = o.user_id WHERE u.created_at > '2024-01-01' GROUP BY u.id, u.name HAVING COUNT(o.id) > 0 ORDER BY total_spent DESC LIMIT 10"
}

{
  "_meta": {
    "payment": {
      "method": "x402",
      "price_usd": 0.054000000000000006,
      "settlement_id": "0x7f772b78b2bf5dabc952a4fbc36de09af7861748b5dcd5b31e398a18170f99b8"
    },
    "capability": "sql-explain",
    "latency_ms": 5646,
    "provenance": {
      "source": "claude-haiku",
      "fetched_at": "2026-06-13T04:23:51.687Z"
    }
  },
  "steps": [
    "Start with the users table and left join it with the orders table on user_id",
    "Filter to only include users created after 2024-01-01",
    "Group the results by user ID and name",
    "Calculate the count of orders and sum of order amounts for each user",
    "Filter groups to only include users with at least 1 order using HAVING clause",
    "Sort results by total_spent in descending order",
    "Return only the first 10 rows"
  ],
  "dialect": "postgres",
  "sql_length": 243,
  "explanation": "This query retrieves the top 10 users created after January 1st, 2024 who have placed at least one order, showing their ID, name, total number of orders, and total amount spent, sorted by spending in descending order.",
  "potential_issues": [
    "LEFT JOIN followed by HAVING COUNT(o.id) > 0 negates the left join benefit - should use INNER JOIN instead",
    "Missing indexes on orders.user_id and users.created_at could cause full table scans",
    "Grouping by u.name in addition to u.id is redundant if id is the primary key"
  ],
  "tables_referenced": [
    "users",
    "orders"
  ],
  "optimization_suggestions": [
    "Replace LEFT JOIN with INNER JOIN since the HAVING clause filters out users with no orders anyway",
    "Create indexes on orders.user_id and users.created_at for faster filtering and joins",
    "Remove u.name from GROUP BY clause if u.id is a primary key (PostgreSQL allows this, other databases may require it)",
    "Consider materializing the user aggregations into a summary table if this query runs frequently"
  ]
}

{
  "tables": {
    "type": "array"
  },
  "operations": {
    "type": "array"
  },
  "plain_english": {
    "type": "string"
  },
  "potential_issues": {
    "type": "array"
  }
}