Oracle Live SQL - Script: Analytics - Windows and the NTH

Statement 1

drop table trips purge

ORA-00942: table or view does not exist

More Details: https://docs.oracle.com/error-help/db/ora-00942

Statement 2

create table trips ( trip_date date, trip_count int )

Table created.

Statement 3

insert into trips values (date '2014-07-01',21228)

1 row(s) inserted.

Statement 4

insert into trips values (date '2014-07-02',26480)

1 row(s) inserted.

Statement 5

insert into trips values (date '2014-07-03',21597)

1 row(s) inserted.

Statement 6

insert into trips values (date '2014-07-04',14148)

1 row(s) inserted.

Statement 7

insert into trips values (date '2014-07-05',10890)

1 row(s) inserted.

Statement 8

insert into trips values (date '2014-07-06',11443)

1 row(s) inserted.

Statement 9

insert into trips values (date '2014-07-07',18280)

1 row(s) inserted.

Statement 10

insert into trips values (date '2014-07-08',25763)

1 row(s) inserted.

Statement 11

insert into trips values (date '2014-07-09',27817)

1 row(s) inserted.

Statement 12

insert into trips values (date '2014-07-10',30541)

1 row(s) inserted.

Statement 13

insert into trips values (date '2014-07-11',28752)

1 row(s) inserted.

Statement 14

insert into trips values (date '2014-07-12',25936)

1 row(s) inserted.

Statement 15

insert into trips values (date '2014-07-13',21082)

1 row(s) inserted.

Statement 16

insert into trips values (date '2014-07-14',27350)

1 row(s) inserted.

Statement 17

insert into trips values (date '2014-07-15',33845)

1 row(s) inserted.

Statement 18

insert into trips values (date '2014-07-16',28607)

1 row(s) inserted.

Statement 19

insert into trips values (date '2014-07-17',30710)

1 row(s) inserted.

Statement 20

insert into trips values (date '2014-07-18',29860)

1 row(s) inserted.

Statement 21

insert into trips values (date '2014-07-19',25726)

1 row(s) inserted.

Statement 22

insert into trips values (date '2014-07-20',21212)

1 row(s) inserted.

Statement 23

insert into trips values (date '2014-07-21',23578)

1 row(s) inserted.

Statement 24

insert into trips values (date '2014-07-22',29029)

1 row(s) inserted.

Statement 25

insert into trips values (date '2014-07-23',34073)

1 row(s) inserted.

Statement 26

insert into trips values (date '2014-07-24',32050)

1 row(s) inserted.

Statement 27

insert into trips values (date '2014-07-25',29975)

1 row(s) inserted.

Statement 28

insert into trips values (date '2014-07-26',27708)

1 row(s) inserted.

Statement 29

insert into trips values (date '2014-07-27',22590)

1 row(s) inserted.

Statement 30

insert into trips values (date '2014-07-28',23981)

1 row(s) inserted.

Statement 31

insert into trips values (date '2014-07-29',27589)

1 row(s) inserted.

Statement 32

insert into trips values (date '2014-07-30',30740)

1 row(s) inserted.

Statement 33

insert into trips values (date '2014-07-31',33541)

1 row(s) inserted.

Statement 34

insert into trips values (date '2014-08-01',32353)

1 row(s) inserted.

Statement 35

insert into trips values (date '2014-08-02',28678)

1 row(s) inserted.

Statement 36

insert into trips values (date '2014-08-03',23146)

1 row(s) inserted.

Statement 37

insert into trips values (date '2014-08-04',24952)

1 row(s) inserted.

Statement 38

insert into trips values (date '2014-08-05',28094)

1 row(s) inserted.

Statement 39

insert into trips values (date '2014-08-06',30495)

1 row(s) inserted.

Statement 40

insert into trips values (date '2014-08-07',32759)

1 row(s) inserted.

Statement 41

insert into trips values (date '2014-08-08',30411)

1 row(s) inserted.

Statement 42

insert into trips values (date '2014-08-09',26286)

1 row(s) inserted.

Statement 43

insert into trips values (date '2014-08-10',20584)

1 row(s) inserted.

Statement 44

insert into trips values (date '2014-08-11',22632)

1 row(s) inserted.

Statement 45

insert into trips values (date '2014-08-12',29646)

1 row(s) inserted.

Statement 46

insert into trips values (date '2014-08-13',29788)

1 row(s) inserted.

Statement 47

insert into trips values (date '2014-08-14',30457)

1 row(s) inserted.

Statement 48

insert into trips values (date '2014-08-15',28974)

1 row(s) inserted.

Statement 49

insert into trips values (date '2014-08-16',26234)

1 row(s) inserted.

Statement 50

insert into trips values (date '2014-08-17',21209)

1 row(s) inserted.

Statement 51

insert into trips values (date '2014-08-18',21956)

1 row(s) inserted.

Statement 52

insert into trips values (date '2014-08-19',24834)

1 row(s) inserted.

Statement 53

insert into trips values (date '2014-08-20',27955)

1 row(s) inserted.

Statement 54

insert into trips values (date '2014-08-21',32304)

1 row(s) inserted.

Statement 55

insert into trips values (date '2014-08-22',29512)

1 row(s) inserted.

Statement 56

insert into trips values (date '2014-08-23',27097)

1 row(s) inserted.

Statement 57

insert into trips values (date '2014-08-24',22036)

1 row(s) inserted.

Statement 58

insert into trips values (date '2014-08-25',22093)

1 row(s) inserted.

Statement 59

insert into trips values (date '2014-08-26',24550)

1 row(s) inserted.

Statement 60

insert into trips values (date '2014-08-27',27018)

1 row(s) inserted.

Statement 61

insert into trips values (date '2014-08-28',28597)

1 row(s) inserted.

Statement 62

insert into trips values (date '2014-08-29',27424)

1 row(s) inserted.

Statement 63

insert into trips values (date '2014-08-30',23930)

1 row(s) inserted.

Statement 64

insert into trips values (date '2014-08-31',23271)

1 row(s) inserted.

Statement 65

insert into trips values (date '2014-09-01',19961)

1 row(s) inserted.

Statement 66

insert into trips values (date '2014-09-02',28831)

1 row(s) inserted.

Statement 67

insert into trips values (date '2014-09-03',32631)

1 row(s) inserted.

Statement 68

insert into trips values (date '2014-09-04',38360)

1 row(s) inserted.

Statement 69

insert into trips values (date '2014-09-05',42319)

1 row(s) inserted.

Statement 70

insert into trips values (date '2014-09-06',40520)

1 row(s) inserted.

Statement 71

insert into trips values (date '2014-09-07',30134)

1 row(s) inserted.

Statement 72

insert into trips values (date '2014-09-08',30360)

1 row(s) inserted.

Statement 73

insert into trips values (date '2014-09-09',34560)

1 row(s) inserted.

Statement 74

insert into trips values (date '2014-09-10',35910)

1 row(s) inserted.

Statement 75

insert into trips values (date '2014-09-11',36439)

1 row(s) inserted.

Statement 76

insert into trips values (date '2014-09-12',39540)

1 row(s) inserted.

Statement 77

insert into trips values (date '2014-09-13',43205)

1 row(s) inserted.

Statement 78

insert into trips values (date '2014-09-14',28122)

1 row(s) inserted.

Statement 79

insert into trips values (date '2014-09-15',29454)

1 row(s) inserted.

Statement 80

insert into trips values (date '2014-09-16',36092)

1 row(s) inserted.

Statement 81

insert into trips values (date '2014-09-17',35531)

1 row(s) inserted.

Statement 82

insert into trips values (date '2014-09-18',40274)

1 row(s) inserted.

Statement 83

insert into trips values (date '2014-09-19',41017)

1 row(s) inserted.

Statement 84

insert into trips values (date '2014-09-20',38864)

1 row(s) inserted.

Statement 85

insert into trips values (date '2014-09-21',28620)

1 row(s) inserted.

Statement 86

insert into trips values (date '2014-09-22',28312)

1 row(s) inserted.

Statement 87

insert into trips values (date '2014-09-23',30316)

1 row(s) inserted.

Statement 88

insert into trips values (date '2014-09-24',31301)

1 row(s) inserted.

Statement 89

insert into trips values (date '2014-09-25',38203)

