How to aggregate and take the last value of one column at the same time in Snowflake SQL?

user_10331351 0
Viewed 266

I need to aggregate some data while at the same time taking the last (chronological) value of one of the columns...

I can achieve this with one CTE but wondered whether there is a shorter/more efficient way of doing this.

Let say I sell grocery and have both "Actual" Sales and "Estimate" Sales in my database.

I want to report on the total sales per product as well as returning whether the latest sales number is ACTUAL or ESTIMATE.

Here is my CTE solution

CREATE OR REPLACE TABLE SALES_DATA (SOMETHING         STRING NOT NULL
                                  , DATA_QUALITY      STRING NOT NULL
                                  , SALES             INTEGER
                                  , CREATED_ON        TIMESTAMP_NTZ DEFAULT CURRENT_TIMESTAMP()
                                  );

INSERT INTO SALES_DATA(SOMETHING, DATA_QUALITY, SALES, CREATED_ON)
  VALUES('CARROTS', 'ESTIMATE',  23, '2021-03-09 13:09')
      , ('BANANAS', 'ACTUAL',     5, '2021-03-09 13:34')
      , ('CARROTS', 'ACTUAL',    12, '2021-03-09 14:09')
      , ('ORANGES', 'ACTUAL',    24, '2021-03-10 13:09')
      , ('BANANAS', 'ESTIMATE',  14, '2021-03-11 00:00')
  ;
  
-- At leaf level, just ensure all rows report the latest Data Quality instead of that of the row itself
WITH LATEST_DATA_QUALITY_ONLY
AS (
SELECT  SOMETHING
      , SALES
      , LAST_VALUE(DATA_QUALITY) OVER(PARTITION BY SOMETHING ORDER BY CREATED_ON) AS LATEST_DATA_QUALITY
  FROM SALES_DATA
)
SELECT SOMETHING
      ,MAX(LATEST_DATA_QUALITY) AS LATEST_DATA_QUALITY
      ,SUM(SALES) AS SALES
  FROM LATEST_DATA_QUALITY_ONLY 
  GROUP BY SOMETHING
  ORDER BY SOMETHING;

I expect this result

enter image description here

snowflake-cloud-data-platform sql
2 Answers

Using ARRAY_AGG to create array ordered by CREATED_ON and accessing first element:

SELECT SOMETHING
  ,(ARRAY_AGG(LATEST_DATA_QUALITY) WITHIN GROUP(ORDER BY CREATED_ON DESC))[0] 
    AS LATEST_DATA_QUALITY
  ,SUM(SALES) AS SALES   
FROM LATEST_DATA_QUALITY_ONLY    
GROUP BY SOMETHING   
ORDER BY SOMETHING;

This pattern tries to mimic KEEP clause.

user_50708790

You can turn the problem on it's head, and windowed SUM over the partition, and then only keep the last row via a QUALIFY:

SELECT something
    ,data_quality AS latest_data_quality
    ,SUM(sales) OVER (PARTITION BY something ORDER BY created_on range between unbounded preceding and unbounded following) as sales
FROM sales_data
QUALIFY ROW_NUMBER() OVER (PARTITION BY something ORDER BY created_on DESC) = 1
ORDER BY something, created_on;
user_439920
Related