Window Functions AI Prompt

Write SQL using window functions to solve this analytical problem.

Problem: {{problem}}
Table: {{table}}
Database: {{database}}

1. Window function syntax:
   function_name() OVER (
     PARTITION BY column1, column2   -- optional: defines groups
     ORDER BY column3                 -- optional: defines order within the window
     ROWS/RANGE BETWEEN ... AND ...  -- optional: defines the window frame
   )

2. Ranking functions:
   ROW_NUMBER(): unique sequential number; no ties
   RANK(): same rank for ties; gaps after ties (1,1,3)
   DENSE_RANK(): same rank for ties; no gaps (1,1,2)
   NTILE(n): divide rows into n equal buckets

   Find the top customer per region:
   SELECT * FROM (
     SELECT customer_id, region, total_spend,
            ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_spend DESC) AS rn
     FROM customers
   ) WHERE rn = 1;

3. Aggregate window functions:
   Running total:
   SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date)

   Running average:
   AVG(amount) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
   -- 7-day rolling average

   Percentage of total:
   amount / SUM(amount) OVER (PARTITION BY category) AS pct_of_category

4. Lag and Lead (access previous/next rows):
   LAG(metric, 1) OVER (PARTITION BY entity_id ORDER BY date) AS prev_value
   LEAD(metric, 1) OVER (PARTITION BY entity_id ORDER BY date) AS next_value

   Period-over-period change:
   revenue - LAG(revenue, 1) OVER (PARTITION BY product_id ORDER BY month) AS mom_change

5. FIRST_VALUE / LAST_VALUE:
   FIRST_VALUE(status) OVER (PARTITION BY order_id ORDER BY updated_at) AS first_status
   LAST_VALUE(status) OVER (PARTITION BY order_id ORDER BY updated_at
     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_status
   -- LAST_VALUE requires the frame clause to reach the last row

6. WINDOW clause (reuse window definition):
   SELECT
     order_id,
     SUM(amount) OVER w AS running_total,
     AVG(amount) OVER w AS running_avg
   FROM orders
   WINDOW w AS (PARTITION BY customer_id ORDER BY order_date);

Return: SQL query using the appropriate window function, window frame explanation, and alternative approaches.