1 row(s) inserted.

Statement 90

insert into trips values (date '2014-09-26',37504)

1 row(s) inserted.

Statement 91

insert into trips values (date '2014-09-27',39468)

1 row(s) inserted.

Statement 92

insert into trips values (date '2014-09-28',29656)

1 row(s) inserted.

Statement 93

insert into trips values (date '2014-09-29',29201)

1 row(s) inserted.

Statement 94

insert into trips values (date '2014-09-30',33431)

1 row(s) inserted.

Statement 95

commit

Statement processed.

Statement 96

select * from trips

 
TRIP_DATE TRIP_COUNT
01-JUL-14 21228
02-JUL-14 26480
03-JUL-14 21597
04-JUL-14 14148
05-JUL-14 10890
06-JUL-14 11443
07-JUL-14 18280
08-JUL-14 25763
09-JUL-14 27817
10-JUL-14 30541
11-JUL-14 28752
12-JUL-14 25936
13-JUL-14 21082
14-JUL-14 27350
15-JUL-14 33845
16-JUL-14 28607
17-JUL-14 30710
18-JUL-14 29860
19-JUL-14 25726
20-JUL-14 21212
21-JUL-14 23578
22-JUL-14 29029
23-JUL-14 34073
24-JUL-14 32050
25-JUL-14 29975
26-JUL-14 27708
27-JUL-14 22590
28-JUL-14 23981
29-JUL-14 27589
30-JUL-14 30740
31-JUL-14 33541
01-AUG-14 32353
02-AUG-14 28678
03-AUG-14 23146
04-AUG-14 24952
05-AUG-14 28094
06-AUG-14 30495
07-AUG-14 32759
08-AUG-14 30411
09-AUG-14 26286
10-AUG-14 20584
11-AUG-14 22632
12-AUG-14 29646
13-AUG-14 29788
14-AUG-14 30457
15-AUG-14 28974
16-AUG-14 26234
17-AUG-14 21209
18-AUG-14 21956
19-AUG-14 24834
20-AUG-14 27955
21-AUG-14 32304
22-AUG-14 29512
23-AUG-14 27097
24-AUG-14 22036
25-AUG-14 22093
26-AUG-14 24550
27-AUG-14 27018
28-AUG-14 28597
29-AUG-14 27424
30-AUG-14 23930
31-AUG-14 23271
01-SEP-14 19961
02-SEP-14 28831
03-SEP-14 32631
04-SEP-14 38360
05-SEP-14 42319
06-SEP-14 40520
07-SEP-14 30134
08-SEP-14 30360
09-SEP-14 34560
10-SEP-14 35910
11-SEP-14 36439
12-SEP-14 39540
13-SEP-14 43205
14-SEP-14 28122
15-SEP-14 29454
16-SEP-14 36092
17-SEP-14 35531
18-SEP-14 40274
19-SEP-14 41017
20-SEP-14 38864
21-SEP-14 28620
22-SEP-14 28312
23-SEP-14 30316
24-SEP-14 31301
25-SEP-14 38203
26-SEP-14 37504
27-SEP-14 39468
28-SEP-14 29656
29-SEP-14 29201
30-SEP-14 33431

92 rows selected.

TRIP_DATE	TRIP_COUNT
01-JUL-14	21228
02-JUL-14	26480
03-JUL-14	21597
04-JUL-14	14148
05-JUL-14	10890
06-JUL-14	11443
07-JUL-14	18280
08-JUL-14	25763
09-JUL-14	27817
10-JUL-14	30541
11-JUL-14	28752
12-JUL-14	25936
13-JUL-14	21082
14-JUL-14	27350
15-JUL-14	33845
16-JUL-14	28607
17-JUL-14	30710
18-JUL-14	29860
19-JUL-14	25726
20-JUL-14	21212
21-JUL-14	23578
22-JUL-14	29029
23-JUL-14	34073
24-JUL-14	32050
25-JUL-14	29975
26-JUL-14	27708
27-JUL-14	22590
28-JUL-14	23981
29-JUL-14	27589
30-JUL-14	30740
31-JUL-14	33541
01-AUG-14	32353
02-AUG-14	28678
03-AUG-14	23146
04-AUG-14	24952
05-AUG-14	28094
06-AUG-14	30495
07-AUG-14	32759
08-AUG-14	30411
09-AUG-14	26286
10-AUG-14	20584
11-AUG-14	22632
12-AUG-14	29646
13-AUG-14	29788
14-AUG-14	30457
15-AUG-14	28974
16-AUG-14	26234
17-AUG-14	21209
18-AUG-14	21956
19-AUG-14	24834
20-AUG-14	27955
21-AUG-14	32304
22-AUG-14	29512
23-AUG-14	27097
24-AUG-14	22036
25-AUG-14	22093
26-AUG-14	24550
27-AUG-14	27018
28-AUG-14	28597
29-AUG-14	27424
30-AUG-14	23930
31-AUG-14	23271
01-SEP-14	19961
02-SEP-14	28831
03-SEP-14	32631
04-SEP-14	38360
05-SEP-14	42319
06-SEP-14	40520
07-SEP-14	30134
08-SEP-14	30360
09-SEP-14	34560
10-SEP-14	35910
11-SEP-14	36439
12-SEP-14	39540
13-SEP-14	43205
14-SEP-14	28122
15-SEP-14	29454
16-SEP-14	36092
17-SEP-14	35531
18-SEP-14	40274
19-SEP-14	41017
20-SEP-14	38864
21-SEP-14	28620
22-SEP-14	28312
23-SEP-14	30316
24-SEP-14	31301
25-SEP-14	38203
26-SEP-14	37504
27-SEP-14	39468
28-SEP-14	29656
29-SEP-14	29201
30-SEP-14	33431

Statement 97

select 
  trip_date,
  trip_count, 
    nth_value(trip_count,2) over (
       order by trip_count
       range between unbounded preceding and unbounded following ) as low_2
from trips
order by 1

 
TRIP_DATE TRIP_COUNT LOW_2
01-JUL-14 21228 11443
02-JUL-14 26480 11443
03-JUL-14 21597 11443
04-JUL-14 14148 11443
05-JUL-14 10890 11443
06-JUL-14 11443 11443
07-JUL-14 18280 11443
08-JUL-14 25763 11443
09-JUL-14 27817 11443
10-JUL-14 30541 11443
11-JUL-14 28752 11443
12-JUL-14 25936 11443
13-JUL-14 21082 11443
14-JUL-14 27350 11443
15-JUL-14 33845 11443
16-JUL-14 28607 11443
17-JUL-14 30710 11443
18-JUL-14 29860 11443
19-JUL-14 25726 11443
20-JUL-14 21212 11443
21-JUL-14 23578 11443
22-JUL-14 29029 11443
23-JUL-14 34073 11443
24-JUL-14 32050 11443
25-JUL-14 29975 11443
26-JUL-14 27708 11443
27-JUL-14 22590 11443
28-JUL-14 23981 11443
29-JUL-14 27589 11443
30-JUL-14 30740 11443
31-JUL-14 33541 11443
01-AUG-14 32353 11443
02-AUG-14 28678 11443
03-AUG-14 23146 11443
04-AUG-14 24952 11443
05-AUG-14 28094 11443
06-AUG-14 30495 11443
07-AUG-14 32759 11443
08-AUG-14 30411 11443
09-AUG-14 26286 11443
10-AUG-14 20584 11443
11-AUG-14 22632 11443
12-AUG-14 29646 11443
13-AUG-14 29788 11443
14-AUG-14 30457 11443
15-AUG-14 28974 11443
16-AUG-14 26234 11443
17-AUG-14 21209 11443
18-AUG-14 21956 11443
19-AUG-14 24834 11443
20-AUG-14 27955 11443
21-AUG-14 32304 11443
22-AUG-14 29512 11443
23-AUG-14 27097 11443
24-AUG-14 22036 11443
25-AUG-14 22093 11443
26-AUG-14 24550 11443
27-AUG-14 27018 11443
28-AUG-14 28597 11443
29-AUG-14 27424 11443
30-AUG-14 23930 11443
31-AUG-14 23271 11443
01-SEP-14 19961 11443
02-SEP-14 28831 11443
03-SEP-14 32631 11443
04-SEP-14 38360 11443
05-SEP-14 42319 11443
06-SEP-14 40520 11443
07-SEP-14 30134 11443
08-SEP-14 30360 11443
09-SEP-14 34560 11443
10-SEP-14 35910 11443
11-SEP-14 36439 11443
12-SEP-14 39540 11443
13-SEP-14 43205 11443
14-SEP-14 28122 11443
15-SEP-14 29454 11443
16-SEP-14 36092 11443
17-SEP-14 35531 11443
18-SEP-14 40274 11443
19-SEP-14 41017 11443
20-SEP-14 38864 11443
21-SEP-14 28620 11443
22-SEP-14 28312 11443
23-SEP-14 30316 11443
24-SEP-14 31301 11443
25-SEP-14 38203 11443
26-SEP-14 37504 11443
27-SEP-14 39468 11443
28-SEP-14 29656 11443
29-SEP-14 29201 11443
30-SEP-14 33431 11443

