But is there a better way?
Nothing that would be more readable nor likely more performant.
When performing aggregation it is necessary to limit the scope of the query to only whatever it is you are calculating. Since you wish to compute two things you need two separate parts plus a third to combine them.
If performance is a concern you should move the aggregation queries directly to the main query instead of using the optimization fencing CTE.
SELECT
FROM products
LEFT JOIN (
SELECT sum()
) s USING (product_id)
LEFT JOIN (
SELECT sum()
) r USING (product_id)
If the second "scope" doesn't need to be calculated but simply informs the one-and-only aggregate you should use SEMI JOIN (EXISTS) instead of a INNER/LEFT JOIN. But that isn't what you have here.
David J.
