How do I use the "Write Advanced SQL Window Functions" prompt?

Copy the prompt template, fill in the variables (TABLE_NAME, COLUMN_DETAILS, DATABASE_SYSTEM, ANALYSIS_REQUIREMENTS, WINDOW_FUNCTION_TYPE), and paste it into ChatGPT, Claude, or Gemini.

What AI tools work with this data prompt?

This prompt works with chatgpt, claude, gemini. Simply copy and paste the template into any of these AI assistants.

Tips for getting the best results?

Always specify ORDER BY in window functions for consistent results across database restarts Use PARTITION BY strategically to reset calculations at logical boundaries Test window functions with edge cases like NULL values and duplicate rankings Consider using CTEs to break complex window function queries into readable components Monitor query performance and add appropriate indexes on partitioning and ordering columns

data advanced

Write Advanced SQL Window Functions

Generate complex SQL window functions for analytics, ranking, and cumulative calculations. Perfect for advanced data analysis queries.

Works with: chatgptclaudegemini

Prompt Template

You are an expert SQL developer specializing in analytical queries and window functions. I need you to write SQL window functions for the following scenario: **Database Context:** - Table name: [TABLE_NAME] - Key columns: [COLUMN_DETAILS] - Database system: [DATABASE_SYSTEM] **Analysis Requirements:** [ANALYSIS_REQUIREMENTS] **Specific Window Function Needs:** [WINDOW_FUNCTION_TYPE] Please provide: 1. Complete SQL query with appropriate window functions 2. Detailed explanation of each window function used 3. Comments explaining the PARTITION BY and ORDER BY logic 4. Performance considerations and indexing recommendations 5. Alternative approaches if applicable 6. Sample output description **Additional Requirements:** - Use proper SQL formatting and indentation - Include error handling considerations - Optimize for [DATABASE_SYSTEM] syntax - Consider edge cases (NULL values, ties, empty partitions) - Provide clear variable names and aliases If the requirements involve multiple analytical needs, break down the solution into logical components and explain how they work together. Include any necessary CTEs or subqueries to make the solution comprehensive and production-ready.

Variables to Customize

[TABLE_NAME]

The name of the database table to query

Example: sales_transactions

[COLUMN_DETAILS]

Relevant columns with their data types

Example: transaction_id (INT), customer_id (INT), transaction_date (DATE), amount (DECIMAL), product_category (VARCHAR)

[DATABASE_SYSTEM]

The specific database system being used

Example: PostgreSQL

[ANALYSIS_REQUIREMENTS]

What business questions need to be answered

Example: Calculate running totals by customer, rank customers by monthly sales, and identify top 3 products per category

[WINDOW_FUNCTION_TYPE]

Specific window functions needed

Example: ROW_NUMBER(), RANK(), SUM() OVER(), LAG(), LEAD()

Example Output

```sql -- Customer Sales Analysis with Window Functions WITH customer_monthly_sales AS ( SELECT customer_id, DATE_TRUNC('month', transaction_date) as sales_month, product_category, amount, -- Running total by customer SUM(amount) OVER ( PARTITION BY customer_id ORDER BY transaction_date ROWS UNBOUNDED PRECEDING ) as running_total, -- Rank customers by monthly sales RANK() OVER ( PARTITION BY DATE_TRUNC('month', transaction_date) ORDER BY SUM(amount) DESC ) as monthly_rank, -- Previous month comparison LAG(SUM(amount)) OVER ( PARTITION BY customer_id ORDER BY DATE_TRUNC('month', transaction_date) ) as prev_month_sales FROM sales_transactions GROUP BY customer_id, sales_month, product_category, amount ) SELECT * FROM customer_monthly_sales WHERE monthly_rank <= 10; ``` **Explanation:** - `SUM() OVER()` creates running totals partitioned by customer - `RANK()` handles ties in monthly sales rankings - `LAG()` enables month-over-month comparisons - CTE improves readability and performance **Performance Tips:** - Create indexes on (customer_id, transaction_date) - Consider partitioning large tables by date - Use LIMIT for large result sets

Pro Tips for Best Results

Always specify ORDER BY in window functions for consistent results across database restarts
Use PARTITION BY strategically to reset calculations at logical boundaries
Test window functions with edge cases like NULL values and duplicate rankings
Consider using CTEs to break complex window function queries into readable components
Monitor query performance and add appropriate indexes on partitioning and ordering columns