92 rows selected.

TRIP_DATE	TRIP_COUNT	LOW_2
01-JUL-14	21228	11443
02-JUL-14	26480	11443
03-JUL-14	21597	11443
04-JUL-14	14148	11443
05-JUL-14	10890	11443
06-JUL-14	11443	11443
07-JUL-14	18280	11443
08-JUL-14	25763	11443
09-JUL-14	27817	11443
10-JUL-14	30541	11443
11-JUL-14	28752	11443
12-JUL-14	25936	11443
13-JUL-14	21082	11443
14-JUL-14	27350	11443
15-JUL-14	33845	11443
16-JUL-14	28607	11443
17-JUL-14	30710	11443
18-JUL-14	29860	11443
19-JUL-14	25726	11443
20-JUL-14	21212	11443
21-JUL-14	23578	11443
22-JUL-14	29029	11443
23-JUL-14	34073	11443
24-JUL-14	32050	11443
25-JUL-14	29975	11443
26-JUL-14	27708	11443
27-JUL-14	22590	11443
28-JUL-14	23981	11443
29-JUL-14	27589	11443
30-JUL-14	30740	11443
31-JUL-14	33541	11443
01-AUG-14	32353	11443
02-AUG-14	28678	11443
03-AUG-14	23146	11443
04-AUG-14	24952	11443
05-AUG-14	28094	11443
06-AUG-14	30495	11443
07-AUG-14	32759	11443
08-AUG-14	30411	11443
09-AUG-14	26286	11443
10-AUG-14	20584	11443
11-AUG-14	22632	11443
12-AUG-14	29646	11443
13-AUG-14	29788	11443
14-AUG-14	30457	11443
15-AUG-14	28974	11443
16-AUG-14	26234	11443
17-AUG-14	21209	11443
18-AUG-14	21956	11443
19-AUG-14	24834	11443
20-AUG-14	27955	11443
21-AUG-14	32304	11443
22-AUG-14	29512	11443
23-AUG-14	27097	11443
24-AUG-14	22036	11443
25-AUG-14	22093	11443
26-AUG-14	24550	11443
27-AUG-14	27018	11443
28-AUG-14	28597	11443
29-AUG-14	27424	11443
30-AUG-14	23930	11443
31-AUG-14	23271	11443
01-SEP-14	19961	11443
02-SEP-14	28831	11443
03-SEP-14	32631	11443
04-SEP-14	38360	11443
05-SEP-14	42319	11443
06-SEP-14	40520	11443
07-SEP-14	30134	11443
08-SEP-14	30360	11443
09-SEP-14	34560	11443
10-SEP-14	35910	11443
11-SEP-14	36439	11443
12-SEP-14	39540	11443
13-SEP-14	43205	11443
14-SEP-14	28122	11443
15-SEP-14	29454	11443
16-SEP-14	36092	11443
17-SEP-14	35531	11443
18-SEP-14	40274	11443
19-SEP-14	41017	11443
20-SEP-14	38864	11443
21-SEP-14	28620	11443
22-SEP-14	28312	11443
23-SEP-14	30316	11443
24-SEP-14	31301	11443
25-SEP-14	38203	11443
26-SEP-14	37504	11443
27-SEP-14	39468	11443
28-SEP-14	29656	11443
29-SEP-14	29201	11443
30-SEP-14	33431	11443

Statement 98

select 
  trip_date,
  trip_count, 
  round(100*
    trip_count /
    nth_value(trip_count,2) over (
       order by trip_count
       range between unbounded preceding and unbounded following )
    ,2) as pct_comparison
from trips
order by 1

 
TRIP_DATE TRIP_COUNT PCT_COMPARISON
01-JUL-14 21228 185.51
02-JUL-14 26480 231.41
03-JUL-14 21597 188.74
04-JUL-14 14148 123.64
05-JUL-14 10890 95.17
06-JUL-14 11443 100
07-JUL-14 18280 159.75
08-JUL-14 25763 225.14
09-JUL-14 27817 243.09
10-JUL-14 30541 266.9
11-JUL-14 28752 251.26
12-JUL-14 25936 226.65
13-JUL-14 21082 184.23
14-JUL-14 27350 239.01
15-JUL-14 33845 295.77
16-JUL-14 28607 250
17-JUL-14 30710 268.37
18-JUL-14 29860 260.95
19-JUL-14 25726 224.82
20-JUL-14 21212 185.37
21-JUL-14 23578 206.05
22-JUL-14 29029 253.68
23-JUL-14 34073 297.76
24-JUL-14 32050 280.08
25-JUL-14 29975 261.95
26-JUL-14 27708 242.14
27-JUL-14 22590 197.41
28-JUL-14 23981 209.57
29-JUL-14 27589 241.1
30-JUL-14 30740 268.64
31-JUL-14 33541 293.11
01-AUG-14 32353 282.73
02-AUG-14 28678 250.62
03-AUG-14 23146 202.27
04-AUG-14 24952 218.05
05-AUG-14 28094 245.51
06-AUG-14 30495 266.49
07-AUG-14 32759 286.28
08-AUG-14 30411 265.76
09-AUG-14 26286 229.71
10-AUG-14 20584 179.88
11-AUG-14 22632 197.78
12-AUG-14 29646 259.08
13-AUG-14 29788 260.32
14-AUG-14 30457 266.16
15-AUG-14 28974 253.2
16-AUG-14 26234 229.26
17-AUG-14 21209 185.34
18-AUG-14 21956 191.87
19-AUG-14 24834 217.02
20-AUG-14 27955 244.3
21-AUG-14 32304 282.3
22-AUG-14 29512 257.9
23-AUG-14 27097 236.8
24-AUG-14 22036 192.57
25-AUG-14 22093 193.07
26-AUG-14 24550 214.54
27-AUG-14 27018 236.11
28-AUG-14 28597 249.91
29-AUG-14 27424 239.66
30-AUG-14 23930 209.12
31-AUG-14 23271 203.36
01-SEP-14 19961 174.44
02-SEP-14 28831 251.95
03-SEP-14 32631 285.16
04-SEP-14 38360 335.23
05-SEP-14 42319 369.82
06-SEP-14 40520 354.1
07-SEP-14 30134 263.34
08-SEP-14 30360 265.32
09-SEP-14 34560 302.02
10-SEP-14 35910 313.82
11-SEP-14 36439 318.44
12-SEP-14 39540 345.54
13-SEP-14 43205 377.57
14-SEP-14 28122 245.76
15-SEP-14 29454 257.4
16-SEP-14 36092 315.41
17-SEP-14 35531 310.5
18-SEP-14 40274 351.95
19-SEP-14 41017 358.45
20-SEP-14 38864 339.63
21-SEP-14 28620 250.11
22-SEP-14 28312 247.42
23-SEP-14 30316 264.93
24-SEP-14 31301 273.54
25-SEP-14 38203 333.85
26-SEP-14 37504 327.75
27-SEP-14 39468 344.91
28-SEP-14 29656 259.16
29-SEP-14 29201 255.19
30-SEP-14 33431 292.15

92 rows selected.

