with laser_gun_measurements as
( select time, distance -- 3 = 110, 2 = 75, 2.5 = 93, 3,5=124,4 = 145 4.5 = 168
  from   table( gun_measurement_tbl_t
                ( gun_measurement_t(0, 850.0)
                , gun_measurement_t(0.1, 846.8)
                , gun_measurement_t(0.2, 843.5)
                , gun_measurement_t(0.3, 840.0)
                , gun_measurement_t(0.5, 833.0)
                , gun_measurement_t(0.6, 829.5)
                , gun_measurement_t(0.7, 826.0)
                , gun_measurement_t(0.9, 818.6)
                , gun_measurement_t(1.1, 812.1)
                , gun_measurement_t(1.2, 808.8)
                , gun_measurement_t(1.3, 805.2)
                , gun_measurement_t(1.5, 797.8)
                , gun_measurement_t(1.6, 793.9)
                , gun_measurement_t(1.7, 790.3)
                , gun_measurement_t(1.9, 783.4)
                , gun_measurement_t(2.1, 776.1)
                , gun_measurement_t(2.2, 772.7)
                , gun_measurement_t(2.3, 769.1)
                , gun_measurement_t(2.4, 765.6)
                , gun_measurement_t(2.5, 762.1)
                , gun_measurement_t(2.6, 758.5)
                , gun_measurement_t(2.7, 755.0)
                , gun_measurement_t(2.8, 751.4)
                , gun_measurement_t(2.9, 747.8)
                , gun_measurement_t(3.0, 744.1)
                , gun_measurement_t(3.1, 740.4)
                , gun_measurement_t(3.2, 736.6)
                , gun_measurement_t(3.3, 732.9)
                , gun_measurement_t(3.4, 729.3)
                , gun_measurement_t(3.5, 725.6)
                , gun_measurement_t(3.6, 722.0)
                , gun_measurement_t(3.7, 718.4)
                , gun_measurement_t(3.8, 714.9)
                , gun_measurement_t(3.9, 711.5)
                , gun_measurement_t(4.0, 708.0)
                , gun_measurement_t(4.1, 704.6)
                , gun_measurement_t(4.2, 701.2)
                , gun_measurement_t(4.3, 697.8)
                , gun_measurement_t(4.4, 694.4)
                , gun_measurement_t(4.5, 691.3)
                )
              )
)              
, distances_covered as
( select time
  ,      time - lag(time) over (order by time) period
  ,      distance - lag(distance) over (order by time) delta_distance
  from   laser_gun_measurements
)
, speed_measurements as -- the query that drives the chart; one bar per groupid, one sub-stack per category
 ( select time 
  ,      abs(delta_distance/period * 3.6) speed
   ,      min(abs(delta_distance/period * 3.6)) over (order by time range between 0 preceding and 2 following) min_speed_in_window
  from   distances_covered
 )
select max(time) keep (dense_rank first order by min_speed_in_window desc) 
,      max(min_speed_in_window) keep (dense_rank first order by min_speed_in_window desc) maximum_two_sec_speed
from   speed_measurements

-- the scatterplot for measured distance:
with laser_gun_measurements as
( select time, distance -- 3 = 110, 2 = 75, 2.5 = 93, 3,5=124,4 = 145 4.5 = 168
  from   table( gun_measurement_tbl_t
                ( gun_measurement_t(0, 850.0)
                , gun_measurement_t(0.1, 846.8)
                , gun_measurement_t(0.2, 843.5)
                , gun_measurement_t(0.3, 840.0)
                , gun_measurement_t(0.5, 833.0)
                , gun_measurement_t(0.6, 829.5)
                , gun_measurement_t(0.7, 826.0)
                , gun_measurement_t(0.9, 818.6)
                , gun_measurement_t(1.1, 812.1)
                , gun_measurement_t(1.2, 808.8)
                , gun_measurement_t(1.3, 805.2)
                , gun_measurement_t(1.5, 797.8)
                , gun_measurement_t(1.6, 793.9)
                , gun_measurement_t(1.7, 790.3)
                , gun_measurement_t(1.9, 783.4)
                , gun_measurement_t(2.1, 776.1)
                , gun_measurement_t(2.2, 772.7)
                , gun_measurement_t(2.3, 769.1)
                , gun_measurement_t(2.4, 765.6)
                , gun_measurement_t(2.5, 762.1)
                , gun_measurement_t(2.6, 758.5)
                , gun_measurement_t(2.7, 755.0)
                , gun_measurement_t(2.8, 751.4)
                , gun_measurement_t(2.9, 747.8)
                , gun_measurement_t(3.0, 744.1)
                , gun_measurement_t(3.1, 740.4)
                , gun_measurement_t(3.2, 736.6)
                , gun_measurement_t(3.3, 732.9)
                , gun_measurement_t(3.4, 729.3)
                , gun_measurement_t(3.5, 725.6)
                , gun_measurement_t(3.6, 722.0)
                , gun_measurement_t(3.7, 718.4)
                , gun_measurement_t(3.8, 714.9)
                , gun_measurement_t(3.9, 711.5)
                , gun_measurement_t(4.0, 708.0)
                , gun_measurement_t(4.1, 704.6)
                , gun_measurement_t(4.2, 701.2)
                , gun_measurement_t(4.3, 697.8)
                , gun_measurement_t(4.4, 694.4)
                , gun_measurement_t(4.5, 691.3)
                )
              )
)              
, chart_query as -- the query that drives the chart; 
 ( select time x
  ,      distance y
  from   laser_gun_measurements
 )
