Snowflake cumulative sum for multiple entry in same date for a given partition

Question

I have a table with below data set. I want to get the cumulative sum based on PK1 and PK2 as on TXN_DATE. I have tried with cumulative window frame functions and its giving the expected result. But I want the output to be in desired format which needs to be grouped by TXN_DATE.

SELECT
    PK1
    ,PK2
    ,TXN_DATE
    ,QTY
    ,SUM(QTY) OVER (PARTITION BY PK1,PK2 ORDER BY TXN_DATE ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SUM_QTY 
FROM MY_TABLE 
ORDER BY TXN_DATE;

Above query is giving the result as below,

I want the result as shown below in either one of the format, Can someone help to get the desired result based on this.

OR

Answer 1

Just get rid of rows between unbounded preceding and current row in your window function. The sum() window function will make the daily total the same for all rows on the same day that way.

with SOURCE_DATA as
(
select   COLUMN1::string as PK1
        ,COLUMN2::string as PK2
        ,COLUMN3::date   as TXN_DATE
        ,COLUMN4::int    as QTY
from (values 
('P001', 'XYZ', '2022-11-03', 15),
('P001', 'XYZ', '2022-11-08', -1),
('P001', 'XYZ', '2022-11-12', -4),
('P002', 'ABZ', '2022-11-03', 10),
('P002', 'ABZ', '2022-11-03', 1),       -- This was listed as ABC in the photo
('P002', 'ABZ', '2022-11-05', -5),
('P002', 'ABZ', '2022-11-10', -1),
('P002', 'ABZ', '2022-11-10', -1),
('P002', 'ABZ', '2022-11-10', 1)
     )
)
select   *
        ,sum(QTY) over (partition by PK1, PK2 order by TXN_DATE) QUANTITY
from SOURCE_DATA
order by PK1, TXN_DATE
;

Output:

PK1	PK2	TXN_DATE	QTY	QUANTITY
P001	XYZ	2022-11-03	15	15
P001	XYZ	2022-11-08	-1	14
P001	XYZ	2022-11-12	-4	10
P002	ABZ	2022-11-03	10	11
P002	ABZ	2022-11-03	1	11
P002	ABZ	2022-11-05	-5	6
P002	ABZ	2022-11-10	-1	5
P002	ABZ	2022-11-10	-1	5
P002	ABZ	2022-11-10	1	5