AI Execution Details

Execution #6

Question

                For each product category, calculate its completed-order revenue for 2025, its share of the total completed-order revenue in 2025, and the percentage change in revenue compared with 2024. Return only categories whose 2025 revenue was above the overall average category revenue for 2025, and rank them by 2025 revenue descending.
            

Mode

Strict SQL

Response

No response was recorded.

Token Usage Think Disabled

Input Tokens

1,597

Output Tokens

711

Reasoning Tokens

Total Tokens

2,308

Performance

AI Response Time

11.18 seconds

Total Processing Time

13.13 seconds

SQL Attempts

Final SQL

WITH "category_revenue_2025" AS (
    SELECT
        "c"."Id" AS "CategoryId",
        "c"."Name" AS "CategoryName",
        SUM("oi"."Quantity" * "oi"."UnitPrice") AS "Revenue2025"
    FROM "categories" "c"
    JOIN "products" "p" ON "c"."Id" = "p"."CategoryId"
    JOIN "order_items" "oi" ON "p"."Id" = "oi"."ProductId"
    JOIN "orders" "o" ON "oi"."OrderId" = "o"."Id"
    WHERE "o"."Status" = 'Completed'
      AND EXTRACT(YEAR FROM "o"."OrderDate") = 2025
    GROUP BY "c"."Id", "c"."Name"
),
"category_revenue_2024" AS (
    SELECT
        "c"."Id" AS "CategoryId",
        SUM("oi"."Quantity" * "oi"."UnitPrice") AS "Revenue2024"
    FROM "categories" "c"
    JOIN "products" "p" ON "c"."Id" = "p"."CategoryId"
    JOIN "order_items" "oi" ON "p"."Id" = "oi"."ProductId"
    JOIN "orders" "o" ON "oi"."OrderId" = "o"."Id"
    WHERE "o"."Status" = 'Completed'
      AND EXTRACT(YEAR FROM "o"."OrderDate") = 2024
    GROUP BY "c"."Id"
),
"total_revenue_2025" AS (
    SELECT SUM("Revenue2025") AS "TotalRevenue"
    FROM "category_revenue_2025"
),
"avg_revenue_2025" AS (
    SELECT AVG("Revenue2025") AS "AvgRevenue"
    FROM "category_revenue_2025"
)
SELECT
    "cr25"."CategoryName",
    "cr25"."Revenue2025",
    ("cr25"."Revenue2025" / "tr"."TotalRevenue") * 100 AS "ShareOfTotalRevenue2025",
    CASE
        WHEN "cr24"."Revenue2024" IS NULL OR "cr24"."Revenue2024" = 0 THEN NULL
        ELSE (("cr25"."Revenue2025" - "cr24"."Revenue2024") / "cr24"."Revenue2024") * 100
    END AS "PercentageChangeVs2024"
FROM "category_revenue_2025" "cr25"
LEFT JOIN "category_revenue_2024" "cr24" ON "cr25"."CategoryId" = "cr24"."CategoryId"
CROSS JOIN "total_revenue_2025" "tr"
CROSS JOIN "avg_revenue_2025" "ar"
WHERE "cr25"."Revenue2025" > "ar"."AvgRevenue"
ORDER BY "cr25"."Revenue2025" DESC;

SQL Generation Attempts

Attempt 1

Success 11.18 seconds

Generated SQL

WITH "category_revenue_2025" AS (
    SELECT
        "c"."Id" AS "CategoryId",
        "c"."Name" AS "CategoryName",
        SUM("oi"."Quantity" * "oi"."UnitPrice") AS "Revenue2025"
    FROM "categories" "c"
    JOIN "products" "p" ON "c"."Id" = "p"."CategoryId"
    JOIN "order_items" "oi" ON "p"."Id" = "oi"."ProductId"
    JOIN "orders" "o" ON "oi"."OrderId" = "o"."Id"
    WHERE "o"."Status" = 'Completed'
      AND EXTRACT(YEAR FROM "o"."OrderDate") = 2025
    GROUP BY "c"."Id", "c"."Name"
),
"category_revenue_2024" AS (
    SELECT
        "c"."Id" AS "CategoryId",
        SUM("oi"."Quantity" * "oi"."UnitPrice") AS "Revenue2024"
    FROM "categories" "c"
    JOIN "products" "p" ON "c"."Id" = "p"."CategoryId"
    JOIN "order_items" "oi" ON "p"."Id" = "oi"."ProductId"
    JOIN "orders" "o" ON "oi"."OrderId" = "o"."Id"
    WHERE "o"."Status" = 'Completed'
      AND EXTRACT(YEAR FROM "o"."OrderDate") = 2024
    GROUP BY "c"."Id"
),
"total_revenue_2025" AS (
    SELECT SUM("Revenue2025") AS "TotalRevenue"
    FROM "category_revenue_2025"
),
"avg_revenue_2025" AS (
    SELECT AVG("Revenue2025") AS "AvgRevenue"
    FROM "category_revenue_2025"
)
SELECT
    "cr25"."CategoryName",
    "cr25"."Revenue2025",
    ("cr25"."Revenue2025" / "tr"."TotalRevenue") * 100 AS "ShareOfTotalRevenue2025",
    CASE
        WHEN "cr24"."Revenue2024" IS NULL OR "cr24"."Revenue2024" = 0 THEN NULL
        ELSE (("cr25"."Revenue2025" - "cr24"."Revenue2024") / "cr24"."Revenue2024") * 100
    END AS "PercentageChangeVs2024"
FROM "category_revenue_2025" "cr25"
LEFT JOIN "category_revenue_2024" "cr24" ON "cr25"."CategoryId" = "cr24"."CategoryId"
CROSS JOIN "total_revenue_2025" "tr"
CROSS JOIN "avg_revenue_2025" "ar"
WHERE "cr25"."Revenue2025" > "ar"."AvgRevenue"
ORDER BY "cr25"."Revenue2025" DESC;