SQL from QTD to daily values bigquery-CodePudding

We receive QTD data daily, what is needed is to transform it to daily -or even monthly any one would work- The value column has no particular pattern it can increase and decrease and might reach to zero because of the product returns and might be the same value -no purchase- or missing for any other reason

SKU	value	Date
ABC	200	2022-01-10
ABC	300	2022-02-10
ABC	100	2022-03-10
XYZ	1000	2022-01-10
XYZ	1200	2022-02-10
XYZ		2022-03-10

Now the required out put should be like this, also avoiding a new quarter value to get subtracted from last day of the previous quarter value

SKU	value	Date
ABC	200	2022-01-10
ABC	100	2022-02-10
ABC	-200	2022-03-10
XYZ	1000	2022-01-10
XYZ	200	2022-02-10
XYZ	0	2022-03-10

The tricky part would be in the entry of the new quarter for example assuming by default that Q4 is from October to December and Q1 from Jan to March

SKU	value	Date
ABC	200	2022-01-12
ABC	300	2022-02-12
ABC	100	2022-03-12
ABC	100	2022-01-01
ABC	250	2022-02-01
ABC	300	2022-03-01

This should be

SKU	value	Date
ABC	200	2022-01-12
ABC	100	2022-02-12
ABC	-200	2022-03-12
ABC	100	2022-01-01
ABC	150	2022-02-01
ABC	50	2022-03-01

This is on big query any help would be much appreciated

CodePudding user response：

You might consider below.

WITH sample_table AS (
  SELECT 'ABC' SKU, 200 value, '2022-01-12' Date UNION ALL
  SELECT 'ABC' SKU, 300 value, '2022-02-12' Date UNION ALL
  SELECT 'ABC' SKU, 100 value, '2022-03-12' Date UNION ALL
  SELECT 'ABC' SKU, 100 value, '2022-01-01' Date UNION ALL
  SELECT 'ABC' SKU, 250 value, '2022-02-01' Date UNION ALL
  SELECT 'ABC' SKU, 300 value, '2022-03-01' Date
)
SELECT SKU,
       IFNULL(value - LAG(value, 1, 0) OVER w, 0) AS value,
       Date
  FROM (SELECT * REPLACE(PARSE_DATE('%Y-%d-%m', Date) AS Date) FROM sample_table)
WINDOW w AS (PARTITION BY SKU, EXTRACT(YEAR FROM Date), EXTRACT(QUARTER FROM Date) 
                 ORDER BY UNIX_DATE(Date));

Query results