<?xml version="1.0" encoding="UTF-8"?>
<rss version="2.0" xmlns:atom="http://www.w3.org/2005/Atom" xmlns:dc="http://purl.org/dc/elements/1.1/">
  <channel>
    <title>DEV Community: Emir Smajic</title>
    <description>The latest articles on DEV Community by Emir Smajic (@emirium).</description>
    <link>https://dev.to/emirium</link>
    <image>
      <url>https://media2.dev.to/dynamic/image/width=90,height=90,fit=cover,gravity=auto,format=auto/https:%2F%2Fdev-to-uploads.s3.us-east-2.amazonaws.com%2Fuploads%2Fuser%2Fprofile_image%2F4251991%2F0cd3d66a-662a-4196-ad64-3109afa88adc.jpg</url>
      <title>DEV Community: Emir Smajic</title>
      <link>https://dev.to/emirium</link>
    </image>
    <atom:link rel="self" type="application/rss+xml" href="https://dev.to/feed/emirium"/>
    <language>en</language>
    <item>
      <title>SQL Window Functions Explained (with Examples You Can Run)</title>
      <dc:creator>Emir Smajic</dc:creator>
      <pubDate>Wed, 18 Nov 2026 13:41:15 +0000</pubDate>
      <link>https://dev.to/emirium/sql-window-functions-explained-with-examples-you-can-run-1a3e</link>
      <guid>https://dev.to/emirium/sql-window-functions-explained-with-examples-you-can-run-1a3e</guid>
      <description>&lt;blockquote&gt;
&lt;p&gt;👉 Every query in this post runs live, on real PostgreSQL or SQLite in your browser, at &lt;a href="https://sqlstreak.com/sql/window-functions?utm_source=devto&amp;amp;utm_campaign=window-functions" rel="noopener noreferrer"&gt;sqlstreak.com/sql/window-functions&lt;/a&gt;. Change them and see what breaks.&lt;/p&gt;
&lt;/blockquote&gt;

&lt;p&gt;Window functions were the point where SQL "clicked" for me. Before them, every "rank these", "top 3 per category" or "compared with last time" question turned into a self-join I couldn't read a week later. With them, it's one extra clause.&lt;/p&gt;

&lt;p&gt;The short version: &lt;strong&gt;a window function calculates across related rows but keeps every row.&lt;/strong&gt; &lt;code&gt;GROUP BY&lt;/code&gt; squashes a group into one row; a window function leaves the rows alone and adds the answer next to each one.&lt;/p&gt;

&lt;p&gt;All examples use a tiny animal-shelter table:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="nf"&gt;pets&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;species&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;age&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;.&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;   &lt;span class="c1"&gt;-- 20 rows: dogs, cats, rabbits, birds&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;h2&gt;
  
  
  1. OVER and PARTITION BY
&lt;/h2&gt;

&lt;p&gt;Put &lt;code&gt;OVER (…)&lt;/code&gt; after a function and it becomes a window function. &lt;code&gt;PARTITION BY&lt;/code&gt; says which rows belong together. Even plain aggregates like &lt;code&gt;MAX&lt;/code&gt; work this way:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;species&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;age&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nb"&gt;MAX&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="n"&gt;age&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;species&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;oldest_in_species&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;pets&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;name&lt;/th&gt;
&lt;th&gt;species&lt;/th&gt;
&lt;th&gt;age&lt;/th&gt;
&lt;th&gt;oldest_in_species&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Ziggy&lt;/td&gt;
&lt;td&gt;bird&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Kiwi&lt;/td&gt;
&lt;td&gt;bird&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Mochi&lt;/td&gt;
&lt;td&gt;cat&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;11&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Luna&lt;/td&gt;
&lt;td&gt;cat&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;11&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Tofu&lt;/td&gt;
&lt;td&gt;cat&lt;/td&gt;
&lt;td&gt;11&lt;/td&gt;
&lt;td&gt;11&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;…&lt;/td&gt;
&lt;td&gt;…&lt;/td&gt;
&lt;td&gt;…&lt;/td&gt;
&lt;td&gt;…&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Gizmo&lt;/td&gt;
&lt;td&gt;dog&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Clover&lt;/td&gt;
&lt;td&gt;rabbit&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Still 20 rows. Each one now knows the oldest age in its own species. With &lt;code&gt;GROUP BY species&lt;/code&gt; you'd get 4 rows and lose the names.&lt;/p&gt;

&lt;h2&gt;
  
  
  2. ROW_NUMBER vs RANK vs DENSE_RANK
&lt;/h2&gt;