TRIP_DATE	TRIP_COUNT	PCT_COMPARISON
01-JUL-14	21228	185.51
02-JUL-14	26480	231.41
03-JUL-14	21597	188.74
04-JUL-14	14148	123.64
05-JUL-14	10890	95.17
06-JUL-14	11443	100
07-JUL-14	18280	159.75
08-JUL-14	25763	225.14
09-JUL-14	27817	243.09
10-JUL-14	30541	266.9
11-JUL-14	28752	251.26
12-JUL-14	25936	226.65
13-JUL-14	21082	184.23
14-JUL-14	27350	239.01
15-JUL-14	33845	295.77
16-JUL-14	28607	250
17-JUL-14	30710	268.37
18-JUL-14	29860	260.95
19-JUL-14	25726	224.82
20-JUL-14	21212	185.37
21-JUL-14	23578	206.05
22-JUL-14	29029	253.68
23-JUL-14	34073	297.76
24-JUL-14	32050	280.08
25-JUL-14	29975	261.95
26-JUL-14	27708	242.14
27-JUL-14	22590	197.41
28-JUL-14	23981	209.57
29-JUL-14	27589	241.1
30-JUL-14	30740	268.64
31-JUL-14	33541	293.11
01-AUG-14	32353	282.73
02-AUG-14	28678	250.62
03-AUG-14	23146	202.27
04-AUG-14	24952	218.05
05-AUG-14	28094	245.51
06-AUG-14	30495	266.49
07-AUG-14	32759	286.28
08-AUG-14	30411	265.76
09-AUG-14	26286	229.71
10-AUG-14	20584	179.88
11-AUG-14	22632	197.78
12-AUG-14	29646	259.08
13-AUG-14	29788	260.32
14-AUG-14	30457	266.16
15-AUG-14	28974	253.2
16-AUG-14	26234	229.26
17-AUG-14	21209	185.34
18-AUG-14	21956	191.87
19-AUG-14	24834	217.02
20-AUG-14	27955	244.3
21-AUG-14	32304	282.3
22-AUG-14	29512	257.9
23-AUG-14	27097	236.8
24-AUG-14	22036	192.57
25-AUG-14	22093	193.07
26-AUG-14	24550	214.54
27-AUG-14	27018	236.11
28-AUG-14	28597	249.91
29-AUG-14	27424	239.66
30-AUG-14	23930	209.12
31-AUG-14	23271	203.36
01-SEP-14	19961	174.44
02-SEP-14	28831	251.95
03-SEP-14	32631	285.16
04-SEP-14	38360	335.23
05-SEP-14	42319	369.82
06-SEP-14	40520	354.1
07-SEP-14	30134	263.34
08-SEP-14	30360	265.32
09-SEP-14	34560	302.02
10-SEP-14	35910	313.82
11-SEP-14	36439	318.44
12-SEP-14	39540	345.54
13-SEP-14	43205	377.57
14-SEP-14	28122	245.76
15-SEP-14	29454	257.4
16-SEP-14	36092	315.41
17-SEP-14	35531	310.5
18-SEP-14	40274	351.95
19-SEP-14	41017	358.45
20-SEP-14	38864	339.63
21-SEP-14	28620	250.11
22-SEP-14	28312	247.42
23-SEP-14	30316	264.93
24-SEP-14	31301	273.54
25-SEP-14	38203	333.85
26-SEP-14	37504	327.75
27-SEP-14	39468	344.91
28-SEP-14	29656	259.16
29-SEP-14	29201	255.19
30-SEP-14	33431	292.15

Statement 99

select 
  trip_date,
  trip_count, 
    nth_value(trip_count,2 from last) over (
       order by trip_count
       range between unbounded preceding and unbounded following ) as hi_2
from trips
order by 1

 
TRIP_DATE TRIP_COUNT HI_2
01-JUL-14 21228 42319
02-JUL-14 26480 42319
03-JUL-14 21597 42319
04-JUL-14 14148 42319
05-JUL-14 10890 42319
06-JUL-14 11443 42319
07-JUL-14 18280 42319
08-JUL-14 25763 42319
09-JUL-14 27817 42319
10-JUL-14 30541 42319
11-JUL-14 28752 42319
12-JUL-14 25936 42319
13-JUL-14 21082 42319
14-JUL-14 27350 42319
15-JUL-14 33845 42319
16-JUL-14 28607 42319
17-JUL-14 30710 42319
18-JUL-14 29860 42319
19-JUL-14 25726 42319
20-JUL-14 21212 42319
21-JUL-14 23578 42319
22-JUL-14 29029 42319
23-JUL-14 34073 42319
24-JUL-14 32050 42319
25-JUL-14 29975 42319
26-JUL-14 27708 42319
27-JUL-14 22590 42319
28-JUL-14 23981 42319
29-JUL-14 27589 42319
30-JUL-14 30740 42319
31-JUL-14 33541 42319
01-AUG-14 32353 42319
02-AUG-14 28678 42319
03-AUG-14 23146 42319
04-AUG-14 24952 42319
05-AUG-14 28094 42319
06-AUG-14 30495 42319
07-AUG-14 32759 42319
08-AUG-14 30411 42319
09-AUG-14 26286 42319
10-AUG-14 20584 42319
11-AUG-14 22632 42319
12-AUG-14 29646 42319
13-AUG-14 29788 42319
14-AUG-14 30457 42319
15-AUG-14 28974 42319
16-AUG-14 26234 42319
17-AUG-14 21209 42319
18-AUG-14 21956 42319
19-AUG-14 24834 42319
20-AUG-14 27955 42319
21-AUG-14 32304 42319
22-AUG-14 29512 42319
23-AUG-14 27097 42319
24-AUG-14 22036 42319
25-AUG-14 22093 42319
26-AUG-14 24550 42319
27-AUG-14 27018 42319
28-AUG-14 28597 42319
29-AUG-14 27424 42319
30-AUG-14 23930 42319
31-AUG-14 23271 42319
01-SEP-14 19961 42319
02-SEP-14 28831 42319
03-SEP-14 32631 42319
04-SEP-14 38360 42319
05-SEP-14 42319 42319
06-SEP-14 40520 42319
07-SEP-14 30134 42319
08-SEP-14 30360 42319
09-SEP-14 34560 42319
10-SEP-14 35910 42319
11-SEP-14 36439 42319
12-SEP-14 39540 42319
13-SEP-14 43205 42319
14-SEP-14 28122 42319
15-SEP-14 29454 42319
16-SEP-14 36092 42319
17-SEP-14 35531 42319
18-SEP-14 40274 42319
19-SEP-14 41017 42319
20-SEP-14 38864 42319
21-SEP-14 28620 42319
22-SEP-14 28312 42319
23-SEP-14 30316 42319
24-SEP-14 31301 42319
25-SEP-14 38203 42319
26-SEP-14 37504 42319
27-SEP-14 39468 42319
28-SEP-14 29656 42319
29-SEP-14 29201 42319
30-SEP-14 33431 42319

92 rows selected.

