Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE table_name ( TXN_DATE, NO_OF_TXNS ) AS
SELECT TRUNC( SYSDATE ) + (LEVEL - 191) * INTERVAL '1' DAY,
       LEVEL
FROM   DUAL
CONNECT BY LEVEL <= 190;
190 rows affected

SELECT TXN_DATE,
       AVG( NO_OF_TXNS ) OVER (
         ORDER BY TXN_DATE
         RANGE BETWEEN 180 PRECEDING
               AND     0   PRECEDING
       ) AS avg_no_of_txns
FROM   table_name
WHERE  NOT ( TXN_DATE - TRUNC( TXN_DATE, 'IW' ) BETWEEN 5 AND 7 );


TXN_DATE
AVG_NO_OF_TXNS


27-FEB-19
1

28-FEB-19
1.5

01-MAR-19
2

04-MAR-19
3

05-MAR-19
3.8

06-MAR-19
4.5

07-MAR-19
5.14285714285714285714285714285714285714

08-MAR-19
5.75

11-MAR-19
6.55555555555555555555555555555555555556

12-MAR-19
7.3

13-MAR-19
8

14-MAR-19
8.66666666666666666666666666666666666667

15-MAR-19
9.30769230769230769230769230769230769231

18-MAR-19
10.07142857142857142857142857142857142857

19-MAR-19
10.8

20-MAR-19
11.5

21-MAR-19
12.17647058823529411764705882352941176471

22-MAR-19
12.83333333333333333333333333333333333333

25-MAR-19
13.57894736842105263157894736842105263158

26-MAR-19
14.3

27-MAR-19
15

28-MAR-19
15.68181818181818181818181818181818181818

29-MAR-19
16.34782608695652173913043478260869565217

01-APR-19
17.08333333333333333333333333333333333333

02-APR-19
17.8

03-APR-19
18.5

04-APR-19
19.18518518518518518518518518518518518519

05-APR-19
19.85714285714285714285714285714285714286

08-APR-19
20.58620689655172413793103448275862068966

09-APR-19
21.3

10-APR-19
22

11-APR-19
22.6875

12-APR-19
23.36363636363636363636363636363636363636

15-APR-19
24.08823529411764705882352941176470588235

16-APR-19
24.8

17-APR-19
25.5

18-APR-19
26.18918918918918918918918918918918918919

19-APR-19
26.86842105263157894736842105263157894737

22-APR-19
27.58974358974358974358974358974358974359

23-APR-19
28.3

24-APR-19
29

25-APR-19
29.69047619047619047619047619047619047619

26-APR-19
30.37209302325581395348837209302325581395

29-APR-19
31.09090909090909090909090909090909090909

30-APR-19
31.8

01-MAY-19
32.5

02-MAY-19
33.19148936170212765957446808510638297872

03-MAY-19
33.875

06-MAY-19
34.59183673469387755102040816326530612245

07-MAY-19
35.3

08-MAY-19
36

09-MAY-19
36.69230769230769230769230769230769230769

10-MAY-19
37.37735849056603773584905660377358490566

13-MAY-19
38.09259259259259259259259259259259259259

14-MAY-19
38.8

15-MAY-19
39.5

16-MAY-19
40.19298245614035087719298245614035087719

17-MAY-19
40.87931034482758620689655172413793103448

20-MAY-19
41.59322033898305084745762711864406779661

21-MAY-19
42.3

22-MAY-19
43

23-MAY-19
43.69354838709677419354838709677419354839

24-MAY-19
44.38095238095238095238095238095238095238

27-MAY-19
45.09375

28-MAY-19
45.8

29-MAY-19
46.5

30-MAY-19
47.19402985074626865671641791044776119403

31-MAY-19
47.88235294117647058823529411764705882353

03-JUN-19
48.59420289855072463768115942028985507246

04-JUN-19
49.3

05-JUN-19
50

06-JUN-19
50.69444444444444444444444444444444444444

07-JUN-19
51.38356164383561643835616438356164383562

