Coding DatabaseIntermediate30 minSaves 30 minutes

SQL Pivot Monthly Revenue: Filter-Aggregate Approach

Q: How does this approach perform with hundreds of product categories?

Performance degrades with many categories due to the increased number of aggregate functions. Each category requires its own `SUM(...) FILTER (...)` expression, which can make the query verbose and slower. For highly variable or numerous categories, consider dynamic SQL generation or a client-side pivot.

Q: Can I pivot on more than one dimension, such as month and region?

Yes, you can extend the grouping. Simply add `region` to the `GROUP BY` clause alongside the month extraction. Each row in the result set would then represent a unique month-region combination, with pivoted category revenues.

Q: What if a specific product category has no sales in a given month within the date range?

The column for that category will display `NULL` for that month, as the `SUM` aggregate will return `NULL` for no matching rows within the `FILTER` clause. You can wrap `SUM` with `COALESCE(SUM(...) FILTER (...), 0)` to explicitly show `0` instead of `NULL`.

Q: Is using `FILTER (WHERE ...)` always better than `CASE WHEN ...` for pivoting?

The `FILTER` clause is standard SQL-2003, often considered more readable for pivoting due to its explicit syntax. Some database optimizers can also process `FILTER` more efficiently than `CASE WHEN` for certain aggregate functions, though performance differences are often minor and database-specific. It primarily offers better clarity and standard compliance.

Q: How can I handle new product categories without modifying the query manually?

This specific `FILTER-aggregate` approach requires manual modification of the query for each new category to add a new `SUM(...) FILTER (...)` expression. For truly dynamic categories, you would typically need to generate the SQL query dynamically using application code, or use a database's proprietary `PIVOT` operator if available and acceptable for your needs.

For data engineers building executive reports, this prompt generates a portable SQL solution to pivot monthly revenue by product category using standard SQL FILTER-aggregate syntax, avoiding proprietary extensions.

Generate a portable SQL solution to pivot monthly revenue data by product category. This approach uses standard SQL FILTER-aggregate syntax, eliminating the need for database-specific extensions. The output includes DDL, the pivot query, expected execution plan notes, index recommendations, and test data for immediate implementation.

Claude ChatGPT Gemini

READY-TO-USE PROMPT

Copy Prompt

prompt.txt

Role: Act as a seasoned Database Engineer with expertise in SQL query optimization and data warehousing. Your focus is on producing portable, performant SQL solutions.

Context: An executive reporting team requires a monthly revenue breakdown by product category. The data needs to be pivoted so each product category appears as a distinct column, showing total revenue for each month within a specified date range. The solution must adhere to standard SQL practices, specifically using the `FILTER` clause within aggregate functions, to ensure compatibility across various SQL databases without relying on proprietary extensions or `PIVOT` operators.

Task: Generate a complete SQL solution package. This package must include:
1. **Data Definition Language (DDL)**: A `CREATE TABLE` statement for a `sales_transactions` table, including relevant columns like `transaction_date`, `product_category`, and `revenue`. Ensure appropriate data types and a primary key.
2. **Pivot Query**: A SQL query that pivots monthly revenue by product category. The query should:
* Aggregate `revenue` for each `product_category` for each month.
* Use the `FILTER (WHERE ...)` clause within aggregate functions (e.g., `SUM(revenue) FILTER (WHERE product_category = 'Electronics')`).
* Group results by month (e.g., `YYYY-MM` format).
* Cover the date range from `{{start_date}}` to `{{end_date}}`.
* Include columns for the specific `product_categories` provided in the placeholder.
3. **Expected Execution Plan Notes**: Briefly describe the likely execution plan characteristics for the generated pivot query, focusing on potential bottlenecks and how the `FILTER` clause impacts aggregation.
4. **Index Recommendation**: Suggest a specific index or set of indexes that would significantly improve the performance of the pivot query. Justify the recommendation.
5. **Test Data**: Provide `INSERT` statements for at least 10-15 rows of sample data into the `sales_transactions` table, ensuring a variety of `transaction_date`, `product_category`, and `revenue` values to demonstrate the pivot functionality across multiple months and categories.

