SQL Window Function and Aggregation Logic Audit
Verify GROUP BY, HAVING and window function logic in a SQL query with test data, so rankings and running totals are right.
The Open buttons send the prompt text as currently shown below to that site only when you click. Fill in the placeholders first.
[VERIFY] is an editorial flag, not a blank to fill. Wherever the prompt shows [VERIFY], check that item against a reliable source before you use it.
Please check the aggregation and window logic in this query. Database: [DATABASE_TYPE]. Query: [QUERY]. What each result column should mean: [COLUMN_MEANINGS]. A small sample of input rows (5 to 15) and the output I got: [SAMPLE_DATA]. Work through it step by step: 1) Explain in plain English what each GROUP BY, HAVING, PARTITION BY and ORDER BY clause does, and the order in which the database evaluates them. 2) Find mistakes such as ties breaking randomly in ranking, a window frame that defaults to something unintended for running totals, filtering on a window result in the wrong place, grouping at the wrong grain, mixing counts of rows with counts of distinct items, and non-aggregated columns. 3) A table with the columns Result column, What it does now, What you intended, and Fix. 4) Recalculate the output by hand from my sample rows, showing every intermediate step, and compare with the output I pasted. 5) A corrected query with comments, plus two edge-case tests (ties and empty groups). Rules: only use the sample rows given. If the sample is too small to prove anything, say what rows to add. Function names and default frame behaviour differ by database, so mark them [VERIFY] for [DATABASE_TYPE]. Do not claim your hand calculation is a substitute for running the query; I will test on a copy.
Fill in before use
Optional: type your details and the prompt above updates. Nothing is saved or sent.
Replace each bracketed placeholder with your own details, then paste into Any AI or another assistant.