10-JUN-19
52.09459459459459459459459459459459459459

11-JUN-19
52.8

12-JUN-19
53.5

13-JUN-19
54.19480519480519480519480519480519480519

14-JUN-19
54.88461538461538461538461538461538461538

17-JUN-19
55.59493670886075949367088607594936708861

18-JUN-19
56.3

19-JUN-19
57

20-JUN-19
57.69512195121951219512195121951219512195

21-JUN-19
58.38554216867469879518072289156626506024

24-JUN-19
59.0952380952380952380952380952380952381

25-JUN-19
59.8

26-JUN-19
60.5

27-JUN-19
61.19540229885057471264367816091954022989

28-JUN-19
61.88636363636363636363636363636363636364

01-JUL-19
62.59550561797752808988764044943820224719

02-JUL-19
63.3

03-JUL-19
64

04-JUL-19
64.69565217391304347826086956521739130435

05-JUL-19
65.38709677419354838709677419354838709677

08-JUL-19
66.09574468085106382978723404255319148936

09-JUL-19
66.8

10-JUL-19
67.5

11-JUL-19
68.19587628865979381443298969072164948454

12-JUL-19
68.88775510204081632653061224489795918367

15-JUL-19
69.5959595959595959595959595959595959596

16-JUL-19
70.3

17-JUL-19
71

18-JUL-19
71.69607843137254901960784313725490196078

19-JUL-19
72.38834951456310679611650485436893203883

22-JUL-19
73.09615384615384615384615384615384615385

23-JUL-19
73.8

24-JUL-19
74.5

25-JUL-19
75.19626168224299065420560747663551401869

26-JUL-19
75.88888888888888888888888888888888888889

29-JUL-19
76.59633027522935779816513761467889908257

30-JUL-19
77.3

31-JUL-19
78

01-AUG-19
78.69642857142857142857142857142857142857

02-AUG-19
79.38938053097345132743362831858407079646

05-AUG-19
80.0964912280701754385964912280701754386

06-AUG-19
80.8

07-AUG-19
81.5

08-AUG-19
82.1965811965811965811965811965811965812

09-AUG-19
82.88983050847457627118644067796610169492

12-AUG-19
83.59663865546218487394957983193277310924

13-AUG-19
84.3

14-AUG-19
85

15-AUG-19
85.69672131147540983606557377049180327869

16-AUG-19
86.3902439024390243902439024390243902439

19-AUG-19
87.09677419354838709677419354838709677419

20-AUG-19
87.8

21-AUG-19
88.5

22-AUG-19
89.1968503937007874015748031496062992126

23-AUG-19
89.890625

26-AUG-19
90.59689922480620155038759689922480620155

27-AUG-19
92

28-AUG-19
93.40310077519379844961240310077519379845

29-AUG-19
94.8062015503875968992248062015503875969

30-AUG-19
95.5

02-SEP-19
97.59689922480620155038759689922480620155

03-SEP-19
99

04-SEP-19
100.403100775193798449612403100775193798


SELECT *
FROM   (
  SELECT TXN_DATE,
         AVG( NO_OF_TXNS ) OVER (
           ORDER BY TXN_DATE
           RANGE BETWEEN 180 PRECEDING
                 AND     0   PRECEDING
         ) AS avg_no_of_txns
  FROM   table_name
  WHERE  NOT ( TXN_DATE - TRUNC( TXN_DATE, 'IW' ) BETWEEN 5 AND 7 )
)
WHERE TXN_DATE >= TRUNC( SYSDATE ) - INTERVAL '180' DAY(3);


TXN_DATE
AVG_NO_OF_TXNS


11-MAR-19
6.55555555555555555555555555555555555556

12-MAR-19
7.3

13-MAR-19
8

14-MAR-19
8.66666666666666666666666666666666666667

15-MAR-19
9.30769230769230769230769230769230769231

18-MAR-19
10.07142857142857142857142857142857142857

19-MAR-19
10.8

