Replacing PostgreSQL Subqueries with Window Function

How to replace subqueries containing aggregate functions in PostgreSQL with Windows functions?

The WebScale Database Infrastructure Operations Experts in PostgreSQL, MySQL, MariaDB, MongoDB and ClickHouse

Copy and paste this URL into your WordPress site to embed

<blockquote class="wp-embedded-content" data-secret="qYtWCSk4K6"><a href="https://minervadb.xyz/replace-subqueries-containing-aggregate-functions-in-postgresql-with-windows-functions/">How to replace subqueries containing aggregate functions in PostgreSQL with Windows functions?</a></blockquote><iframe sandbox="allow-scripts" security="restricted" src="https://minervadb.xyz/replace-subqueries-containing-aggregate-functions-in-postgresql-with-windows-functions/embed/#?secret=qYtWCSk4K6" width="600" height="400" title="&#8220;How to replace subqueries containing aggregate functions in PostgreSQL with Windows functions?&#8221; &#8212; The WebScale Database Infrastructure Operations Experts in PostgreSQL, MySQL, MariaDB, MongoDB and ClickHouse" data-secret="qYtWCSk4K6" frameborder="0" marginwidth="0" marginheight="0" scrolling="no" class="wp-embedded-content"></iframe><script type="text/javascript">
/*! This file is auto-generated */
!function(c,d){"use strict";var e=!1,o=!1;if(d.querySelector)if(c.addEventListener)e=!0;if(c.wp=c.wp||{},c.wp.receiveEmbedMessage);else if(c.wp.receiveEmbedMessage=function(e){var t=e.data;if(!t);else if(!(t.secret||t.message||t.value));else if(/[^a-zA-Z0-9]/.test(t.secret));else{for(var r,s,a,i=d.querySelectorAll('iframe[data-secret="'+t.secret+'"]'),n=d.querySelectorAll('blockquote[data-secret="'+t.secret+'"]'),o=new RegExp("^https?:$","i"),l=0;l<n.length;l++)n[l].style.display="none";for(l=0;l<i.length;l++)if(r=i[l],e.source!==r.contentWindow);else{if(r.removeAttribute("style"),"height"===t.message){if(1e3<(s=parseInt(t.value,10)))s=1e3;else if(~~s<200)s=200;r.height=s}if("link"===t.message)if(s=d.createElement("a"),a=d.createElement("a"),s.href=r.getAttribute("src"),a.href=t.value,!o.test(a.protocol));else if(a.host===s.host)if(d.activeElement===r)c.top.location.href=t.value}}},e)c.addEventListener("message",c.wp.receiveEmbedMessage,!1),d.addEventListener("DOMContentLoaded",t,!1),c.addEventListener("load",t,!1);function t(){if(o);else{o=!0;for(var e,t,r,s=-1!==navigator.appVersion.indexOf("MSIE 10"),a=!!navigator.userAgent.match(/Trident.*rv:11\./),i=d.querySelectorAll("iframe.wp-embedded-content"),n=0;n<i.length;n++){if(!(r=(t=i[n]).getAttribute("data-secret")))r=Math.random().toString(36).substr(2,10),t.src+="#?secret="+r,t.setAttribute("data-secret",r);if(s||a)(e=t.cloneNode(!0)).removeAttribute("security"),t.parentNode.replaceChild(e,t);t.contentWindow.postMessage({message:"ready",secret:r},"*")}}}}(window,document);
</script>

Copy and paste this code into your site to embed