Constraints:
* The pivot must be implemented using `SUM(...) FILTER (WHERE ...)` for each pivoted column. Do not use `CASE WHEN` for pivoting, nor any database-specific `PIVOT` operator.
* The solution must be portable across SQL databases (e.g., PostgreSQL, MySQL, SQL Server, Oracle) that support standard SQL `FILTER` clause in aggregates.
* The output must be structured clearly, with each section (DDL, Query, Plan Notes, Index, Test Data) explicitly labeled.
* Assume `{{start_date}}` and `{{end_date}}` are in 'YYYY-MM-DD' format.
* Assume `{{product_categories}}` is a comma-separated string of category names (e.g., 'Electronics,Clothing,Home Goods').

Output: Provide the DDL, the pivot query, execution plan notes, index recommendation, and test data as described.

Estimated results

DifficultyIntermediate

Setup time30 min

Time saved30 minutes

Best modelsClaude, ChatGPT, Gemini

Best audienceData Analytics, Finance

Editor's note

Why this prompt matters

Generating executive reports often requires transforming transactional data into a cross-tabulated format, such as pivoting monthly revenue figures by product category. While many database systems offer proprietary PIVOT operators, relying on these can introduce significant portability challenges. Data engineers frequently encounter scenarios where a reporting solution must function identically across diverse SQL environments, from PostgreSQL to Oracle, without extensive rewrites.

This workflow addresses that specific need by focusing on the standard SQL FILTER clause within aggregate functions. It provides a structured approach to achieve row-to-column transformation using widely supported syntax, ensuring that the resulting SQL is both efficient and universally compatible. This method is particularly valuable for teams managing multi-database infrastructures or those planning future system migrations, where vendor lock-in for core reporting logic is undesirable.

Reach for this workflow when your reporting requirements demand a clear, maintainable, and database-agnostic solution for pivoting aggregated data. It streamlines the process of creating complex summary views, allowing engineers to deliver consistent data insights regardless of the underlying database platform.

Anatomy

Prompt engineering breakdown

Role

Act as a seasoned Database Engineer with expertise in SQL query optimization and data warehousing. Your focus is on producing portable, performant SQL solutions.

Context

An executive reporting team requires a monthly revenue breakdown by product category. The data needs to be pivoted so each product category appears as a distinct column, showing total revenue for each month within a specified date range. The solution must adhere to standard SQL practices, specifically using the `FILTER` clause within aggregate functions, to ensure compatibility across various SQL databases without relying on proprietary extensions or `PIVOT` operators.

Goal

Generate a complete SQL solution package. This package must include: DDL, Pivot Query, Expected Execution Plan Notes, Index Recommendation, Test Data.

Constraints

The pivot must be implemented using `SUM(...) FILTER (WHERE ...)` for each pivoted column. Do not use `CASE WHEN` for pivoting, nor any database-specific `PIVOT` operator. The solution must be portable across SQL databases (e.g., PostgreSQL, MySQL, SQL Server, Oracle) that support standard SQL `FILTER` clause in aggregates. The output must be structured clearly, with each section (DDL, Query, Plan Notes, Index, Test Data) explicitly labeled. Assume `{{start_date}}` and `{{end_date}}` are in 'YYYY-MM-DD' format. Assume `{{product_categories}}` is a comma-separated string of category names.

Output format

Provide the DDL, the pivot query, execution plan notes, index recommendation, and test data as described, with each section explicitly labeled.

Why this structure works

The prompt's structure guides the model to a precise and functional output. Role priming establishes the desired expert perspective, ensuring the generated SQL is performant and portable. Explicit constraints, like enforcing the FILTER clause, prevent the model from using less compatible or less efficient alternatives. Finally, the clearly defined output format ensures all necessary components—DDL, query, plan notes, index, and test data—are present and organized for immediate use.

Pick your version

Prompt variations

BeginnerWorks with any model

When you need a straightforward SQL pivot using the FILTER clause and are less concerned with performance analysis or detailed indexing strategies. Ideal for learning or quick data exploration.