20-MAR-19
11.5

21-MAR-19
12.17647058823529411764705882352941176471

22-MAR-19
12.83333333333333333333333333333333333333

25-MAR-19
13.57894736842105263157894736842105263158

26-MAR-19
14.3

27-MAR-19
15

28-MAR-19
15.68181818181818181818181818181818181818

29-MAR-19
16.34782608695652173913043478260869565217

01-APR-19
17.08333333333333333333333333333333333333

02-APR-19
17.8

03-APR-19
18.5

04-APR-19
19.18518518518518518518518518518518518519

05-APR-19
19.85714285714285714285714285714285714286

08-APR-19
20.58620689655172413793103448275862068966

09-APR-19
21.3

10-APR-19
22

11-APR-19
22.6875

12-APR-19
23.36363636363636363636363636363636363636

15-APR-19
24.08823529411764705882352941176470588235

16-APR-19
24.8

17-APR-19
25.5

18-APR-19
26.18918918918918918918918918918918918919

19-APR-19
26.86842105263157894736842105263157894737

22-APR-19
27.58974358974358974358974358974358974359

23-APR-19
28.3

24-APR-19
29

25-APR-19
29.69047619047619047619047619047619047619

26-APR-19
30.37209302325581395348837209302325581395

29-APR-19
31.09090909090909090909090909090909090909

30-APR-19
31.8

01-MAY-19
32.5

02-MAY-19
33.19148936170212765957446808510638297872

03-MAY-19
33.875

06-MAY-19
34.59183673469387755102040816326530612245

07-MAY-19
35.3

08-MAY-19
36

09-MAY-19
36.69230769230769230769230769230769230769

10-MAY-19
37.37735849056603773584905660377358490566

13-MAY-19
38.09259259259259259259259259259259259259

14-MAY-19
38.8

15-MAY-19
39.5

16-MAY-19
40.19298245614035087719298245614035087719

17-MAY-19
40.87931034482758620689655172413793103448

20-MAY-19
41.59322033898305084745762711864406779661

21-MAY-19
42.3

22-MAY-19
43

23-MAY-19
43.69354838709677419354838709677419354839

24-MAY-19
44.38095238095238095238095238095238095238

27-MAY-19
45.09375

28-MAY-19
45.8

29-MAY-19
46.5

30-MAY-19
47.19402985074626865671641791044776119403

31-MAY-19
47.88235294117647058823529411764705882353

03-JUN-19
48.59420289855072463768115942028985507246

04-JUN-19
49.3

05-JUN-19
50

06-JUN-19
50.69444444444444444444444444444444444444

07-JUN-19
51.38356164383561643835616438356164383562

10-JUN-19
52.09459459459459459459459459459459459459

11-JUN-19
52.8

12-JUN-19
53.5

13-JUN-19
54.19480519480519480519480519480519480519

14-JUN-19
54.88461538461538461538461538461538461538

17-JUN-19
55.59493670886075949367088607594936708861

18-JUN-19
56.3

19-JUN-19
57

20-JUN-19
57.69512195121951219512195121951219512195

21-JUN-19
58.38554216867469879518072289156626506024

24-JUN-19
59.0952380952380952380952380952380952381

25-JUN-19
59.8

26-JUN-19
60.5

27-JUN-19
61.19540229885057471264367816091954022989

28-JUN-19
61.88636363636363636363636363636363636364

01-JUL-19
62.59550561797752808988764044943820224719

02-JUL-19
63.3

03-JUL-19
64

04-JUL-19
64.69565217391304347826086956521739130435

05-JUL-19
65.38709677419354838709677419354838709677

08-JUL-19
66.09574468085106382978723404255319148936

09-JUL-19
66.8

10-JUL-19
67.5

11-JUL-19
68.19587628865979381443298969072164948454

12-JUL-19
68.88775510204081632653061224489795918367

15-JUL-19
69.5959595959595959595959595959595959596

16-JUL-19
70.3

17-JUL-19
71

