title: Aggregate functions are not allowed in a recursive query. Is there an alternative way to write this query?
body: <p><strong>TL;DR</strong> I can't figure out how to write a recursive Postgres query that doesn't use aggregate functions in its recursive part. Is there an alternative way to write the recursive query shown below?</p>
  
  <p>Let's say we have some sports:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE sports (id INTEGER, name TEXT);
  
  INSERT INTO sports VALUES (1, '100 meter sprint');
  INSERT INTO sports VALUES (2, '400 meter sprint');
  INSERT INTO sports VALUES (3, '50 meter swim');
  INSERT INTO sports VALUES (4, '100 meter swim');
  </code></pre>
  
  <p>And some lap times for athletes competing in those sports:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE lap_times (sport_id INTEGER, athlete TEXT, seconds NUMERIC);
  
  INSERT INTO lap_times VALUES (1, 'Alice',  10);
  INSERT INTO lap_times VALUES (1, 'Bob',    11);
  INSERT INTO lap_times VALUES (1, 'Claire', 12);
  
  INSERT INTO lap_times VALUES (2, 'Alice',  40);
  INSERT INTO lap_times VALUES (2, 'Bob',    38);
  INSERT INTO lap_times VALUES (2, 'Claire', 39);
  
  INSERT INTO lap_times VALUES (3, 'Alice',  25);
  INSERT INTO lap_times VALUES (3, 'Bob',    23);
  INSERT INTO lap_times VALUES (3, 'Claire', 24);
  
  INSERT INTO lap_times VALUES (4, 'Alice',  65);
  INSERT INTO lap_times VALUES (4, 'Bob',    67);
  INSERT INTO lap_times VALUES (4, 'Claire', 66);
  </code></pre>
  
  <p>We want to create some arbitrary categories:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE categories (id INTEGER, name TEXT);
  
  INSERT INTO categories VALUES (1, 'Running');
  INSERT INTO categories VALUES (2, 'Swimming');
  INSERT INTO categories VALUES (3, '100 meter');
  </code></pre>
  
  <p>And make our sports members of those categories:</p>
  
  <pre class="lang-sql prettyprint-override"><code>CREATE TABLE memberships (category_id INTEGER, member_type TEXT, member_id INTEGER);
  
  INSERT INTO memberships VALUES (1, 'Sport', 1);
  INSERT INTO memberships VALUES (1, 'Sport', 2);
  
  INSERT INTO memberships VALUES (2, 'Sport', 3);
  INSERT INTO memberships VALUES (2, 'Sport', 4);
  
  INSERT INTO memberships VALUES (3, 'Sport', 1);
  INSERT INTO memberships VALUES (3, 'Sport', 4);
  </code></pre>
  
  <p>And we want a 'super' category that contains other categories:</p>
  
  <pre class="lang-sql prettyprint-override"><code>INSERT INTO categories VALUES (4, 'Running + Swimming');
  
  INSERT INTO memberships VALUES (4, 'Category', 1);
  INSERT INTO memberships VALUES (4, 'Category', 2);
  </code></pre>
  
  <p>Now comes the tricky bit.</p>
  
  <p>We want to rank our athletes on their lap times for each sport:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT sport_id, athlete,
    RANK() over(PARTITION BY sport_id ORDER BY seconds)
  FROM lap_times lt;
  </code></pre>
  
  <p>But we also want to do this at a category level. When we do, the rank for the athlete should be based on their average rank across all sports in that category. For example:</p>
  
  <pre><code>Alice is 1st in 100 meter sprint and 3rd in 400 meter sprint
    -&gt; average rank: 2
  
  Bob is 2nd in 100 meter sprint and 1st in 400 meter sprint
    -&gt; average rank: 1.5
  
  Claire is 3rd in 100 meter sprint and 2nd in 400 meter sprint
    -&gt; average rank: 2.5
  
  Ranking for running: 1st Bob, 2nd Alice, 3rd Claire
  </code></pre>
  
  <p>And for 'super' categories, the rank for the athlete should be based on their average rank across categories, NOT the underlying sports within those categories. i.e. it should only consider its direct children, rather than expanding out all the sports. </p>
  
  <p>I tried my best to write a query to calculate these rankings. It's a recursive query that starts at the bottom  with the sports and works its way up through memberships to calculate rankings for categories and 'super' categories. Here's my query:</p>
  
  <pre class="lang-sql prettyprint-override"><code>WITH RECURSIVE rankings(rankable_type, rankable_id, athlete, value, rank) AS (
    SELECT 'Sport', sport_id, athlete, seconds, RANK() over(PARTITION BY sport_id ORDER BY seconds)
    FROM lap_times lt
  
    UNION ALL
  
    SELECT 'Category', category_id, athlete, avg(r.rank), RANK() OVER (PARTITION by category_id ORDER BY avg(r.rank))
    FROM categories c
    JOIN memberships m ON m.category_id = c.id
    JOIN rankings r ON r.rankable_type = m.member_type AND r.rankable_id = m.member_id
    GROUP BY category_id, athlete
  )
  SELECT * FROM rankings;
  </code></pre>
  
  <p>However, when I run it, I receive the following error:</p>
  
  <pre><code>ERROR: aggregate functions are not allowed in a recursive query's recursive term
  </code></pre>
  
  <p>This is cause by <code>avg(r.rank)</code> in the recursive part of the query. Postgresql doesn't allow aggregate functions to be called in the recursive part of the query. Is there an alternative way to write this?</p>
  
  <p>If I swap <code>avg(r.rank), RANK() ...</code> out for <code>NULL, NULL</code> the query executes and the results look correct for sports and it includes the expected number of rows for categories.</p>
  
  <p>I thought about maybe trying to unwind the recursion to two or three levels using nested queries as that would be fine for my use case, but I thought I'd ask here first before trying that.</p>
  
  <p>Another alternative might be to change the schema so it's less flexible so that sports cannot belong to multiple categories. I'm not sure how the query would look in that case, but it might be simpler?</p>
  
  <p>Thanks in advance, I really appreciate it.</p>