Monthly revenue with a running total: every AI model's reply, tested

[5 lines every SQL prompt of ours shares, word for word: the whole prompt, on the methods page]
For each month of 2026 that has orders that weren't cancelled, show the month as YYYY-MM, that month's revenue in dollars from those orders, and the running total of that revenue from January through that month. Order by month.
Write one SQLite query and reply with it in a ```sql code block.

WITH m AS (
  SELECT substr(o.ordered_on, 1, 7) AS month, SUM(oi.quantity * p.price_cents) / 100.0 AS revenue
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE o.status <> 'cancelled' AND o.ordered_on LIKE '2026-%'
  GROUP BY month
)
SELECT month, revenue, SUM(revenue) OVER (ORDER BY month) AS running_total
FROM m
ORDER BY month

Exactly what this prompt's replies are checked against, with every other prompt of our test runs.

1. GPT-6 Luna

   Passed: Returned the right 8 rows.

   ```sql
   WITH monthly_revenue AS (
     SELECT
       strftime('%Y-%m', o.ordered_on) AS month,
       SUM(oi.quantity * p.price_cents) / 100.0 AS revenue
     FROM orders AS o
     JOIN order_items AS oi ON oi.order_id = o.id
     JOIN products AS p ON p.id = oi.product_id
     WHERE o.ordered_on >= '2026-01-01'
       AND o.ordered_on < '2027-01-01'
       AND o.status <> 'cancelled'
     GROUP BY strftime('%Y-%m', o.ordered_on)
   )
   SELECT
     month,
     revenue,
     SUM(revenue) OVER (ORDER BY month) AS running_total
   FROM monthly_revenue
   ORDER BY month;
   ```

   517 tokens in, 172 out · 1.8 s · $0.0001 · 1 message on Pro · answered by openai/gpt-6-luna via OpenAI · September 27, 2026

2. GLM 5.3 Flash

   Passed: Returned the right 8 rows.

   ```sql
   WITH monthly AS (
     SELECT substr(o.ordered_on, 1, 7) AS month,
            SUM(oi.quantity * p.price_cents) / 100.0 AS revenue
     FROM orders o
     JOIN order_items oi ON oi.order_id = o.id
     JOIN products p ON p.id = oi.product_id
     WHERE o.status <> 'cancelled'
       AND o.ordered_on >= '2026-01' AND o.ordered_on < '2027-01'
     GROUP BY substr(o.ordered_on, 1, 7)
   )
   SELECT month,
          revenue,
          SUM(revenue) OVER (ORDER BY month) AS running_total
   FROM monthly
   ORDER BY month;
   ```

   534 tokens in, 153 out · 1.4 s · $0.0002 · 1 message on Pro · answered by z-ai/glm-5.3-flash via Friendli · September 27, 2026

3. DeepSeek V4.1 Flash

   Passed: Returned the right 8 rows.

   ```sql
   WITH monthly AS (
     SELECT substr(o.ordered_on, 1, 7) AS month,
            SUM(oi.quantity * p.price_cents) / 100.0 AS revenue
     FROM orders o
     JOIN order_items oi ON oi.order_id = o.id
     JOIN products p ON p.id = oi.product_id
     WHERE o.status <> 'cancelled'
       AND substr(o.ordered_on, 1, 7) LIKE '2026-%'
     GROUP BY month
   )
   SELECT month,
          revenue,
          SUM(revenue) OVER (ORDER BY month) AS running_total
   FROM monthly
   ORDER BY month;
   ```

   562 tokens in, 256 out (113 of them reasoning) · 2.6 s · $0.0003 · 1 message on Pro · answered by deepseek/deepseek-v4.1-flash via StreamLake · September 27, 2026

4. GLM 5.3

   Passed: Returned the right 8 rows.

   ```sql
   SELECT
       substr(ordered_on, 1, 7) AS month,
       ROUND(SUM(oi.quantity * p.price_cents) / 100.0, 2) AS revenue,
       ROUND(SUM(SUM(oi.quantity * p.price_cents) / 100.0) OVER (ORDER BY substr(ordered_on, 1, 7)), 2) AS running_total
   FROM orders o
   JOIN order_items oi ON oi.order_id = o.id
   JOIN products p ON p.id = oi.product_id
   WHERE o.status <> 'cancelled'
     AND substr(ordered_on, 1, 4) = '2026'
   GROUP BY substr(ordered_on, 1, 7)
   ORDER BY month;
   ```

   534 tokens in, 237 out (78 of them reasoning) · 3.2 s · $0.0004 · 1 message on Pro · answered by z-ai/glm-5.3 via Morph · September 27, 2026

5. Gemini 3.8 Flash

   Passed: Returned the right 8 rows.

   ```sql
   WITH monthly_revenue AS (
       SELECT 
           strftime('%Y-%m', o.ordered_on) AS month,
           SUM(oi.quantity * p.price_cents) / 100.0 AS revenue
       FROM orders o
       JOIN order_items oi ON o.id = oi.order_id
       JOIN products p ON oi.product_id = p.id
       WHERE o.ordered_on >= '2026-01-01' 
         AND o.ordered_on < '2027-01-01'
         AND LOWER(o.status) != 'cancelled'
       GROUP BY strftime('%Y-%m', o.ordered_on)
   )
   SELECT 
       month,
       revenue,
       SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
   FROM monthly_revenue
   ORDER BY month;
   ```

   566 tokens in, 215 out · 4.7 s · $0.0012 · 1 message on Pro · answered by google/gemini-3.8-flash via Google · September 27, 2026

6. DeepSeek V4 Pro

   Passed: Returned the right 8 rows.

   ```sql
   WITH revenue_by_month AS (
       SELECT
           strftime('%Y-%m', o.ordered_on) AS month,
           SUM(oi.quantity * p.price_cents) AS revenue_cents
       FROM orders o
       JOIN order_items oi ON oi.order_id = o.id
       JOIN products p ON p.id = oi.product_id
       WHERE o.ordered_on >= '2026-01-01'
         AND o.ordered_on < '2027-01-01'
         AND o.status != 'cancelled'
       GROUP BY strftime('%Y-%m', o.ordered_on)
   )
   SELECT
       month,
       ROUND(revenue_cents / 100.0, 2) AS revenue_dollars,
       ROUND(
           SUM(revenue_cents) OVER (
               ORDER BY month
               ROWS UNBOUNDED PRECEDING
           ) / 100.0,
           2
       ) AS running_total_dollars
   FROM revenue_by_month
   ORDER BY month;
   ```

   534 tokens in, 2,033 out (1,810 of them reasoning) · 34.4 s · $0.0016 · 1 message on Pro · answered by deepseek/deepseek-v4-pro-0813 via Baidu · September 27, 2026

7. Claude Haiku 4.5

   Passed: Returned the right 8 rows.

   ```sql
   SELECT
     strftime('%Y-%m', o.ordered_on) AS month,
     ROUND(SUM(oi.quantity * p.price_cents) / 100.0, 2) AS revenue_dollars,
     ROUND(SUM(SUM(oi.quantity * p.price_cents)) OVER (ORDER BY strftime('%Y-%m', o.ordered_on)) / 100.0, 2) AS running_total_dollars
   FROM orders o
   JOIN order_items oi ON o.id = oi.order_id
   JOIN products p ON oi.product_id = p.id
   WHERE strftime('%Y', o.ordered_on) = '2026'
     AND o.status != 'cancelled'
   GROUP BY strftime('%Y-%m', o.ordered_on)
   ORDER BY month;
   ```

   566 tokens in, 202 out · 1.6 s · $0.0016 · 1 message on Pro · answered by anthropic/claude-haiku-4.5 via Amazon Bedrock · September 27, 2026

8. Claude Sonnet 5

   Passed: Returned the right 8 rows.

   ```sql
   SELECT
       month,
       revenue_dollars,
       SUM(revenue_dollars) OVER (ORDER BY month) AS running_total_dollars
   FROM (
       SELECT
           strftime('%Y-%m', o.ordered_on) AS month,
           SUM(oi.quantity * p.price_cents) / 100.0 AS revenue_dollars
       FROM orders o
       JOIN order_items oi ON oi.order_id = o.id
       JOIN products p ON p.id = oi.product_id
       WHERE o.status <> 'cancelled'
         AND strftime('%Y', o.ordered_on) = '2026'
       GROUP BY month
   ) monthly
   ORDER BY month;
   ```

   854 tokens in, 249 out · 3.0 s · $0.0042 · 1 message on Pro · answered by anthropic/claude-sonnet-5 via Claude Platform on AWS · September 27, 2026

9. Kimi K3

   Passed: Returned the right 8 rows.

   ```sql
   SELECT
     strftime('%Y-%m', o.ordered_on) AS month,
     ROUND(SUM(oi.quantity * p.price_cents) / 100.0, 2) AS revenue,
     ROUND(SUM(SUM(oi.quantity * p.price_cents)) OVER (ORDER BY strftime('%Y-%m', o.ordered_on)) / 100.0, 2) AS running_total
   FROM orders o
   JOIN order_items oi ON oi.order_id = o.id
   JOIN products p ON p.id = oi.product_id
   WHERE o.status <> 'cancelled'
     AND strftime('%Y', o.ordered_on) = '2026'
   GROUP BY month
   ORDER BY month;
   ```

   605 tokens in, 185 out (18 of them reasoning) · 2.5 s · $0.0042 · 1 message on Pro · answered by moonshotai/kimi-k3 via Together · September 27, 2026

10. GPT-6 Sol

    Passed: Returned the right 8 rows.

    ```sql
    WITH monthly AS (
      SELECT
        substr(o.ordered_on, 1, 7) AS month,
        SUM(COALESCE(oi.quantity * p.price_cents, 0)) AS revenue_cents
      FROM orders AS o
      LEFT JOIN order_items AS oi ON oi.order_id = o.id
      LEFT JOIN products AS p ON p.id = oi.product_id
      WHERE o.status <> 'cancelled'
        AND o.ordered_on >= '2026-01-01'
        AND o.ordered_on < '2027-01-01'
      GROUP BY substr(o.ordered_on, 1, 7)
    )
    SELECT
      month,
      revenue_cents / 100.0 AS revenue_dollars,
      SUM(revenue_cents) OVER (ORDER BY month) / 100.0 AS running_total_dollars
    FROM monthly
    ORDER BY month;
    ```

    517 tokens in, 325 out (129 of them reasoning) · 4.5 s · $0.0043 · 1 message on Pro · answered by openai/gpt-6-sol via OpenAI · September 27, 2026

11. Claude Sonnet 5.5

    Passed: Returned the right 8 rows.

    ```sql
    WITH monthly AS (
      SELECT
        strftime('%Y-%m', o.ordered_on) AS month,
        SUM(oi.quantity * p.price_cents) / 100.0 AS revenue
      FROM orders o
      JOIN order_items oi ON oi.order_id = o.id
      JOIN products p ON p.id = oi.product_id
      WHERE o.status <> 'cancelled'
        AND o.ordered_on >= '2026-01-01'
        AND o.ordered_on < '2027-01-01'
      GROUP BY strftime('%Y-%m', o.ordered_on)
    )
    SELECT
      month,
      revenue,
      SUM(revenue) OVER (ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
    FROM monthly
    ORDER BY month;
    ```
    
    Assumptions: cancelled orders have `status = 'cancelled'` (case-sensitive), and revenue uses the current `products.price_cents` since order items store no price. Months with no qualifying orders are omitted, as requested.

    858 tokens in, 385 out · 2.7 s · $0.0060 · 1 message on Pro · answered by anthropic/claude-sonnet-5.5 via Anthropic · September 28, 2026

12. Grok 4.7

    Passed: Returned the right 8 rows.

    ```sql
    WITH monthly AS (
      SELECT
        strftime('%Y-%m', o.ordered_on) AS month,
        SUM(oi.quantity * p.price_cents) / 100.0 AS revenue
      FROM orders AS o
      JOIN order_items AS oi ON oi.order_id = o.id
      JOIN products AS p ON p.id = oi.product_id
      WHERE o.status != 'cancelled'
        AND o.ordered_on >= '2026-01-01'
        AND o.ordered_on < '2027-01-01'
      GROUP BY month
    )
    SELECT
      month,
      revenue,
      SUM(revenue) OVER (ORDER BY month) AS running_total
    FROM monthly
    ORDER BY month;
    ```

    1,765 tokens in, 1,557 out (1,392 of them reasoning) · 16.9 s · $0.0086 · 1 message on Pro · answered by x-ai/grok-4.7 via xAI · September 27, 2026

13. Gemini 3.1 Pro

    Passed: Returned the right 8 rows.

    ```sql
    WITH monthly_revenue AS (
        SELECT 
            strftime('%Y-%m', o.ordered_on) AS month,
            SUM(oi.quantity * p.price_cents) / 100.0 AS revenue
        FROM orders o
        JOIN order_items oi ON o.id = oi.order_id
        JOIN products p ON oi.product_id = p.id
        WHERE o.ordered_on LIKE '2026-%' 
          AND o.status != 'cancelled'
        GROUP BY month
    )
    SELECT 
        month,
        revenue,
        SUM(revenue) OVER (ORDER BY month) AS running_total
    FROM monthly_revenue
    ORDER BY month;
    ```

    566 tokens in, 1,198 out (1,035 of them reasoning) · 11.1 s · $0.0155 · 1 message on Pro · answered by google/gemini-3.1-pro-preview via Google · September 27, 2026

14. Claude Opus 5.5

    Passed: Returned the right 8 rows.

    ```sql
    WITH monthly AS (
      SELECT
        strftime('%Y-%m', o.ordered_on) AS month,
        SUM(oi.quantity * p.price_cents) AS revenue_cents
      FROM orders o
      JOIN order_items oi ON oi.order_id = o.id
      JOIN products p ON p.id = oi.product_id
      WHERE o.ordered_on >= '2026-01-01'
        AND o.ordered_on < '2027-01-01'
        AND lower(o.status) NOT IN ('cancelled', 'canceled')
      GROUP BY month
    )
    SELECT
      month,
      revenue_cents / 100.0 AS revenue_dollars,
      SUM(revenue_cents) OVER (ORDER BY month) / 100.0 AS running_total_dollars
    FROM monthly
    ORDER BY month;
    ```
    
    Notes:
    - This assumes `ordered_on` is stored as ISO text, such as `2026-03-15` or `2026-03-15 10:22:00`.
    - Revenue uses the current `price_cents` from `products`, because `order_items` doesn't store the price at the time of sale.
    - Totals are added up in whole cents and divided by 100 only at the end. This avoids floating-point rounding drift.
    - The running total only includes months that have orders.

    856 tokens in, 670 out (224 of them reasoning) · 7.8 s · $0.0176 · 1 message on Pro · answered by anthropic/claude-opus-5.5 via Claude Platform on AWS · September 27, 2026

15. GPT-6 Astra

    Passed: Returned the right 8 rows.

    ```sql
    WITH monthly AS (
        SELECT
            strftime('%Y-%m', o.ordered_on) AS month,
            COALESCE(SUM(oi.quantity * p.price_cents), 0) AS revenue_cents
        FROM orders AS o
        LEFT JOIN order_items AS oi ON oi.order_id = o.id
        LEFT JOIN products AS p ON p.id = oi.product_id
        WHERE o.status <> 'cancelled'
          AND o.ordered_on >= '2026-01-01'
          AND o.ordered_on < '2027-01-01'
        GROUP BY strftime('%Y-%m', o.ordered_on)
    )
    SELECT
        month,
        revenue_cents / 100.0 AS revenue_dollars,
        SUM(revenue_cents) OVER (
            ORDER BY month
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) / 100.0 AS running_total_dollars
    FROM monthly
    ORDER BY month;
    ```

    517 tokens in, 276 out (62 of them reasoning) · 5.8 s · $0.0190 · 1 message on Pro · answered by openai/gpt-6-astra via OpenAI · September 27, 2026

16. Claude Fable 5.1

    Passed: Returned the right 8 rows.

    ```sql
    WITH monthly AS (
      SELECT
        strftime('%Y-%m', o.ordered_on) AS month,
        SUM(oi.quantity * p.price_cents) / 100.0 AS revenue_dollars
      FROM orders o
      JOIN order_items oi ON oi.order_id = o.id
      JOIN products p ON p.id = oi.product_id
      WHERE o.status <> 'cancelled'
        AND o.ordered_on >= '2026-01-01'
        AND o.ordered_on < '2027-01-01'
      GROUP BY month
    )
    SELECT
      month,
      revenue_dollars,
      SUM(revenue_dollars) OVER (ORDER BY month
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total_dollars
    FROM monthly
    ORDER BY month;
    ```
    
    Assumes `status` uses the literal value `'cancelled'` (adjust if your data uses e.g. `'canceled'` or different casing) and `ordered_on` is an ISO date/time string.

    856 tokens in, 375 out · 6.3 s · $0.0293 · 1 message on Pro · answered by anthropic/claude-fable-5.1 via Anthropic · September 27, 2026

• Customers in one country
• Count orders by status
• Revenue by category
• Every customer, even those without orders
• The best AI for SQL