, chart_dimensions as
  ( select 20 height 
    ,      100 width -- the width of the chart, can be changed
    ,      10 x_margin -- positions to the left of the y-axis
    ,      5 y_marker_distance
    ,      20 x_marker_distance
    ,      '*' marker
    from   dual
  )
 , limits as
 ( select min(y)*0.95 low_limit -- decide to use 0 as the baseline for the chartś y-axis or some other value
   ,      max(y)*1.05 high_limit
   ,      min(x) start_x
   ,      max(x) end_x
   from   chart_query
 )
, lines as
  ( select level line
    from   chart_dimensions
    connect by level <= height
  )
, y_markers as
( select line
  ,      (height-line)/height * (high_limit - low_limit) + low_limit marker_value
  from   lines cross join chart_dimensions cross join limits
  where  mod(height - line, y_marker_distance) = 0
)  
, columns as
  ( select level x
    from   chart_dimensions 
    connect by level <= width
  )
, x_axis as
( select x
  ,      case mod(width - x, x_marker_distance)
         when 0 then '|' else '_'
         end character
  ,      case 
         when mod(width-x , x_marker_distance)= 0 then substr( lpad(x/width * ( end_x- start_x) + start_x,4), 1,4)  
         when mod(width -x, x_marker_distance)< 4 then ''
         else ' '
         end marker_value
  from   columns cross join chart_dimensions cross join limits
)  
, x_axis_1 (axis, x) as
( select character axis
  ,      x
  from   x_axis
  where  x = 1
  union all
  select axis || character
  ,      x_axis.x
  from   x_axis_1 
         join   
         x_axis
         on (x_axis_1.x +1 = x_axis.x)
)
, x_axis_2 (axis, x) as
( select to_char(marker_value) axis
  ,      x
  from   x_axis
  where  x = 1
  union all
  select axis || marker_value
  ,      x_axis.x
  from   x_axis_2 
         join   
         x_axis
         on (x_axis_2.x +1 = x_axis.x)
)
, values_on_line as
(
select line
,      c.x
,      round((c.x - start_x)/(end_x - start_x) * width) col
from   lines l
       cross join limits   
       cross join chart_dimensions
       left outer join
       chart_query c
       on (c.y between (height-line-0.5)/height * (high_limit - low_limit) + low_limit 
                        and 
                        (height-line+0.5)/height * (high_limit - low_limit) + low_limit 
          )
)
, chart_lines (line, chart_line, col) as
( select line
  ,      '|'  axis
  ,      0
  from   lines
  union all
  select c.line
  ,      chart_line|| case when v.line is null then ' ' else marker end
  ,      c.col+1
  from   chart_lines c
       left outer join
       values_on_line v
       on (v.line = c.line and c.col+1 = v.col)
       cross join chart_dimensions
  where c.col < width
)
select line
,      substr(lpad(nvl(to_char(round(marker_value,1)),' '),25),26-x_margin,x_margin) ||chart_line y
from   lines
       left outer join
       y_markers
       using (line)
       join
       chart_lines
       using (line)
       cross join chart_dimensions       
       where chart_lines.col = width
union all
select height+1 line, lpad(' ',x_margin)||axis
from   x_axis_1 cross join chart_dimensions
where  x = width
union all
select height + 2 line,  lpad(' ',x_margin)||axis
from   x_axis_2 cross join chart_dimensions
where  x = width
order
by     line



-- the scatterplot for speed:

with laser_gun_measurements as
( select time, distance -- 3 = 110, 2 = 75, 2.5 = 93, 3,5=124,4 = 145 4.5 = 168
  from   table( gun_measurement_tbl_t
                ( gun_measurement_t(0, 850.0)
                , gun_measurement_t(0.1, 846.8)
                , gun_measurement_t(0.2, 843.5)
                , gun_measurement_t(0.3, 840.0)
                , gun_measurement_t(0.5, 833.0)
                , gun_measurement_t(0.6, 829.5)
                , gun_measurement_t(0.7, 826.0)
                , gun_measurement_t(0.9, 818.6)
                , gun_measurement_t(1.1, 812.1)
                , gun_measurement_t(1.2, 808.8)
                , gun_measurement_t(1.3, 805.2)
                , gun_measurement_t(1.5, 797.8)
                , gun_measurement_t(1.6, 793.9)
                , gun_measurement_t(1.7, 790.3)
                , gun_measurement_t(1.9, 783.4)
                , gun_measurement_t(2.1, 776.1)
                , gun_measurement_t(2.2, 772.7)
                , gun_measurement_t(2.3, 769.1)
                , gun_measurement_t(2.4, 765.6)
                , gun_measurement_t(2.5, 762.1)
                , gun_measurement_t(2.6, 758.5)
                , gun_measurement_t(2.7, 755.0)
                , gun_measurement_t(2.8, 751.4)
                , gun_measurement_t(2.9, 747.8)
                , gun_measurement_t(3.0, 744.1)
                , gun_measurement_t(3.1, 740.4)
                , gun_measurement_t(3.2, 736.6)
                , gun_measurement_t(3.3, 732.9)
                , gun_measurement_t(3.4, 729.3)
                , gun_measurement_t(3.5, 725.6)
                , gun_measurement_t(3.6, 722.0)
                , gun_measurement_t(3.7, 718.4)
                , gun_measurement_t(3.8, 714.9)
                , gun_measurement_t(3.9, 711.5)
                , gun_measurement_t(4.0, 708.0)
                , gun_measurement_t(4.1, 704.6)
                , gun_measurement_t(4.2, 701.2)
                , gun_measurement_t(4.3, 697.8)
                , gun_measurement_t(4.4, 694.4)
                , gun_measurement_t(4.5, 691.3)
                )
              )
)              
, distances_covered as
( select time
  ,      time - lag(time) over (order by time) period
  ,      distance - lag(distance) over (order by time) delta_distance
  from   laser_gun_measurements
)
, chart_query as -- the query that drives the chart; one bar per groupid, one sub-stack per category
 ( select time x
  ,      abs(delta_distance/period * 3.6) y
  from   distances_covered
 )
, chart_dimensions as
  ( select 20 height 
    ,      100 width -- the width of the chart, can be changed
    ,      10 x_margin -- positions to the left of the y-axis
    ,      5 y_marker_distance
    ,      20 x_marker_distance
    ,      '*' marker
    from   dual
  )
 , limits as
 ( select min(y)*0.8 low_limit -- decide to use 0 as the baseline for the chartś y-axis or some other value
   ,      max(y)*1.2 high_limit
   ,      min(x) start_x
   ,      max(x) end_x
   from   chart_query
 )
, lines as
  ( select level line
    from   chart_dimensions
    connect by level <= height
  )
, y_markers as
( select line
  ,      (height-line)/height * (high_limit - low_limit) + low_limit marker_value
  from   lines cross join chart_dimensions cross join limits
  where  mod(height - line, y_marker_distance) = 0
)  
, columns as
  ( select level x
    from   chart_dimensions 
    connect by level <= width
  )
, x_axis as
( select x
  ,      case mod(width - x, x_marker_distance)
         when 0 then '|' else '_'
         end character
  ,      case 
         when mod(width-x , x_marker_distance)= 0 then substr( lpad(x/width * ( end_x- start_x) + start_x,4), 1,4)  
         when mod(width -x, x_marker_distance)< 4 then ''
         else ' '
         end marker_value
  from   columns cross join chart_dimensions cross join limits
)  
, x_axis_1 (axis, x) as
( select character axis
  ,      x
  from   x_axis
  where  x = 1
  union all
  select axis || character
  ,      x_axis.x
  from   x_axis_1 
         join   
         x_axis
         on (x_axis_1.x +1 = x_axis.x)
)
, x_axis_2 (axis, x) as
( select to_char(marker_value) axis
  ,      x
  from   x_axis
  where  x = 1
  union all
  select axis || marker_value
  ,      x_axis.x
  from   x_axis_2 
         join   
         x_axis
         on (x_axis_2.x +1 = x_axis.x)
)
, values_on_line as
(
select line
,      c.x
,      round((c.x - start_x)/(end_x - start_x) * width) col
from   lines l
       cross join limits   
       cross join chart_dimensions
       left outer join
       chart_query c
       on (c.y between (height-line-0.5)/height * (high_limit - low_limit) + low_limit 
                        and 
                        (height-line+0.5)/height * (high_limit - low_limit) + low_limit 
          )
)
, chart_lines (line, chart_line, col) as
( select line
  ,      '|'  axis
  ,      0
  from   lines
  union all
  select c.line
  ,      chart_line|| case when v.line is null then ' ' else marker end
  ,      c.col+1
  from   chart_lines c
       left outer join
       values_on_line v
       on (v.line = c.line and c.col+1 = v.col)
       cross join chart_dimensions
  where c.col < width
)
select line
,      substr(lpad(nvl(to_char(round(marker_value,1)),' '),25),26-x_margin,x_margin) ||chart_line y
from   lines
       left outer join
       y_markers
       using (line)
       join
       chart_lines
       using (line)
       cross join chart_dimensions       
       where chart_lines.col = width
union all
select height+1 line, lpad(' ',x_margin)||axis
from   x_axis_1 cross join chart_dimensions
where  x = width
union all
select height + 2 line,  lpad(' ',x_margin)||axis
from   x_axis_2 cross join chart_dimensions
where  x = width
order
by     line