&lt;p&gt;The three ranking functions differ only in how they treat ties:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;age&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nb"&gt;ROW_NUMBER&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;age&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;row_num&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nb"&gt;RANK&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt;       &lt;span class="k"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;age&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;rnk&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nb"&gt;DENSE_RANK&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;age&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;dense&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;pets&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;name&lt;/th&gt;
&lt;th&gt;age&lt;/th&gt;
&lt;th&gt;row_num&lt;/th&gt;
&lt;th&gt;rnk&lt;/th&gt;
&lt;th&gt;dense&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;Ziggy&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Luna&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Rocket&lt;/td&gt;
&lt;td&gt;5&lt;/td&gt;
&lt;td&gt;8&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Noodle&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;td&gt;8&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Olive&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;td&gt;8&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;Biscuit&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;11&lt;/td&gt;
&lt;td&gt;11&lt;/td&gt;
&lt;td&gt;9&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;ul&gt;
&lt;li&gt;
&lt;code&gt;ROW_NUMBER()&lt;/code&gt; always counts 1, 2, 3… Luna and Rocket are both 5, but one of them has to be 7 and the other 8. &lt;strong&gt;Which one is arbitrary&lt;/strong&gt;: add a tie-breaker (&lt;code&gt;ORDER BY age DESC, id&lt;/code&gt;) if it matters.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;RANK()&lt;/code&gt; gives ties the same number, then skips: 7, 7, &lt;strong&gt;9&lt;/strong&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;code&gt;DENSE_RANK()&lt;/code&gt; gives ties the same number and doesn't skip: 7, 7, &lt;strong&gt;8&lt;/strong&gt;.&lt;/li&gt;
&lt;/ul&gt;

&lt;p&gt;Interview tip: "find the second highest salary" is a &lt;code&gt;DENSE_RANK&lt;/code&gt; question, because two people can share the highest.&lt;/p&gt;

&lt;h2&gt;
  
  
  3. Top N per group
&lt;/h2&gt;

&lt;p&gt;This is the one I use most. Number the rows inside each group, then keep the first ones:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;species&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;age&lt;/span&gt;
&lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;
  &lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;species&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;name&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;age&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
    &lt;span class="nb"&gt;ROW_NUMBER&lt;/span&gt;&lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;OVER&lt;/span&gt; &lt;span class="p"&gt;(&lt;/span&gt;&lt;span class="k"&gt;PARTITION&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;species&lt;/span&gt; &lt;span class="k"&gt;ORDER&lt;/span&gt; &lt;span class="k"&gt;BY&lt;/span&gt; &lt;span class="n"&gt;age&lt;/span&gt; &lt;span class="k"&gt;DESC&lt;/span&gt;&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="k"&gt;AS&lt;/span&gt; &lt;span class="n"&gt;rn&lt;/span&gt;
  &lt;span class="k"&gt;FROM&lt;/span&gt; &lt;span class="n"&gt;pets&lt;/span&gt;
&lt;span class="p"&gt;)&lt;/span&gt; &lt;span class="n"&gt;ranked&lt;/span&gt;
&lt;span class="k"&gt;WHERE&lt;/span&gt; &lt;span class="n"&gt;rn&lt;/span&gt; &lt;span class="o"&gt;=&lt;/span&gt; &lt;span class="mi"&gt;1&lt;/span&gt;&lt;span class="p"&gt;;&lt;/span&gt;
&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;species&lt;/th&gt;
&lt;th&gt;name&lt;/th&gt;
&lt;th&gt;age&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;bird&lt;/td&gt;
&lt;td&gt;Ziggy&lt;/td&gt;
&lt;td&gt;6&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;cat&lt;/td&gt;
&lt;td&gt;Tofu&lt;/td&gt;
&lt;td&gt;11&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;dog&lt;/td&gt;
&lt;td&gt;Gizmo&lt;/td&gt;
&lt;td&gt;10&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;rabbit&lt;/td&gt;
&lt;td&gt;Clover&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;Why the subquery? SQL evaluates &lt;code&gt;WHERE&lt;/code&gt; &lt;strong&gt;before&lt;/strong&gt; window functions, so &lt;code&gt;WHERE ROW_NUMBER() OVER (...) = 1&lt;/code&gt; is an error. Compute the number first, filter outside. Change &lt;code&gt;rn = 1&lt;/code&gt; to &lt;code&gt;rn &amp;lt;= 3&lt;/code&gt; and you have "top 3 per species".&lt;/p&gt;

&lt;h2&gt;
  
  
  4. Running totals and LAG
&lt;/h2&gt;

&lt;p&gt;Add &lt;code&gt;ORDER BY&lt;/code&gt; inside &lt;code&gt;OVER&lt;/code&gt; and aggregates become running: they include everything up to the current row. &lt;code&gt;LAG(x)&lt;/code&gt; reads the value from the previous row (and &lt;code&gt;LEAD(x)&lt;/code&gt; the next one). This uses a pizza-shop &lt;code&gt;orders&lt;/code&gt; table:&lt;br&gt;
&lt;/p&gt;

