The most efficient way to retrieve the row associated with the minimum / maximum generally doesn't involve the
MAX() aggregates at all; instead, you can just attach an
ORDER BY to your query, and add a
LIMIT 1 to fetch only the first record.
WITH Data AS ( SELECT * FROM public.log_analyticss WHERE "buyPlatform" = 'platA' AND "date" >= '1526356073.6126819' ) (SELECT 'Min percent', * FROM Data ORDER BY "percent" ASC LIMIT 1) UNION ALL (SELECT 'Max percent', * FROM Data ORDER BY "percent" DESC LIMIT 1) UNION ALL (SELECT 'Min profit', * FROM Data ORDER BY "profit" ASC LIMIT 1) UNION ALL (SELECT 'Max profit', * FROM Data ORDER BY "profit" DESC LIMIT 1)
In your case, a temp table might be better than a CTE, as you can re-use it to compute the averages.
Note that if one of these maximum/minimum values is shared by two different rows, this query would only return one of them. The chosen row is selected effectively at random, but you can attach more fields to the
ORDER BY clause to serve as a tie-breaker.
If you actually want both records in this case, you'll need something more like Auston's or Radim's answer, i.e. compute the aggregates first, then join back to the data on the
percent columns. You can still make use of a temp table / CTE here to avoid hitting
log_analyticss more than once.