18-JUL-19
71.69607843137254901960784313725490196078

19-JUL-19
72.38834951456310679611650485436893203883

22-JUL-19
73.09615384615384615384615384615384615385

23-JUL-19
73.8

24-JUL-19
74.5

25-JUL-19
75.19626168224299065420560747663551401869

26-JUL-19
75.88888888888888888888888888888888888889

29-JUL-19
76.59633027522935779816513761467889908257

30-JUL-19
77.3

31-JUL-19
78

01-AUG-19
78.69642857142857142857142857142857142857

02-AUG-19
79.38938053097345132743362831858407079646

05-AUG-19
80.0964912280701754385964912280701754386

06-AUG-19
80.8

07-AUG-19
81.5

08-AUG-19
82.1965811965811965811965811965811965812

09-AUG-19
82.88983050847457627118644067796610169492

12-AUG-19
83.59663865546218487394957983193277310924

13-AUG-19
84.3

14-AUG-19
85

15-AUG-19
85.69672131147540983606557377049180327869

16-AUG-19
86.3902439024390243902439024390243902439

19-AUG-19
87.09677419354838709677419354838709677419

20-AUG-19
87.8

21-AUG-19
88.5

22-AUG-19
89.1968503937007874015748031496062992126

23-AUG-19
89.890625

26-AUG-19
90.59689922480620155038759689922480620155

27-AUG-19
92

28-AUG-19
93.40310077519379844961240310077519379845

29-AUG-19
94.8062015503875968992248062015503875969

30-AUG-19
95.5

02-SEP-19
97.59689922480620155038759689922480620155

03-SEP-19
99

04-SEP-19
100.403100775193798449612403100775193798