TRIP_DATE	TRIP_COUNT	HI_2
01-JUL-14	21228	42319
02-JUL-14	26480	42319
03-JUL-14	21597	42319
04-JUL-14	14148	42319
05-JUL-14	10890	42319
06-JUL-14	11443	42319
07-JUL-14	18280	42319
08-JUL-14	25763	42319
09-JUL-14	27817	42319
10-JUL-14	30541	42319
11-JUL-14	28752	42319
12-JUL-14	25936	42319
13-JUL-14	21082	42319
14-JUL-14	27350	42319
15-JUL-14	33845	42319
16-JUL-14	28607	42319
17-JUL-14	30710	42319
18-JUL-14	29860	42319
19-JUL-14	25726	42319
20-JUL-14	21212	42319
21-JUL-14	23578	42319
22-JUL-14	29029	42319
23-JUL-14	34073	42319
24-JUL-14	32050	42319
25-JUL-14	29975	42319
26-JUL-14	27708	42319
27-JUL-14	22590	42319
28-JUL-14	23981	42319
29-JUL-14	27589	42319
30-JUL-14	30740	42319
31-JUL-14	33541	42319
01-AUG-14	32353	42319
02-AUG-14	28678	42319
03-AUG-14	23146	42319
04-AUG-14	24952	42319
05-AUG-14	28094	42319
06-AUG-14	30495	42319
07-AUG-14	32759	42319
08-AUG-14	30411	42319
09-AUG-14	26286	42319
10-AUG-14	20584	42319
11-AUG-14	22632	42319
12-AUG-14	29646	42319
13-AUG-14	29788	42319
14-AUG-14	30457	42319
15-AUG-14	28974	42319
16-AUG-14	26234	42319
17-AUG-14	21209	42319
18-AUG-14	21956	42319
19-AUG-14	24834	42319
20-AUG-14	27955	42319
21-AUG-14	32304	42319
22-AUG-14	29512	42319
23-AUG-14	27097	42319
24-AUG-14	22036	42319
25-AUG-14	22093	42319
26-AUG-14	24550	42319
27-AUG-14	27018	42319
28-AUG-14	28597	42319
29-AUG-14	27424	42319
30-AUG-14	23930	42319
31-AUG-14	23271	42319
01-SEP-14	19961	42319
02-SEP-14	28831	42319
03-SEP-14	32631	42319
04-SEP-14	38360	42319
05-SEP-14	42319	42319
06-SEP-14	40520	42319
07-SEP-14	30134	42319
08-SEP-14	30360	42319
09-SEP-14	34560	42319
10-SEP-14	35910	42319
11-SEP-14	36439	42319
12-SEP-14	39540	42319
13-SEP-14	43205	42319
14-SEP-14	28122	42319
15-SEP-14	29454	42319
16-SEP-14	36092	42319
17-SEP-14	35531	42319
18-SEP-14	40274	42319
19-SEP-14	41017	42319
20-SEP-14	38864	42319
21-SEP-14	28620	42319
22-SEP-14	28312	42319
23-SEP-14	30316	42319
24-SEP-14	31301	42319
25-SEP-14	38203	42319
26-SEP-14	37504	42319
27-SEP-14	39468	42319
28-SEP-14	29656	42319
29-SEP-14	29201	42319
30-SEP-14	33431	42319

Statement 100

drop table trips purge

Table dropped.

Statement 101

create table trips ( trip_date date, trip_count int )

Table created.

Statement 102

insert into trips values (date '2014-07-01',21228)

1 row(s) inserted.

Statement 103

insert into trips values (date '2014-07-02',26480)

1 row(s) inserted.

Statement 104

insert into trips values (date '2014-07-03',21597)

1 row(s) inserted.

Statement 105

insert into trips values (date '2014-07-04',14148)

1 row(s) inserted.

Statement 106

insert into trips values (date '2014-07-05',10890)

1 row(s) inserted.

Statement 107

insert into trips values (date '2014-07-06',11443)

1 row(s) inserted.

Statement 108

insert into trips values (date '2014-07-07',18280)

1 row(s) inserted.

Statement 109

insert into trips values (date '2014-07-08',25763)

1 row(s) inserted.

Statement 110

insert into trips values (date '2014-07-09',27817)

1 row(s) inserted.

Statement 111

insert into trips values (date '2014-07-10',30541)

1 row(s) inserted.

Statement 112

insert into trips values (date '2014-07-11',28752)

1 row(s) inserted.

Statement 113

insert into trips values (date '2014-07-12',25936)

1 row(s) inserted.

Statement 114

insert into trips values (date '2014-07-13',21082)

1 row(s) inserted.

Statement 115

insert into trips values (date '2014-07-14',27350)

1 row(s) inserted.

Statement 116

insert into trips values (date '2014-07-15',33845)

1 row(s) inserted.

Statement 117

insert into trips values (date '2014-07-16',28607)

1 row(s) inserted.

Statement 118

insert into trips values (date '2014-07-17',30710)

1 row(s) inserted.

Statement 119

insert into trips values (date '2014-07-18',29860)

1 row(s) inserted.

Statement 120

insert into trips values (date '2014-07-19',25726)

1 row(s) inserted.

Statement 121

insert into trips values (date '2014-07-20',21212)

1 row(s) inserted.

Statement 122

insert into trips values (date '2014-07-21',23578)

1 row(s) inserted.

Statement 123

insert into trips values (date '2014-07-22',29029)

1 row(s) inserted.

Statement 124

insert into trips values (date '2014-07-23',34073)

1 row(s) inserted.

Statement 125

insert into trips values (date '2014-07-24',32050)

1 row(s) inserted.

Statement 126

insert into trips values (date '2014-07-25',29975)

1 row(s) inserted.

Statement 127

insert into trips values (date '2014-07-26',27708)

1 row(s) inserted.

Statement 128

insert into trips values (date '2014-07-27',22590)

1 row(s) inserted.

Statement 129

insert into trips values (date '2014-07-28',23981)

1 row(s) inserted.

Statement 130

insert into trips values (date '2014-07-29',27589)

1 row(s) inserted.

Statement 131

insert into trips values (date '2014-07-30',30740)

1 row(s) inserted.

Statement 132

insert into trips values (date '2014-07-31',33541)

1 row(s) inserted.

Statement 133

insert into trips values (date '2014-08-01',32353)

1 row(s) inserted.

Statement 134

insert into trips values (date '2014-08-02',28678)

1 row(s) inserted.

Statement 135

insert into trips values (date '2014-08-03',23146)

1 row(s) inserted.

Statement 136

insert into trips values (date '2014-08-04',24952)

1 row(s) inserted.

Statement 137

insert into trips values (date '2014-08-05',28094)

1 row(s) inserted.

Statement 138

insert into trips values (date '2014-08-06',30495)

1 row(s) inserted.

Statement 139

insert into trips values (date '2014-08-07',32759)

1 row(s) inserted.

Statement 140

insert into trips values (date '2014-08-08',30411)

1 row(s) inserted.

Statement 141

insert into trips values (date '2014-08-09',26286)

1 row(s) inserted.

Statement 142

insert into trips values (date '2014-08-10',20584)

1 row(s) inserted.

Statement 143

insert into trips values (date '2014-08-11',22632)

1 row(s) inserted.

Statement 144

insert into trips values (date '2014-08-12',29646)

1 row(s) inserted.

Statement 145

insert into trips values (date '2014-08-13',29788)

1 row(s) inserted.

Statement 146

insert into trips values (date '2014-08-14',30457)

1 row(s) inserted.

Statement 147

insert into trips values (date '2014-08-15',28974)

1 row(s) inserted.

Statement 148

insert into trips values (date '2014-08-16',26234)

1 row(s) inserted.

Statement 149

insert into trips values (date '2014-08-17',21209)

1 row(s) inserted.

Statement 150

insert into trips values (date '2014-08-18',21956)

1 row(s) inserted.

Statement 151

insert into trips values (date '2014-08-19',24834)

1 row(s) inserted.

Statement 152

insert into trips values (date '2014-08-20',27955)

1 row(s) inserted.

Statement 153

insert into trips values (date '2014-08-21',32304)

1 row(s) inserted.

Statement 154

insert into trips values (date '2014-08-22',29512)

1 row(s) inserted.

Statement 155

insert into trips values (date '2014-08-23',27097)

1 row(s) inserted.

Statement 156

insert into trips values (date '2014-08-24',22036)

1 row(s) inserted.

Statement 157

insert into trips values (date '2014-08-25',22093)

1 row(s) inserted.

Statement 158

insert into trips values (date '2014-08-26',24550)

1 row(s) inserted.

Statement 159

insert into trips values (date '2014-08-27',27018)

1 row(s) inserted.

Statement 160

insert into trips values (date '2014-08-28',28597)

1 row(s) inserted.

Statement 161

insert into trips values (date '2014-08-29',27424)

1 row(s) inserted.

Statement 162

insert into trips values (date '2014-08-30',23930)

1 row(s) inserted.

Statement 163

insert into trips values (date '2014-08-31',23271)

1 row(s) inserted.

Statement 164

insert into trips values (date '2014-09-01',19961)

1 row(s) inserted.

Statement 165

insert into trips values (date '2014-09-02',28831)

1 row(s) inserted.

Statement 166

insert into trips values (date '2014-09-03',32631)

1 row(s) inserted.

Statement 167

insert into trips values (date '2014-09-04',38360)

1 row(s) inserted.

Statement 168

insert into trips values (date '2014-09-05',42319)

1 row(s) inserted.

Statement 169

insert into trips values (date '2014-09-06',40520)

1 row(s) inserted.

Statement 170

insert into trips values (date '2014-09-07',30134)

1 row(s) inserted.

Statement 171

insert into trips values (date '2014-09-08',30360)

1 row(s) inserted.

Statement 172

