This was a question asked in a BI Engineer interview. I could not solve it and still scratching my head around it. Although, I used lag window function using case statements, but the interviewer did not seem to get impressed by it. Is there any other way it can be solved?
Below is the input data. If the day of the week is Monday, then it does not add the previous day's amount. It adds the amount for previous days only till Sunday which only sums up the total amount for the week.
Date Amount
27-Sep-2021 1
28-Sep-2021 13
29-Sep-2021 15
30-Sep-2021 20
1-Oct-2021 9
2-Oct-2021 20
3-Oct-2021 8
4-Oct-2021 2
5-Oct-2021 9
6-Oct-2021 11
7-Oct-2021 15
8-Oct-2021 8
9-Oct-2021 16
10-Oct-2021 3
11-Oct-2021 3
12-Oct-2021 18
And below is the expected output
Date Amount WTD
27-Sep-2021 1 1
28-Sep-2021 13 14
29-Sep-2021 15 29
30-Sep-2021 20 49
1-Oct-2021 9 58
2-Oct-2021 20 78
3-Oct-2021 8 86
4-Oct-2021 2 2
5-Oct-2021 9 11
6-Oct-2021 11 22
7-Oct-2021 15 37
8-Oct-2021 8 45
9-Oct-2021 16 61
10-Oct-2021 3 64
11-Oct-2021 3 3
12-Oct-2021 18 21
CodePudding user response：
The window function sum() over() should help here. Also note that I use SET DATEFIRST 1 to set Monday as the the first day of the week.
Example or 
CodePudding user response：
Of course, sitting at a terminal, I have the luxury of fixing syntax errors as they appear. I always try to remind myself to determine if a value can play into ROWS UNBOUNDED PRECEDING when a gap problems arises.
CREATE Table MyTable(Date DATETIME, Amount INT) INSERT MyTable VALUES ('27-Sep-2021',1), ('28-Sep-2021',13), ('29-Sep-2021',15), ('30-Sep-2021',20), ('1-Oct-2021',9), ('2-Oct-2021',20), ('3-Oct-2021',8), ('4-Oct-2021',2), ('5-Oct-2021',9), ('6-Oct-2021',11), ('7-Oct-2021',15), ('8-Oct-2021',8), ('9-Oct-2021',16), ('10-Oct-2021',3), ('11-Oct-2021',3), ('12-Oct-2021',18)
GO
16 rows affected
--Monday=2 ;WITH Grouped AS ( SELECT *, IsMondayGroup = SUM(CASE WHEN DATEPART(WEEKDAY,Date) = 2 THEN 1 ELSE 0 END) OVER (ORDER BY Date ROWS UNBOUNDED PRECEDING) FROM MyTable ) SELECT Date,Amount, GroupSum = SUM(Amount) OVER(PARTITION BY IsMondayGroup ORDER BY Date) FROM Grouped
GODate | Amount | GroupSum :---------------------- | -----: | -------: 2021-09-27 00:00:00.000 | 1 | 1 2021-09-28 00:00:00.000 | 13 | 14 2021-09-29 00:00:00.000 | 15 | 29 2021-09-30 00:00:00.000 | 20 | 49 2021-10-01 00:00:00.000 | 9 | 58 2021-10-02 00:00:00.000 | 20 | 78 2021-10-03 00:00:00.000 | 8 | 86 2021-10-04 00:00:00.000 | 2 | 2 2021-10-05 00:00:00.000 | 9 | 11 2021-10-06 00:00:00.000 | 11 | 22 2021-10-07 00:00:00.000 | 15 | 37 2021-10-08 00:00:00.000 | 8 | 45 2021-10-09 00:00:00.000 | 16 | 61 2021-10-10 00:00:00.000 | 3 | 64 2021-10-11 00:00:00.000 | 3 | 3 2021-10-12 00:00:00.000 | 18 | 21
db<>fiddle here