TXN_DATE	AVG_NO_OF_TXNS
27-FEB-19	1
28-FEB-19	1.5
01-MAR-19	2
04-MAR-19	3
05-MAR-19	3.8
06-MAR-19	4.5
07-MAR-19	5.14285714285714285714285714285714285714
08-MAR-19	5.75
11-MAR-19	6.55555555555555555555555555555555555556
12-MAR-19	7.3
13-MAR-19	8
14-MAR-19	8.66666666666666666666666666666666666667
15-MAR-19	9.30769230769230769230769230769230769231
18-MAR-19	10.07142857142857142857142857142857142857
19-MAR-19	10.8
20-MAR-19	11.5
21-MAR-19	12.17647058823529411764705882352941176471
22-MAR-19	12.83333333333333333333333333333333333333
25-MAR-19	13.57894736842105263157894736842105263158
26-MAR-19	14.3
27-MAR-19	15
28-MAR-19	15.68181818181818181818181818181818181818
29-MAR-19	16.34782608695652173913043478260869565217
01-APR-19	17.08333333333333333333333333333333333333
02-APR-19	17.8
03-APR-19	18.5
04-APR-19	19.18518518518518518518518518518518518519
05-APR-19	19.85714285714285714285714285714285714286
08-APR-19	20.58620689655172413793103448275862068966
09-APR-19	21.3
10-APR-19	22
11-APR-19	22.6875
12-APR-19	23.36363636363636363636363636363636363636
15-APR-19	24.08823529411764705882352941176470588235
16-APR-19	24.8
17-APR-19	25.5
18-APR-19	26.18918918918918918918918918918918918919
19-APR-19	26.86842105263157894736842105263157894737
22-APR-19	27.58974358974358974358974358974358974359
23-APR-19	28.3
24-APR-19	29
25-APR-19	29.69047619047619047619047619047619047619
26-APR-19	30.37209302325581395348837209302325581395
29-APR-19	31.09090909090909090909090909090909090909
30-APR-19	31.8
01-MAY-19	32.5
02-MAY-19	33.19148936170212765957446808510638297872
03-MAY-19	33.875
06-MAY-19	34.59183673469387755102040816326530612245
07-MAY-19	35.3
08-MAY-19	36
09-MAY-19	36.69230769230769230769230769230769230769
10-MAY-19	37.37735849056603773584905660377358490566
13-MAY-19	38.09259259259259259259259259259259259259
14-MAY-19	38.8
15-MAY-19	39.5
16-MAY-19	40.19298245614035087719298245614035087719
17-MAY-19	40.87931034482758620689655172413793103448
20-MAY-19	41.59322033898305084745762711864406779661
21-MAY-19	42.3
22-MAY-19	43
23-MAY-19	43.69354838709677419354838709677419354839
24-MAY-19	44.38095238095238095238095238095238095238
27-MAY-19	45.09375
28-MAY-19	45.8
29-MAY-19	46.5
30-MAY-19	47.19402985074626865671641791044776119403
31-MAY-19	47.88235294117647058823529411764705882353
03-JUN-19	48.59420289855072463768115942028985507246
04-JUN-19	49.3
05-JUN-19	50
06-JUN-19	50.69444444444444444444444444444444444444
07-JUN-19	51.38356164383561643835616438356164383562
10-JUN-19	52.09459459459459459459459459459459459459
11-JUN-19	52.8
12-JUN-19	53.5
13-JUN-19	54.19480519480519480519480519480519480519
14-JUN-19	54.88461538461538461538461538461538461538
17-JUN-19	55.59493670886075949367088607594936708861
18-JUN-19	56.3
19-JUN-19	57
20-JUN-19	57.69512195121951219512195121951219512195
21-JUN-19	58.38554216867469879518072289156626506024
24-JUN-19	59.0952380952380952380952380952380952381
25-JUN-19	59.8
26-JUN-19	60.5
27-JUN-19	61.19540229885057471264367816091954022989
28-JUN-19	61.88636363636363636363636363636363636364
01-JUL-19	62.59550561797752808988764044943820224719
02-JUL-19	63.3
03-JUL-19	64
04-JUL-19	64.69565217391304347826086956521739130435
05-JUL-19	65.38709677419354838709677419354838709677
08-JUL-19	66.09574468085106382978723404255319148936
09-JUL-19	66.8
10-JUL-19	67.5
11-JUL-19	68.19587628865979381443298969072164948454
12-JUL-19	68.88775510204081632653061224489795918367
15-JUL-19	69.5959595959595959595959595959595959596
16-JUL-19	70.3
17-JUL-19	71
18-JUL-19	71.69607843137254901960784313725490196078
19-JUL-19	72.38834951456310679611650485436893203883
22-JUL-19	73.09615384615384615384615384615384615385
23-JUL-19	73.8
24-JUL-19	74.5
25-JUL-19	75.19626168224299065420560747663551401869
26-JUL-19	75.88888888888888888888888888888888888889
29-JUL-19	76.59633027522935779816513761467889908257
30-JUL-19	77.3
31-JUL-19	78
01-AUG-19	78.69642857142857142857142857142857142857
02-AUG-19	79.38938053097345132743362831858407079646
05-AUG-19	80.0964912280701754385964912280701754386
06-AUG-19	80.8
07-AUG-19	81.5
08-AUG-19	82.1965811965811965811965811965811965812
09-AUG-19	82.88983050847457627118644067796610169492
12-AUG-19	83.59663865546218487394957983193277310924
13-AUG-19	84.3
14-AUG-19	85
15-AUG-19	85.69672131147540983606557377049180327869
16-AUG-19	86.3902439024390243902439024390243902439
19-AUG-19	87.09677419354838709677419354838709677419
20-AUG-19	87.8
21-AUG-19	88.5
22-AUG-19	89.1968503937007874015748031496062992126
23-AUG-19	89.890625
26-AUG-19	90.59689922480620155038759689922480620155
27-AUG-19	92
28-AUG-19	93.40310077519379844961240310077519379845
29-AUG-19	94.8062015503875968992248062015503875969
30-AUG-19	95.5
02-SEP-19	97.59689922480620155038759689922480620155
03-SEP-19	99
04-SEP-19	100.403100775193798449612403100775193798