insert into trips values (date '2014-09-09',34560)

1 row(s) inserted.

Statement 173

insert into trips values (date '2014-09-10',35910)

1 row(s) inserted.

Statement 174

insert into trips values (date '2014-09-11',36439)

1 row(s) inserted.

Statement 175

insert into trips values (date '2014-09-12',39540)

1 row(s) inserted.

Statement 176

insert into trips values (date '2014-09-13',43205)

1 row(s) inserted.

Statement 177

insert into trips values (date '2014-09-14',28122)

1 row(s) inserted.

Statement 178

insert into trips values (date '2014-09-15',29454)

1 row(s) inserted.

Statement 179

insert into trips values (date '2014-09-16',36092)

1 row(s) inserted.

Statement 180

insert into trips values (date '2014-09-17',35531)

1 row(s) inserted.

Statement 181

insert into trips values (date '2014-09-18',40274)

1 row(s) inserted.

Statement 182

insert into trips values (date '2014-09-19',41017)

1 row(s) inserted.

Statement 183

insert into trips values (date '2014-09-20',38864)

1 row(s) inserted.

Statement 184

insert into trips values (date '2014-09-21',28620)

1 row(s) inserted.

Statement 185

insert into trips values (date '2014-09-22',28312)

1 row(s) inserted.

Statement 186

insert into trips values (date '2014-09-23',30316)

1 row(s) inserted.

Statement 187

insert into trips values (date '2014-09-24',31301)

1 row(s) inserted.

Statement 188

insert into trips values (date '2014-09-25',38203)

1 row(s) inserted.

Statement 189

insert into trips values (date '2014-09-26',37504)

1 row(s) inserted.

Statement 190

insert into trips values (date '2014-09-27',39468)

1 row(s) inserted.

Statement 191

insert into trips values (date '2014-09-28',29656)

1 row(s) inserted.

Statement 192

insert into trips values (date '2014-09-29',29201)

1 row(s) inserted.

Statement 193

insert into trips values (date '2014-09-30',33431)

1 row(s) inserted.

Statement 194

commit

Statement processed.

Statement 195

select * from trips

 
TRIP_DATE TRIP_COUNT
01-JUL-14 21228
02-JUL-14 26480
03-JUL-14 21597
04-JUL-14 14148
05-JUL-14 10890
06-JUL-14 11443
07-JUL-14 18280
08-JUL-14 25763
09-JUL-14 27817
10-JUL-14 30541
11-JUL-14 28752
12-JUL-14 25936
13-JUL-14 21082
14-JUL-14 27350
15-JUL-14 33845
16-JUL-14 28607
17-JUL-14 30710
18-JUL-14 29860
19-JUL-14 25726
20-JUL-14 21212
21-JUL-14 23578
22-JUL-14 29029
23-JUL-14 34073
24-JUL-14 32050
25-JUL-14 29975
26-JUL-14 27708
27-JUL-14 22590
28-JUL-14 23981
29-JUL-14 27589
30-JUL-14 30740
31-JUL-14 33541
01-AUG-14 32353
02-AUG-14 28678
03-AUG-14 23146
04-AUG-14 24952
05-AUG-14 28094
06-AUG-14 30495
07-AUG-14 32759
08-AUG-14 30411
09-AUG-14 26286
10-AUG-14 20584
11-AUG-14 22632
12-AUG-14 29646
13-AUG-14 29788
14-AUG-14 30457
15-AUG-14 28974
16-AUG-14 26234
17-AUG-14 21209
18-AUG-14 21956
19-AUG-14 24834
20-AUG-14 27955
21-AUG-14 32304
22-AUG-14 29512
23-AUG-14 27097
24-AUG-14 22036
25-AUG-14 22093
26-AUG-14 24550
27-AUG-14 27018
28-AUG-14 28597
29-AUG-14 27424
30-AUG-14 23930
31-AUG-14 23271
01-SEP-14 19961
02-SEP-14 28831
03-SEP-14 32631
04-SEP-14 38360
05-SEP-14 42319
06-SEP-14 40520
07-SEP-14 30134
08-SEP-14 30360
09-SEP-14 34560
10-SEP-14 35910
11-SEP-14 36439
12-SEP-14 39540
13-SEP-14 43205
14-SEP-14 28122
15-SEP-14 29454
16-SEP-14 36092
17-SEP-14 35531
18-SEP-14 40274
19-SEP-14 41017
20-SEP-14 38864
21-SEP-14 28620
22-SEP-14 28312
23-SEP-14 30316
24-SEP-14 31301
25-SEP-14 38203
26-SEP-14 37504
27-SEP-14 39468
28-SEP-14 29656
29-SEP-14 29201
30-SEP-14 33431

92 rows selected.

TRIP_DATE	TRIP_COUNT
01-JUL-14	21228
02-JUL-14	26480
03-JUL-14	21597
04-JUL-14	14148
05-JUL-14	10890
06-JUL-14	11443
07-JUL-14	18280
08-JUL-14	25763
09-JUL-14	27817
10-JUL-14	30541
11-JUL-14	28752
12-JUL-14	25936
13-JUL-14	21082
14-JUL-14	27350
15-JUL-14	33845
16-JUL-14	28607
17-JUL-14	30710
18-JUL-14	29860
19-JUL-14	25726
20-JUL-14	21212
21-JUL-14	23578
22-JUL-14	29029
23-JUL-14	34073
24-JUL-14	32050
25-JUL-14	29975
26-JUL-14	27708
27-JUL-14	22590
28-JUL-14	23981
29-JUL-14	27589
30-JUL-14	30740
31-JUL-14	33541
01-AUG-14	32353
02-AUG-14	28678
03-AUG-14	23146
04-AUG-14	24952
05-AUG-14	28094
06-AUG-14	30495
07-AUG-14	32759
08-AUG-14	30411
09-AUG-14	26286
10-AUG-14	20584
11-AUG-14	22632
12-AUG-14	29646
13-AUG-14	29788
14-AUG-14	30457
15-AUG-14	28974
16-AUG-14	26234
17-AUG-14	21209
18-AUG-14	21956
19-AUG-14	24834
20-AUG-14	27955
21-AUG-14	32304
22-AUG-14	29512
23-AUG-14	27097
24-AUG-14	22036
25-AUG-14	22093
26-AUG-14	24550
27-AUG-14	27018
28-AUG-14	28597
29-AUG-14	27424
30-AUG-14	23930
31-AUG-14	23271
01-SEP-14	19961
02-SEP-14	28831
03-SEP-14	32631
04-SEP-14	38360
05-SEP-14	42319
06-SEP-14	40520
07-SEP-14	30134
08-SEP-14	30360
09-SEP-14	34560
10-SEP-14	35910
11-SEP-14	36439
12-SEP-14	39540
13-SEP-14	43205
14-SEP-14	28122
15-SEP-14	29454
16-SEP-14	36092
17-SEP-14	35531
18-SEP-14	40274
19-SEP-14	41017
20-SEP-14	38864
21-SEP-14	28620
22-SEP-14	28312
23-SEP-14	30316
24-SEP-14	31301
25-SEP-14	38203
26-SEP-14	37504
27-SEP-14	39468
28-SEP-14	29656
29-SEP-14	29201
30-SEP-14	33431

Statement 196

select 
  trip_date,
  trip_count, 
    nth_value(trip_count,2) over (
       order by trip_count
       range between unbounded preceding and unbounded following ) as low_2
