Use last_value() with group_by instead of as window function in Snowflake

Question

I would like to use last_value() or any similar function in a group by query instead of as a window function. It it possible?

CREATE TABLE test
    (Group_id INT, Fecha DATE, Sales INT)
;

INSERT INTO test (Group_id,Fecha,Sales)
VALUES  ( 1, '2021-10-15', 100 ),
        ( 1, '2021-11-15', 90 ),
        ( 1, '2022-01-15', 95 ),
        ( 2, '2021-10-15', 50 ),
        ( 2, '2021-11-15', 60 ),
        ( 2, '2022-01-15', 70 )
;

Current solution:

 select 
      distinct 
      group_id,
      last_value(sales) over (partition by group_id order by fecha) as last_sales,
      max(sales) over (partition by group_id) as max_sales
  from test

Desired solution:

  select
      group_id, 
      max(sales) as max_sales,
      last_value(sales) over (order by fecha) as last_sales
  from test
  group by group_id

Is there any way I can use the last_value() function as in the desired solution?

Output should be:

Answer 1

It is possible to achieve similar effect usingARRAY_AGG:

SELECT Group_id,
      -- MAX_BY should be preferred way
      MAX_BY(sales, fecha) AS last_sales_,
      -- alternative
      (ARRAY_AGG(sales) WITHIN GROUP(ORDER BY fecha DESC))[0] AS last_sales,
      MAX(Sales) AS max_sales
FROM test
GROUP BY Group_id
ORDER BY Group_id;

Output:

Related: Equivalent for Keep in Snowflake