prompt.txt

As a SQL assistant, help me create a basic monthly revenue report. I need to see total revenue for different product categories, with each category as its own column. Use the `FILTER` clause with `SUM` for this. The report should cover dates from `{{start_date}}` to `{{end_date}}` and include these `{{categories_list}}`. Please provide the `CREATE TABLE` for a `sales` table with columns like `date`, `category`, and `amount`. Also, give me the pivot `SELECT` query and some `INSERT` statements for test data. Focus on clarity and a simple structure.

ProfessionalBest with claude

When you require a comprehensive SQL solution including DDL, a specific pivot query, execution plan insights, and index recommendations, suitable for integration into production systems or detailed reporting.

prompt.txt

Act as an experienced Data Engineer focused on creating performant and highly portable SQL solutions. Your task is to develop a complete SQL package for an executive request: pivot monthly revenue by product category. The solution must strictly use the `FILTER (WHERE ...)` clause within aggregate functions, avoiding `CASE WHEN` or database-specific PIVOT syntax, to ensure broad compatibility. Provide a `CREATE TABLE` statement for `sales_data` (including `transaction_date`, `product_category`, `revenue`), a `SELECT` query to pivot revenue by month and `{{product_categories}}` from `{{start_date}}` to `{{end_date}}`, brief notes on the query's expected execution plan, an index recommendation with justification, and 10-15 rows of sample `INSERT` data. Output should be clearly structured by section.

Short VersionWorks with any model

For quick generation of the core SQL components (DDL, pivot query, test data) without extensive explanations or performance considerations. Useful for rapid prototyping.

prompt.txt

Generate a SQL pivot query for monthly revenue by product category. Use `SUM(...) FILTER (WHERE ...)` exclusively; no `CASE WHEN` or proprietary `PIVOT`. Include `CREATE TABLE` for `sales_transactions` with `transaction_date`, `product_category`, `revenue`. The query must aggregate `revenue` for `{{product_categories}}` across months from `{{start_date}}` to `{{end_date}}`, grouped by month. Also provide `INSERT` statements for 10-15 rows of sample data. Ensure the output is standard SQL for maximum portability.

EnterpriseBest with chatgpt

For critical enterprise applications where data governance, compliance, and stakeholder communication are paramount. Requires a solution that considers reliability, auditing, and cross-functional impact.

prompt.txt

As a Senior Database Architect, design a highly reliable and auditable SQL solution for a critical executive reporting requirement. We need to pivot monthly revenue data by specific `{{product_categories}}` for the period `{{start_date}}` to `{{end_date}}`. This solution must adhere to enterprise-level data governance standards, emphasizing portability across diverse SQL environments by exclusively employing the `FILTER` clause within aggregate functions, without reliance on proprietary extensions. Deliver a `CREATE TABLE` script for `sales_transactions`, the pivot `SELECT` query, a concise risk assessment on potential performance implications with notes on execution plan, a justified index strategy for optimal resource utilization, and comprehensive `INSERT` statements for validation and UAT. Structure the output for clear stakeholder review and compliance checks.

What you'll get

Expected output

-- DDL CREATE TABLE sales_transactions ( transaction_id INT PRIMARY KEY AUTO_INCREMENT, -- Or SERIAL for PostgreSQL transaction_date DATE NOT NULL, product_category VARCHAR(50) NOT NULL, revenue DECIMAL(10, 2) NOT NULL );

-- Pivot Query SELECT DATE_FORMAT(transaction_date, '%Y-%m') AS sales_month, SUM(revenue) FILTER (WHERE product_category = 'Electronics') AS Electronics_Revenue, SUM(revenue) FILTER (WHERE product_category = 'Clothing') AS Clothing_Revenue, SUM(revenue) FILTER (WHERE product_category = 'Home Goods') AS Home_Goods_Revenue FROM sales_transactions WHERE transaction_date >= '2023-01-01' AND transaction_date <= '2023-03-31' GROUP BY sales_month ORDER BY sales_month;