&lt;div class="highlight js-code-highlight"&gt;
&lt;pre class="highlight sql"&gt;&lt;code&gt;&lt;span class="k"&gt;SELECT&lt;/span&gt; &lt;span class="n"&gt;id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;customer_id&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt; &lt;span class="n"&gt;ordered_on&lt;/span&gt;&lt;span class="p"&gt;,&lt;/span&gt;
  &lt;span class="nb"&gt;COUNT&lt;/span&gt;&lt;span class="cm"&gt;(*) OVER (ORDER BY id) AS orders_so_far,
  LAG(ordered_on) OVER (
    PARTITION BY customer_id ORDER BY ordered_on, id
  ) AS previous_order
FROM orders
ORDER BY id;
&lt;/span&gt;&lt;/code&gt;&lt;/pre&gt;

&lt;/div&gt;



&lt;div class="table-wrapper-paragraph"&gt;&lt;table&gt;
&lt;thead&gt;
&lt;tr&gt;
&lt;th&gt;id&lt;/th&gt;
&lt;th&gt;customer_id&lt;/th&gt;
&lt;th&gt;ordered_on&lt;/th&gt;
&lt;th&gt;orders_so_far&lt;/th&gt;
&lt;th&gt;previous_order&lt;/th&gt;
&lt;/tr&gt;
&lt;/thead&gt;
&lt;tbody&gt;
&lt;tr&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;2026-03-01&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;2026-03-01&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;1&lt;/td&gt;
&lt;td&gt;2026-03-03&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;2026-03-01&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;3&lt;/td&gt;
&lt;td&gt;2026-03-04&lt;/td&gt;
&lt;td&gt;4&lt;/td&gt;
&lt;td&gt;NULL&lt;/td&gt;
&lt;/tr&gt;
&lt;tr&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;2&lt;/td&gt;
&lt;td&gt;2026-03-07&lt;/td&gt;
&lt;td&gt;7&lt;/td&gt;
&lt;td&gt;2026-03-01&lt;/td&gt;
&lt;/tr&gt;
&lt;/tbody&gt;
&lt;/table&gt;&lt;/div&gt;

&lt;p&gt;&lt;code&gt;previous_order&lt;/code&gt; is per customer, because of &lt;code&gt;PARTITION BY customer_id&lt;/code&gt;. It's &lt;code&gt;NULL&lt;/code&gt; on each customer's first order. That's the building block for "days since last order", churn, and retention queries.&lt;/p&gt;

&lt;h2&gt;
  
  
  Three mistakes I made so you don't have to
&lt;/h2&gt;

&lt;ol&gt;
&lt;li&gt;
&lt;strong&gt;Filtering a window function in &lt;code&gt;WHERE&lt;/code&gt;.&lt;/strong&gt; Wrap the query (subquery or CTE) and filter outside.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;&lt;code&gt;ROW_NUMBER&lt;/code&gt; when ties should share a place.&lt;/strong&gt; Use &lt;code&gt;RANK&lt;/code&gt; or &lt;code&gt;DENSE_RANK&lt;/code&gt;.&lt;/li&gt;
&lt;li&gt;
&lt;strong&gt;Forgetting &lt;code&gt;ORDER BY&lt;/code&gt; inside &lt;code&gt;OVER&lt;/code&gt; for a running total.&lt;/strong&gt; &lt;code&gt;SUM(x) OVER ()&lt;/code&gt; gives the grand total on every row, which looks plausible and is wrong.&lt;/li&gt;
&lt;/ol&gt;

&lt;h2&gt;
  
  
  Does SQLite support this?
&lt;/h2&gt;

&lt;p&gt;Yes, since 3.25 (2018), with the same syntax as PostgreSQL for everything above. I checked every query in this post on both.&lt;/p&gt;

&lt;h2&gt;
  
  
  Try it
&lt;/h2&gt;

&lt;p&gt;The &lt;a href="https://sqlstreak.com/sql/window-functions?utm_source=devto&amp;amp;utm_campaign=window-functions" rel="noopener noreferrer"&gt;interactive version of this guide&lt;/a&gt; lets you edit each query and switch between Postgres and SQLite. I built it as part of &lt;a href="https://sqlstreak.com/?utm_source=devto&amp;amp;utm_campaign=window-functions" rel="noopener noreferrer"&gt;sqlstreak&lt;/a&gt;, a daily five-minute SQL practice app. The first lessons need no signup.&lt;/p&gt;

&lt;p&gt;What's the window function you actually use at work? I'm collecting real-world examples for the next guide.&lt;/p&gt;

</description>
      <category>sql</category>
      <category>postgres</category>
      <category>database</category>
      <category>beginners</category>
    </item>
  </channel>
</rss>
