CODEWITHSUNDEEP
sqlgoogle-bigquery
Jonathon Chau
Jonathon Chau

Reputation: 23

Big Query SQL to work out the week to date sales

Looking for some assistance in sql via Big Query. I have 9 columns in my data shown below :

*Item *Date *Day *Day Number *WeekStart
*Channel
*Sales
*Forecast
*Profile

I would like to create an additional column called Projections. For eg :

If today was Thursday or Day Number 4, I want the query to return the sum of 'Sum of Wednesday Sales / Sum of Wed Profile,

If today was Friday or Day Number 5, Sum of Wed+Thu Sales / Sum of Wed+Thu Profile,

If today was Saturday or Day Number 6, Sum of Wed+Thu+Fri Sales/ Sum of Wed+Thu+Fri Profile

If today was Sunday or Day Number 7,Sum of Wed+Thu+Fri+Sat Sales/ Sum of Wed+Thu+Fri+Sat Profile

The attached picture is what I want to ultimately return. Hope someone is able to assist as this is way over my head.

enter image description here

Upvotes: 0

Views: 133

Answers (1)

Nestor
Nestor

Reputation: 1377

Can you try this:

WITH Sample AS
 (SELECT DATE('2023-01-02') as date1, 1 as sale,2 as profile UNION ALL
  SELECT DATE('2023-01-03'), 2, 10 UNION ALL
  SELECT DATE('2023-01-01'), 3, 10 UNION ALL
  SELECT DATE('2023-01-04'), 4, 10 UNION ALL
  SELECT DATE('2023-01-05'), 5, 10 UNION ALL
  SELECT DATE('2023-01-06'), 6, 10 UNION ALL
  SELECT DATE('2023-01-07'), 7, 10 UNION ALL
  SELECT DATE('2023-01-08'), 8, 10 UNION ALL
  SELECT DATE('2023-01-09'), 9, 10 UNION ALL
  SELECT DATE('2023-01-10'), 10, 10 UNION ALL
  SELECT DATE('2023-01-11'), 11, 10 UNION ALL
  SELECT DATE('2023-01-12'), 12, 10 UNION ALL
  SELECT DATE('2023-01-13'), 13, 10 UNION ALL
  SELECT DATE('2023-01-14'), 14, 10 UNION ALL
  SELECT DATE('2023-01-15'), 15, 10 UNION ALL
  SELECT DATE('2023-01-16'), 16, 10 UNION ALL
  SELECT DATE('2023-01-17'), 17, 10 UNION ALL
  SELECT DATE('2023-01-18'), 18, 10),

ExtractDateTemp AS
( SELECT date1,  FORMAT_DATE('%A', date1) AS nDay, CASE WHEN FORMAT_DATE('%A', date1) = "Wednesday" then 1 ELSE 0 END as grp, sale, profile from Sample),

group1 AS
(SELECT *,
  SUM(grp) OVER(
    ORDER BY UNIX_DATE(date1) 
  ) AS reset
FROM ExtractDateTemp)

Select * EXCEPT(grp,reset),SUM(sale) over (PARTITION BY reset ORDER BY UNIX_DATE(date1)
      ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as SaleTotal , SUM(profile) over (PARTITION BY reset ORDER BY UNIX_DATE(date1)
      ASC ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) as profileTotal  from group1

I partitioned the rows with weeks and assigned values per day to start the week to Wednesday then ordered them in ascending order then used sum function to add the values cumulatively excluding the current row.

Output: [![image](https://i.sstatic.net/ADyIY.png)](https://i.sstatic.net/ADyIY.png)

Be sure each week has a Wednesday or the values will be wrong. Also you can also apply the summation with profile as same as what I did with Sale Column.

UPDATED: I got the ordering of the date wrong it included the days past wednesday in the running sum, I have update the query with the right date order. You can play around the values on Sample table for testing,

Upvotes: 1

Related Questions