Use SQL Window Functions for Advanced Analytics

This prompt writes a window-function query for a specific analytical question against your stated database, with inline comments explaining each clause and notes on what the result set looks like and how it performs. Reach for it when a question needs running totals, rankings, period-over-period comparisons, or 'top N per group' — the cases where GROUP BY alone falls short. Providing your schema is what makes the output runnable rather than a generic template, since window functions hinge on the exact partition and order columns.

What you can expect back

-- (1) Full query
SELECT
  customer_id,
  order_date,
  amount,
  SUM(amount) OVER (
    PARTITION BY customer_id        -- (2) restart the running total per customer
    ORDER BY order_date             -- accumulate in date order
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders
ORDER BY customer_id, order_date;

-- (3) Result set
One row per order, with a running_total column that grows as each
customer places more orders. The first order for a customer equals
its own amount; each later row adds the current amount to the prior total.

-- Performance
- The PARTITION BY/ORDER BY drives a sort; an index or clustering on
  (customer_id, order_date) helps. In BigQuery, cluster the table on
  customer_id to limit data scanned.

Illustrative example — your results will vary by tool and inputs.

Get sharper results

• 01Always paste real column names and types — a window query built on guessed columns won't run, and the partition/order columns are the whole point.
• 02If two orders share a timestamp, tell the model so it can choose a deterministic tiebreaker in the ORDER BY (e.g. add order id) — otherwise running totals and ranks can be non-deterministic.
• 03Ask it to compare ROW_NUMBER vs. RANK vs. DENSE_RANK for your case; picking the wrong one is the most common 'top N per group' bug.
• 04Request both the window-function version and a self-join or subquery alternative so you can sanity-check the result and compare performance.
• 05For big tables, ask explicitly about frame clauses — RANGE vs. ROWS behaves differently with duplicate order keys and can change your numbers.

Adapt it for your case