-- Expected Execution Plan Notes The query will likely perform a full table scan or an index scan on sales_transactions if an appropriate index exists on transaction_date and product_category. The WHERE clause will filter rows by date range early. The GROUP BY sales_month will then aggregate the filtered data. The FILTER clauses within the SUM functions are applied during the aggregation phase, effectively creating conditional sums for each product category. This approach avoids multiple passes over the data that separate subqueries might incur, but the aggregation step itself can be resource-intensive, especially with a large number of distinct categories or a very wide date range. The database optimizer will typically handle the FILTER clause efficiently, often as part of a single aggregation pass.

-- Index Recommendation CREATE INDEX idx_sales_date_category_revenue ON sales_transactions (transaction_date, product_category, revenue); This composite index would significantly improve performance.

transaction_date: Allows for efficient filtering by the WHERE clause, reducing the number of rows processed.
product_category: Aids in the conditional aggregation within the FILTER clauses, as rows for specific categories can be quickly located or grouped.
revenue: Included as a covering column, allowing the query to be satisfied entirely from the index without needing to access the base table for revenue values, further speeding up the aggregation.

-- Test Data INSERT INTO sales_transactions (transaction_date, product_category, revenue) VALUES ('2023-01-05', 'Electronics', 1200.50), ('2023-01-10', 'Clothing', 350.75), ('2023-01-15', 'Home Goods', 800.00), ('2023-01-20', 'Electronics', 950.25), ('2023-02-01', 'Clothing', 420.00), ('2023-02-08', 'Electronics', 1500.00), ('2023-02-12', 'Home Goods', 600.50), ('2023-02-25', 'Clothing', 280.00), ('2023-03-03', 'Electronics', 1100.00), ('2023-03-10', 'Home Goods', 750.25), ('2023-03-18', 'Clothing', 500.00), ('2023-03-22', 'Electronics', 1300.00), ('2023-03-28', 'Home Goods', 900.00);

Under the hood

Why this prompt works

This prompt is effective because it employs several targeted prompt engineering techniques to guide the model toward a comprehensive and technically precise SQL solution. The initial role priming as a 'seasoned Database Engineer' establishes an expectation for high-quality, performant, and portable SQL, setting the context for the entire generation process. This ensures the output reflects best practices rather than generic code.

Explicit constraints are crucial here, specifically forbidding CASE WHEN and proprietary PIVOT operators. This directly forces the model to use the FILTER clause, which is the core requirement for portability and adherence to standard SQL. Without this constraint, the model might default to more common but less portable solutions.

The structured output requirement, detailing DDL, the pivot query, execution plan notes, index recommendations, and test data, ensures a complete, actionable package. A simple one-liner request would likely yield only the query, leaving the user to infer DDL, performance considerations, and testing. By breaking down the task into distinct, labeled sections, the prompt ensures all necessary components for a real-world implementation are provided. This comprehensive approach significantly enhances the utility of the generated output for a data engineer.

Model fit

Best AI models for this prompt

Claude

Claude models are well-suited for this task due to their strong understanding of complex SQL constructs and ability to generate detailed explanations. They typically produce accurate FILTER-aggregate syntax and can articulate the nuances of execution plans and index recommendations effectively. See the full Claude hub for deeper guidance.

ChatGPT

ChatGPT models are generalists that perform reliably for SQL generation. They can construct the DDL and pivot query correctly, though the depth of execution plan analysis or index justification might require more specific prompting. See the full ChatGPT hub for deeper guidance.

Gemini

Gemini models excel at structured code generation and often provide clear, concise SQL solutions. They are effective at adhering to the FILTER-aggregate constraint and can offer practical index recommendations that align with common database optimization strategies. See the full Gemini hub for deeper guidance.

When to use

When cross-database compatibility is a primary concern, avoiding proprietary PIVOT syntax.
For reports with a fixed, known set of product categories that do not change frequently.
To aggregate specific subsets of data within a single query pass, improving readability over nested CASE statements.
When the number of pivoted columns (product categories) is manageable, typically under 15-20.
To generate executive summaries showing monthly performance of key product lines.