from trips
order by 1

 
TRIP_DATE TRIP_COUNT LOW_2
01-JUL-14 21228 11443
02-JUL-14 26480 11443
03-JUL-14 21597 11443
04-JUL-14 14148 11443
05-JUL-14 10890 11443
06-JUL-14 11443 11443
07-JUL-14 18280 11443
08-JUL-14 25763 11443
09-JUL-14 27817 11443
10-JUL-14 30541 11443
11-JUL-14 28752 11443
12-JUL-14 25936 11443
13-JUL-14 21082 11443
14-JUL-14 27350 11443
15-JUL-14 33845 11443
16-JUL-14 28607 11443
17-JUL-14 30710 11443
18-JUL-14 29860 11443
19-JUL-14 25726 11443
20-JUL-14 21212 11443
21-JUL-14 23578 11443
22-JUL-14 29029 11443
23-JUL-14 34073 11443
24-JUL-14 32050 11443
25-JUL-14 29975 11443
26-JUL-14 27708 11443
27-JUL-14 22590 11443
28-JUL-14 23981 11443
29-JUL-14 27589 11443
30-JUL-14 30740 11443
31-JUL-14 33541 11443
01-AUG-14 32353 11443
02-AUG-14 28678 11443
03-AUG-14 23146 11443
04-AUG-14 24952 11443
05-AUG-14 28094 11443
06-AUG-14 30495 11443
07-AUG-14 32759 11443
08-AUG-14 30411 11443
09-AUG-14 26286 11443
10-AUG-14 20584 11443
11-AUG-14 22632 11443
12-AUG-14 29646 11443
13-AUG-14 29788 11443
14-AUG-14 30457 11443
15-AUG-14 28974 11443
16-AUG-14 26234 11443
17-AUG-14 21209 11443
18-AUG-14 21956 11443
19-AUG-14 24834 11443
20-AUG-14 27955 11443
21-AUG-14 32304 11443
22-AUG-14 29512 11443
23-AUG-14 27097 11443
24-AUG-14 22036 11443
25-AUG-14 22093 11443
26-AUG-14 24550 11443
27-AUG-14 27018 11443
28-AUG-14 28597 11443
29-AUG-14 27424 11443
30-AUG-14 23930 11443
31-AUG-14 23271 11443
01-SEP-14 19961 11443
02-SEP-14 28831 11443
03-SEP-14 32631 11443
04-SEP-14 38360 11443
05-SEP-14 42319 11443
06-SEP-14 40520 11443
07-SEP-14 30134 11443
08-SEP-14 30360 11443
09-SEP-14 34560 11443
10-SEP-14 35910 11443
11-SEP-14 36439 11443
12-SEP-14 39540 11443
13-SEP-14 43205 11443
14-SEP-14 28122 11443
15-SEP-14 29454 11443
16-SEP-14 36092 11443
17-SEP-14 35531 11443
18-SEP-14 40274 11443
19-SEP-14 41017 11443
20-SEP-14 38864 11443
21-SEP-14 28620 11443
22-SEP-14 28312 11443
23-SEP-14 30316 11443
24-SEP-14 31301 11443
25-SEP-14 38203 11443
26-SEP-14 37504 11443
27-SEP-14 39468 11443
28-SEP-14 29656 11443
29-SEP-14 29201 11443
30-SEP-14 33431 11443

92 rows selected.

TRIP_DATE	TRIP_COUNT	LOW_2
01-JUL-14	21228	11443
02-JUL-14	26480	11443
03-JUL-14	21597	11443
04-JUL-14	14148	11443
05-JUL-14	10890	11443
06-JUL-14	11443	11443
07-JUL-14	18280	11443
08-JUL-14	25763	11443
09-JUL-14	27817	11443
10-JUL-14	30541	11443
11-JUL-14	28752	11443
12-JUL-14	25936	11443
13-JUL-14	21082	11443
14-JUL-14	27350	11443
15-JUL-14	33845	11443
16-JUL-14	28607	11443
17-JUL-14	30710	11443
18-JUL-14	29860	11443
19-JUL-14	25726	11443
20-JUL-14	21212	11443
21-JUL-14	23578	11443
22-JUL-14	29029	11443
23-JUL-14	34073	11443
24-JUL-14	32050	11443
25-JUL-14	29975	11443
26-JUL-14	27708	11443
27-JUL-14	22590	11443
28-JUL-14	23981	11443
29-JUL-14	27589	11443
30-JUL-14	30740	11443
31-JUL-14	33541	11443
01-AUG-14	32353	11443
02-AUG-14	28678	11443
03-AUG-14	23146	11443
04-AUG-14	24952	11443
05-AUG-14	28094	11443
06-AUG-14	30495	11443
07-AUG-14	32759	11443
08-AUG-14	30411	11443
09-AUG-14	26286	11443
10-AUG-14	20584	11443
11-AUG-14	22632	11443
12-AUG-14	29646	11443
13-AUG-14	29788	11443
14-AUG-14	30457	11443
15-AUG-14	28974	11443
16-AUG-14	26234	11443
17-AUG-14	21209	11443
18-AUG-14	21956	11443
19-AUG-14	24834	11443
20-AUG-14	27955	11443
21-AUG-14	32304	11443
22-AUG-14	29512	11443
23-AUG-14	27097	11443
24-AUG-14	22036	11443
25-AUG-14	22093	11443
26-AUG-14	24550	11443
27-AUG-14	27018	11443
28-AUG-14	28597	11443
29-AUG-14	27424	11443
30-AUG-14	23930	11443
31-AUG-14	23271	11443
01-SEP-14	19961	11443
02-SEP-14	28831	11443
03-SEP-14	32631	11443
04-SEP-14	38360	11443
05-SEP-14	42319	11443
06-SEP-14	40520	11443
07-SEP-14	30134	11443
08-SEP-14	30360	11443
09-SEP-14	34560	11443
10-SEP-14	35910	11443
11-SEP-14	36439	11443
12-SEP-14	39540	11443
13-SEP-14	43205	11443
14-SEP-14	28122	11443
15-SEP-14	29454	11443
16-SEP-14	36092	11443
17-SEP-14	35531	11443
18-SEP-14	40274	11443
19-SEP-14	41017	11443
20-SEP-14	38864	11443
21-SEP-14	28620	11443
22-SEP-14	28312	11443
23-SEP-14	30316	11443
24-SEP-14	31301	11443
25-SEP-14	38203	11443
26-SEP-14	37504	11443
27-SEP-14	39468	11443
28-SEP-14	29656	11443
29-SEP-14	29201	11443
30-SEP-14	33431	11443

Statement 197

select 
  trip_date,
  trip_count, 
  round(100*
    trip_count /
    nth_value(trip_count,2) over (
       order by trip_count
       range between unbounded preceding and unbounded following )
    ,2) as pct_comparison
from trips
order by 1

 
TRIP_DATE TRIP_COUNT PCT_COMPARISON
01-JUL-14 21228 185.51
02-JUL-14 26480 231.41
03-JUL-14 21597 188.74
04-JUL-14 14148 123.64
05-JUL-14 10890 95.17
06-JUL-14 11443 100
07-JUL-14 18280 159.75
08-JUL-14 25763 225.14
09-JUL-14 27817 243.09
10-JUL-14 30541 266.9
11-JUL-14 28752 251.26
12-JUL-14 25936 226.65
13-JUL-14 21082 184.23
14-JUL-14 27350 239.01
15-JUL-14 33845 295.77
16-JUL-14 28607 250
17-JUL-14 30710 268.37
18-JUL-14 29860 260.95
19-JUL-14 25726 224.82
20-JUL-14 21212 185.37
21-JUL-14 23578 206.05
22-JUL-14 29029 253.68
23-JUL-14 34073 297.76
24-JUL-14 32050 280.08
25-JUL-14 29975 261.95
26-JUL-14 27708 242.14
27-JUL-14 22590 197.41
28-JUL-14 23981 209.57
29-JUL-14 27589 241.1
30-JUL-14 30740 268.64
31-JUL-14 33541 293.11
01-AUG-14 32353 282.73
02-AUG-14 28678 250.62
03-AUG-14 23146 202.27
04-AUG-14 24952 218.05
05-AUG-14 28094 245.51
06-AUG-14 30495 266.49
07-AUG-14 32759 286.28
08-AUG-14 30411 265.76
09-AUG-14 26286 229.71
10-AUG-14 20584 179.88
11-AUG-14 22632 197.78
12-AUG-14 29646 259.08
13-AUG-14 29788 260.32
14-AUG-14 30457 266.16
15-AUG-14 28974 253.2
16-AUG-14 26234 229.26
17-AUG-14 21209 185.34
18-AUG-14 21956 191.87
19-AUG-14 24834 217.02
20-AUG-14 27955 244.3
21-AUG-14 32304 282.3
22-AUG-14 29512 257.9
23-AUG-14 27097 236.8
24-AUG-14 22036 192.57
25-AUG-14 22093 193.07
26-AUG-14 24550 214.54
27-AUG-14 27018 236.11
28-AUG-14 28597 249.91
29-AUG-14 27424 239.66
30-AUG-14 23930 209.12
31-AUG-14 23271 203.36
01-SEP-14 19961 174.44
02-SEP-14 28831 251.95
03-SEP-14 32631 285.16
04-SEP-14 38360 335.23
05-SEP-14 42319 369.82
06-SEP-14 40520 354.1
07-SEP-14 30134 263.34
08-SEP-14 30360 265.32
09-SEP-14 34560 302.02
10-SEP-14 35910 313.82
11-SEP-14 36439 318.44
12-SEP-14 39540 345.54
13-SEP-14 43205 377.57
14-SEP-14 28122 245.76
15-SEP-14 29454 257.4
16-SEP-14 36092 315.41
17-SEP-14 35531 310.5
18-SEP-14 40274 351.95
19-SEP-14 41017 358.45
20-SEP-14 38864 339.63
21-SEP-14 28620 250.11
22-SEP-14 28312 247.42
23-SEP-14 30316 264.93
24-SEP-14 31301 273.54
25-SEP-14 38203 333.85
26-SEP-14 37504 327.75
27-SEP-14 39468 344.91
28-SEP-14 29656 259.16
29-SEP-14 29201 255.19
30-SEP-14 33431 292.15

