Aggregating rows with rank or index >= N

If I understood you correctly, you can achieve this with CASE EXPRESSION :

SELECT customer_id,min(order_id),min(order_ts), CASE WHEN rnk < 5 then rnk else 5 end as rnk,sum(amount)FROM(    SELECT customer_id,            order_id,            order_ts,            RANK() OVER (PARTITION BY customer_id ORDER BY order_ts ASC) as rnk,            amount     FROM jq)GROUP BY customer_id,         CASE WHEN rnk < 5 then rnk else 5 endORDER BY customer_id

This will group each rnk > 5 as 5, so as 1 group. I selected min order_id,ts to select it form the 5th.

Though this produces the correct result, sagi's answer is more efficient.

You can use a SELECT on the result and filter for RANK < 5. Then do a UNION ALL on the aggregated values for RANK >= 5

WITH Cte AS(    SELECT         customer_id,         order_id,         order_ts,         RANK() OVER (PARTITION BY customer_id ORDER BY order_ts ASC) AS rnk,         amount     FROM jq     GROUP BY customer_id )SELECT    customer_id,    order_id,    order_ts,    rnk,    amountFROM CteWHERE rnk < 5UNION ALLSELECT    customer_id,    MIN(order_id),    MIN(order_ts),    MIN(rnk),    SUM(amount)FROM CteWHERE rnk >= 5GROUP BY customer_idORDER BY customer_id;

*This is for SQL Server

Try something like this, use an outer query to get the result

SELECT customer_id,(CASE WHEN ROW_NO <5 THEN ROW_NO ELSE 5 END) ROW_NO, SUM(amount) amountFROM (    SELECT customer_id,            order_id,            order_ts,            RANK() OVER (PARTITION BY customer_id ORDER BY order_ts ASC) ROW_NO,        amount FROM jq )   DGROUP BY customer_id,(CASE WHEN ROW_NO <5 THEN ROW_NO ELSE 5 END)