PhraseryPhrasery homeGet Phrasery
Data & SQL21 categories

Solve this with a window function

You're about to write a self-join and suspect there's a cleaner way.

LongSQLwindowsdrafting
I want to do the following with a window function rather than a self-join or correlated subquery.

1. Say which window function fits: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, SUM or COUNT over a frame, FIRST_VALUE, NTH_VALUE. Explain why that one and not the neighbouring one, especially ROW_NUMBER against RANK where ties exist.
2. Write the query with an explicit PARTITION BY and ORDER BY, and an explicit frame clause. Do not rely on the default frame, state it and explain what it means.
3. Say what happens at partition boundaries, and what LAG or LEAD returns on the first or last row, plus the default I should supply.
4. Note whether the result changes if the ORDER BY column has ties or NULLs, and how NULLs sort in my dialect.

If a window function is the wrong tool here, say so and show the better approach.

What I want:
Schema:
Dialect:

Making it yours

  • Paste your own material where the prompt asks for it. Everything above that line is instruction, not content.
  • Delete any rule that does not apply to you. A shorter prompt that fits beats a thorough one that does not.
  • If the answer comes back vague, add a line saying what you do not want. Constraints work better than encouragement.

Writing and reading queries. Drafting SQL from a plain question, making an inherited query legible, and moving it between dialects.

Related prompts

Explain this query to me line by lineYou inherited a 200-line query and need to understand it before changing it.Write the query from a plain-language questionYou know exactly what you want to know and not how to express it in SQL.Port this query to another SQL dialectMoving a query between Postgres, BigQuery, Snowflake, MySQL, or SQLite.Build and explain a spreadsheet formulaWhen the lookup keeps returning N/A and the nesting is four deep.Explain this query in three sentencesSomeone handed you a query and you need the gist before you touch it.Rewrite this without nested subqueriesFour levels of nesting and nobody can read it, including you.

Keep this one

Phrasery saves prompts with a right-click and puts them back the same way. Add this to your own library in one click.

Add to your browser — free