Very weird result of CTEs and subqueries
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 ;