sql - PostgreSQL query to group consecutive days of week -

i trying write complex query , beginner in postgresql.

here schema sample data , progress done far:

http://sqlfiddle.com/#!15/e8b13/35

in schema no column primary key.

my function takes argument course_no, execute query , returns json.

my query should following:

1. timeslots grouped isodow, start time , end time.
2. consecutive week days grouped showing first-last week day(like mon-tue or mon-fri)
3. json ordered start_timestamp min(start_timestamp) in first group in json.

example of course_no 47 timeslots on monday 11:00-11:45:

"[{"schedule":"mon 11:00-11:45","count_timeslots":47}]" 

a course_no 15 timeslots on tuesday 09:00-09:30 , 20 timeslots on thursday 10:00-10:45:

"[{"schedule":"tue 09:00-09:30","count_timeslots":15},{"schedule":"thu 10:00-10:45","count_timeslots":20}]" 

a course_no 46 timeslots on mon, tue, , wed 09:00-09:30 , 23 timeslots on wed 17:00-18:00.

notice "mon-wed..."

"[{"schedule":"mon-wed 09:00-09:45","count_timeslots":46},{"schedule":"wed 17:00-18:00","count_timeslots":23}]" 

i know how extract time timestamp , group start_time , end_time, have no idea how group consecutive days?

update: progress

i wrote query counts no of slots, group start_timestamp , end_timestamp , order min(start_timestamp).

i need group them isodow(days of week) when consecutive.

select count(*)  count_timeslot,  (extract(hour start_timestamp) || ':' ||  extract(minute start_timestamp)) start_time,      (extract(hour end_timestamp) || ':' || extract(minute end_timestamp))  end_time timeslot group start_time, end_time order min(start_timestamp);  

update 2: progress

almost completed query of window functions of postgresql.

1. first grouped them on basis start_time, end_time, day_of_week.

2. then created grp calculating day_of_week - row no() on partition of start_time gives me constant value when consecutive.

3. third calculated sum of count_timeslot on partition of start_time , grp.

my query

select *, sum(count_timeslot) on (partition start_time, grp)  n_count_time  (    select *, day_of_week - row_number()     on (partition start_time order day_of_week) grp     from(       select count(*) count_timeslot,       (extract(hour start_timestamp) || ':' || extract(minute       start_timestamp)) start_time,       (extract(hour end_timestamp) || ':' || extract(minute       end_timestamp)) end_time,       extract(isodow start_timestamp) day_of_week       timeslot group start_time, end_time, day_of_week       order min(start_timestamp)      )foo    )foo1; 

here solution:

with temp(k, v)  as(values('mon', 1), ('tue', 2), ('wed', 3), ('thu', 4), ('fri', 5), ('sat', 6), ('sun', 7))  select array_to_json(array_agg(row_to_json(foo4))) from(     select grouped || ' ' || start_time || '-' || end_time schedule     , n_count_time count_timeslots      from(         select *          , row_number() on (partition start_time, grp) row_no          from(             select *              , case              when count( * ) on (partition start_time, grp) > 1              first_value(name_of_day) on (partition start_time, grp)                   || '-' || last_value(name_of_day) on (partition start_time, grp)              else first_value(name_of_day) on (partition start_time, grp)              end grouped             , sum(count_timeslot) on (partition start_time, grp) n_count_time              from(                 select *                  , day_of_week - row_number() on (partition start_time order day_of_week) grp                 , k name_of_day from(select count( * ) count_timeslot                 , to_char(start_timestamp, 'hh24:mi') start_time                 , to_char(end_timestamp, 'hh24:mi') end_time                 , extract(isodow start_timestamp) day_of_week                  timeslot                  group start_time, end_time, day_of_week                  order min(start_timestamp)                 ) foo, temp v = day_of_week) foo1             ) foo2         ) foo3 row_no = 1     ) foo4; 

working fiddle: http://sqlfiddle.com/#!15/e8b13/47