Very weird result of CTEs and subqueries WITH t1 AS (SELECT a.id,
                   AVG(standard_qty) std_avg,
                   AVG(poster_qty) pos_avg,
                   AVG(glossy_qty) gloss_avg
            FROM accounts a
            JOIN orders o
            ON a.id = o.account_id
            GROUP BY 1),

     t2 AS (SELECT MAX(std_avg) max_std_avg,
                   MAX(pos_avg) max_pos_avg , 
                   MAX(gloss_avg) max_gloss_avg
            FROM t1)

SELECT std_id , max_std_avg, pos_id , max_pos_avg, glos_id , max_gloss_avg
FROM(SELECT
        (SELECT id std_id  FROM t1,t2 WHERE std_avg = max_std_avg),
        (SELECT id pos_id  FROM t1,t2 WHERE pos_avg = max_pos_avg),
        (SELECT id glos_id FROM t1,t2 WHERE gloss_avg =max_gloss_avg)
    )foo ,t1,t2


RESULT:

std_id  | max_std_avg| pos_id | max_pos_avg | glos_id |  max_gloss_avg     
-----------------------------------------------------------------------
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
'
'
'
'
'
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258
(350 ROWS)


However, the output should be only 1 single row:

std_id  | max_std_avg| pos_id | max_pos_avg | glos_id |  max_gloss_avg     
-----------------------------------------------------------------------
   1341 | 1891.7777  |   4251 | 2184.4615   |    4211 | 523.258


I would really appreciate if anyone can explain why?
Thanks.
In the last, external query you cross join foo with t1 and t2, while you only need (and use) columns from foo and t2.

So remove the t1 join:

WITH 
    ...
SELECT
FROM (
     ...
     ) foo, t2 ; 




The whole external query could be simplified further, as you don't really need the t2 used four times, in the three subqueries plus the main query. You can use it once and still join it to t1 three times:

WITH t1 AS ...,
     t2 AS ...
SELECT std.id   AS std_id,   t2.max_std_avg, 
       pos.id   AS pos_id,   t2.max_pos_avg, 
       gloss.id AS gloss_id, t2.max_gloss_avg
FROM t2
     JOIN t1 AS std   ON std  .std_avg   = t2.max_std_avg
     JOIN t1 AS pos   ON pos  .pos_avg   = t2.max_pos_avg
     JOIN t1 AS gloss ON gloss.gloss_avg = t2.max_gloss_avg
;




Another way to get the same results but in a slightly different format (3 rows instead of 1), would be to not use joins at all but more window functions. You could also:


combine the two CTEs into one.
remove the accounts from the first CTE. If there is a FOREIGN KEY from orders to accounts, the results will be the same.


The new query:

WITH t1 AS (SELECT account_id,
                   AVG(standard_qty) AS std_avg,
                   AVG(poster_qty)   AS pos_avg,
                   AVG(glossy_qty)   AS gloss_avg,

                   MAX(AVG(standard_qty)) OVER () AS max_std_avg,
                   MAX(AVG(poster_qty))   OVER () AS max_pos_avg , 
                   MAX(AVG(glossy_qty))   OVER () AS max_gloss_avg
            FROM orders o
            GROUP BY account_id)

SELECT t1.*, 
       CASE WHEN std_avg   = max_std_avg
           THEN 'max_std_avg'   ELSE NULL
       END AS std_result,
       CASE WHEN pos_avg   = max_pos_avg
           THEN 'max_pos_avg'   ELSE NULL
       END AS pos_result,
       CASE WHEN gloss_avg = max_gloss_avg
           THEN 'max_gloss_avg' ELSE NULL
       END AS gloss_result
FROM t1
WHERE std_avg   = max_std_avg
   OR pos_avg   = max_pos_avg
   OR gloss_avg = max_gloss_avg ;