PostgreSQL error when removing orders placed closer than 2 minutes apart SQL Error [42803]: ERROR: aggregate functions are not allowed in WHERE

I am getting the following error when removing orders placed closer than 2 minutes apart, but I cannot use HAVING as there isn’t a GROUP BY in the sub query.

Am I approaching this correctly and should I GROUP something to make this work?

SQL Error [42803]: ERROR: aggregate functions are not allowed in WHERE

SELECT customer_id,
MAX(created_at) last_order_date,
MAX(created_at) + ((SELECT EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at)) 
FROM (SELECT customer_id, created_at
FROM (SELECT customer_id, created_at, rank() over (partition by customer_id order by created_at desc) lasttwo
FROM orders) sub
WHERE sub.lasttwo <= 2
AND SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2) s2) ::text||' minute')::INTERVAL AS nextdate,
(SELECT AVG(total_price - total_tax) 
FROM (SELECT customer_id, created_at, total_price, total_tax
FROM (SELECT customer_id, created_at, total_price, total_tax, rank() over (partition by customer_id order by created_at desc) lasttwo
FROM orders) sub
WHERE sub.lasttwo <= 2
AND SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2) s2) nextvalue 
FROM orders 
GROUP BY customer_id

Answer

OK, I rewrote your query using CTEs, and fixed the aggregation. Here it is:

with
sub as (
  SELECT customer_id, created_at, total_price, total_tax,
    rank() over (partition by customer_id order by created_at desc) lasttwo
  FROM orders
),
s2 as (
  SELECT customer_id, created_at, total_price, total_tax
  FROM sub
  WHERE sub.lasttwo <= 2
  GROUP BY customer_id, created_at, total_price, total_tax -- fix #1
  HAVING SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2 -- fix #2
),
subx as (
  SELECT customer_id, created_at,
  rank() over (partition by customer_id order by created_at desc) lasttwo
  FROM orders
),
s2x as (
  SELECT customer_id, created_at
  FROM subx
  WHERE sub.lasttwo <= 2
  GROUP BY customer_id, created_at -- fix #3
  HAVING SUM(EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at))) > 2 -- fix #4
)
SELECT customer_id,
  MAX(created_at) last_order_date,
  MAX(created_at) + ((
    SELECT EXTRACT(MINUTE FROM MAX(created_at)-MIN(created_at)) from s2x
  ) ::text||' minute')::INTERVAL AS nextdate,
  (SELECT AVG(total_price - total_tax) from s2) nextvalue
FROM orders
GROUP BY customer_id

I can’t really run this query and test it for real, but you get the idea.

By the way, sub and s2 can be combined into a single CTE. The same can be said about subx and s2x.