When not to use

If the number of product categories is very large or highly dynamic, as the query becomes unwieldy.
When the target database has an optimized, native PIVOT operator and portability is not a strict requirement.
For interactive dashboards where column structure changes frequently based on user input or data discovery.
If performance for extremely large datasets is critical and pre-aggregation or materialized views are feasible alternatives.

Get more from it

Pro tips

1
Parameterize dates: Avoid hardcoding start/end dates to prevent manual query rewrites, ensuring consistent reporting periods.
2
Confirm category names: Ensure `product_category` values in `FILTER` clauses exactly match database entries, preventing missing data in pivoted columns.
3
Index `transaction_date` and `product_category`: Speeds up data filtering and grouping operations, mitigating slow query times.
4
Limit date ranges: Querying overly broad date ranges increases processing time; restrict reports to relevant periods for efficiency.
5
Validate data types: Mismatched `revenue` or `transaction_date` types can cause aggregation errors or incorrect sorting, affecting report accuracy.
6
Consider `COALESCE`: Wrap `SUM(...) FILTER (...)` with `COALESCE(..., 0)` to display zero instead of NULL for months with no sales in a category.

Don't ship this

Common mistakes

✗ Forgetting to extract month/year from `transaction_date` for grouping.
Fix — Use `TO_CHAR(transaction_date, 'YYYY-MM')` or equivalent for consistent monthly grouping across databases.
✗ Hardcoding categories in `FILTER` that are not present in the dataset.
Fix — Verify all specified `product_categories` exist in the `sales_transactions` table to avoid empty or zero-value columns.
✗ Missing an index on `product_category` or `transaction_date`, leading to full table scans.
Fix — Create a composite index on `(transaction_date, product_category)` for efficient filtering and sorting.
✗ Incorrectly defining the date range using `BETWEEN` or comparison operators.
Fix — Double-check `start_date` and `end_date` boundaries to capture all desired data and avoid off-by-one errors.
✗ Using `WHERE product_category IN (...)` in the main `WHERE` clause, filtering out non-pivoted categories.
Fix — Apply `FILTER (WHERE product_category = 'X')` directly within each aggregate for correct pivoting logic, not in the main `WHERE` clause.
✗ Not aliasing the pivoted columns, resulting in unreadable or default column names.
Fix — Always provide clear aliases for each `SUM(...) FILTER (...)` column, like `AS ElectronicsRevenue`.

Frequently asked questions

Q.How does this approach perform with hundreds of product categories?

Performance degrades with many categories due to the increased number of aggregate functions. Each category requires its own SUM(...) FILTER (...) expression, which can make the query verbose and slower. For highly variable or numerous categories, consider dynamic SQL generation or a client-side pivot.

Q.Can I pivot on more than one dimension, such as month and region?

Yes, you can extend the grouping. Simply add region to the GROUP BY clause alongside the month extraction. Each row in the result set would then represent a unique month-region combination, with pivoted category revenues.

Q.What if a specific product category has no sales in a given month within the date range?

The column for that category will display NULL for that month, as the SUM aggregate will return NULL for no matching rows within the FILTER clause. You can wrap SUM with COALESCE(SUM(...) FILTER (...), 0) to explicitly show 0 instead of NULL.

Q.Is using `FILTER (WHERE ...)` always better than `CASE WHEN ...` for pivoting?

The FILTER clause is standard SQL-2003, often considered more readable for pivoting due to its explicit syntax. Some database optimizers can also process FILTER more efficiently than CASE WHEN for certain aggregate functions, though performance differences are often minor and database-specific. It primarily offers better clarity and standard compliance.

Q.How can I handle new product categories without modifying the query manually?

This specific FILTER-aggregate approach requires manual modification of the query for each new category to add a new SUM(...) FILTER (...) expression. For truly dynamic categories, you would typically need to generate the SQL query dynamically using application code, or use a database's proprietary PIVOT operator if available and acceptable for your needs.

Version 1.0Last reviewed July 20, 2026

Reviewed by PromptInFlow Editorial Team