92 rows selected.

TRIP_DATE	TRIP_COUNT	PCT_COMPARISON
01-JUL-14	21228	185.51
02-JUL-14	26480	231.41
03-JUL-14	21597	188.74
04-JUL-14	14148	123.64
05-JUL-14	10890	95.17
06-JUL-14	11443	100
07-JUL-14	18280	159.75
08-JUL-14	25763	225.14
09-JUL-14	27817	243.09
10-JUL-14	30541	266.9
11-JUL-14	28752	251.26
12-JUL-14	25936	226.65
13-JUL-14	21082	184.23
14-JUL-14	27350	239.01
15-JUL-14	33845	295.77
16-JUL-14	28607	250
17-JUL-14	30710	268.37
18-JUL-14	29860	260.95
19-JUL-14	25726	224.82
20-JUL-14	21212	185.37
21-JUL-14	23578	206.05
22-JUL-14	29029	253.68
23-JUL-14	34073	297.76
24-JUL-14	32050	280.08
25-JUL-14	29975	261.95
26-JUL-14	27708	242.14
27-JUL-14	22590	197.41
28-JUL-14	23981	209.57
29-JUL-14	27589	241.1
30-JUL-14	30740	268.64
31-JUL-14	33541	293.11
01-AUG-14	32353	282.73
02-AUG-14	28678	250.62
03-AUG-14	23146	202.27
04-AUG-14	24952	218.05
05-AUG-14	28094	245.51
06-AUG-14	30495	266.49
07-AUG-14	32759	286.28
08-AUG-14	30411	265.76
09-AUG-14	26286	229.71
10-AUG-14	20584	179.88
11-AUG-14	22632	197.78
12-AUG-14	29646	259.08
13-AUG-14	29788	260.32
14-AUG-14	30457	266.16
15-AUG-14	28974	253.2
16-AUG-14	26234	229.26
17-AUG-14	21209	185.34
18-AUG-14	21956	191.87
19-AUG-14	24834	217.02
20-AUG-14	27955	244.3
21-AUG-14	32304	282.3
22-AUG-14	29512	257.9
23-AUG-14	27097	236.8
24-AUG-14	22036	192.57
25-AUG-14	22093	193.07
26-AUG-14	24550	214.54
27-AUG-14	27018	236.11
28-AUG-14	28597	249.91
29-AUG-14	27424	239.66
30-AUG-14	23930	209.12
31-AUG-14	23271	203.36
01-SEP-14	19961	174.44
02-SEP-14	28831	251.95
03-SEP-14	32631	285.16
04-SEP-14	38360	335.23
05-SEP-14	42319	369.82
06-SEP-14	40520	354.1
07-SEP-14	30134	263.34
08-SEP-14	30360	265.32
09-SEP-14	34560	302.02
10-SEP-14	35910	313.82
11-SEP-14	36439	318.44
12-SEP-14	39540	345.54
13-SEP-14	43205	377.57
14-SEP-14	28122	245.76
15-SEP-14	29454	257.4
16-SEP-14	36092	315.41
17-SEP-14	35531	310.5
18-SEP-14	40274	351.95
19-SEP-14	41017	358.45
20-SEP-14	38864	339.63
21-SEP-14	28620	250.11
22-SEP-14	28312	247.42
23-SEP-14	30316	264.93
24-SEP-14	31301	273.54
25-SEP-14	38203	333.85
26-SEP-14	37504	327.75
27-SEP-14	39468	344.91
28-SEP-14	29656	259.16
29-SEP-14	29201	255.19
30-SEP-14	33431	292.15

Statement 198

select 
  trip_date,
  trip_count, 
    nth_value(trip_count,2 from last) over (
       order by trip_count
       range between unbounded preceding and unbounded following ) as hi_2
from trips
order by 1

 
TRIP_DATE TRIP_COUNT HI_2
01-JUL-14 21228 42319
02-JUL-14 26480 42319
03-JUL-14 21597 42319
04-JUL-14 14148 42319
05-JUL-14 10890 42319
06-JUL-14 11443 42319
07-JUL-14 18280 42319
08-JUL-14 25763 42319
09-JUL-14 27817 42319
10-JUL-14 30541 42319
11-JUL-14 28752 42319
12-JUL-14 25936 42319
13-JUL-14 21082 42319
14-JUL-14 27350 42319
15-JUL-14 33845 42319
16-JUL-14 28607 42319
17-JUL-14 30710 42319
18-JUL-14 29860 42319
19-JUL-14 25726 42319
20-JUL-14 21212 42319
21-JUL-14 23578 42319
22-JUL-14 29029 42319
23-JUL-14 34073 42319
24-JUL-14 32050 42319
25-JUL-14 29975 42319
26-JUL-14 27708 42319
27-JUL-14 22590 42319
28-JUL-14 23981 42319
29-JUL-14 27589 42319
30-JUL-14 30740 42319
31-JUL-14 33541 42319
01-AUG-14 32353 42319
02-AUG-14 28678 42319
03-AUG-14 23146 42319
04-AUG-14 24952 42319
05-AUG-14 28094 42319
06-AUG-14 30495 42319
07-AUG-14 32759 42319
08-AUG-14 30411 42319
09-AUG-14 26286 42319
10-AUG-14 20584 42319
11-AUG-14 22632 42319
12-AUG-14 29646 42319
13-AUG-14 29788 42319
14-AUG-14 30457 42319
15-AUG-14 28974 42319
16-AUG-14 26234 42319
17-AUG-14 21209 42319
18-AUG-14 21956 42319
19-AUG-14 24834 42319
20-AUG-14 27955 42319
21-AUG-14 32304 42319
22-AUG-14 29512 42319
23-AUG-14 27097 42319
24-AUG-14 22036 42319
25-AUG-14 22093 42319
26-AUG-14 24550 42319
27-AUG-14 27018 42319
28-AUG-14 28597 42319
29-AUG-14 27424 42319
30-AUG-14 23930 42319
31-AUG-14 23271 42319
01-SEP-14 19961 42319
02-SEP-14 28831 42319
03-SEP-14 32631 42319
04-SEP-14 38360 42319
05-SEP-14 42319 42319
06-SEP-14 40520 42319
07-SEP-14 30134 42319
08-SEP-14 30360 42319
09-SEP-14 34560 42319
10-SEP-14 35910 42319
11-SEP-14 36439 42319
12-SEP-14 39540 42319
13-SEP-14 43205 42319
14-SEP-14 28122 42319
15-SEP-14 29454 42319
16-SEP-14 36092 42319
17-SEP-14 35531 42319
18-SEP-14 40274 42319
19-SEP-14 41017 42319
20-SEP-14 38864 42319
21-SEP-14 28620 42319
22-SEP-14 28312 42319
23-SEP-14 30316 42319
24-SEP-14 31301 42319
25-SEP-14 38203 42319
26-SEP-14 37504 42319
27-SEP-14 39468 42319
28-SEP-14 29656 42319
29-SEP-14 29201 42319
30-SEP-14 33431 42319

92 rows selected.

Analytics - Windows and the NTH_VALUE function

Additional Information