select usbl.user_session_billing_log_id,
       min(least(t4.item_billing_end_date_by_availability,
                 t4.item_billing_end_date_by_expiration_minutes,
                 t4.item_billing_end_date_by_total_minutes)) as expected_end_date
from (
       select usbl.user_session_billing_log_id,
              usbl.billing_date::date
                + (end_week_minute || ' minutes')::interval
                + (end_hour || ' hours')::interval
                + ((case
                      when (end_week_day - (date_part('isodow', usbl.billing_date) - 1)) < 0
                        then (end_week_day - (date_part('isodow', usbl.billing_date) - 1)) + 7
                end) || ' days')::interval                                         as item_billing_end_date_by_availability,
              usbl.billing_date + ((usbl.item_billing_total_minutes
                - usbl.item_billing_spent_minutes) ||
                                   ' minutes')::interval                           as item_billing_end_date_by_total_minutes,
              ib.activation_date + (ib.expiration_minutes || ' minutes')::interval as item_billing_end_date_by_expiration_minutes
       from (
              select user_session_billing_log_id,
                     start_week_day,
                     start_hour,
                     start_week_minute,
                     case
                       when end_week_day = 7 then 0
                       else end_week_day
                       end as end_week_day,
                     case
                       when end_week_day = 7 then 0
                       else end_hour
                       end as end_hour,
                     end_week_minute
              from (
                     select user_session_billing_log_id,
                            (s / 60) / 24 as start_week_day,
                            (s / 60) % 24 as start_hour,
                            s % 60        as start_week_minute,
                            (e / 60) / 24 as end_week_day,
                            (e / 60) % 24 as end_hour,
                            e % 60        as end_week_minute
                     from (
                            select min(s) as s, max(e) as e, user_session_billing_log_id
                            from (select s,
                                         e,
                                         max(new_start) over (order by s,e) as left_edge,
                                         user_session_billing_log_id
                                  from (select s,
                                               e,
                                               case when s <= max(le) over (order by s,e) then null else s end as new_start,
                                               user_session_billing_log_id
                                        from (
                                               select s,
                                                      e,
                                                      lag(e) over (order by s,e) as le,
                                                      user_session_billing_log_id
                                               from (
                                                      select p.week_day * 24 * 60 + p.start_hour * 60 + p.start_minute as s,
                                                             p.week_day * 24 * 60 + p.end_hour * 60 + p.end_minute     as e,
                                                             user_session_billing_log_id
                                                      from (
                                                             select avail_period.week_day,
                                                                    avail_period.start_hour,
                                                                    avail_period.start_minute,
                                                                    avail_period.end_hour,
                                                                    avail_period.end_minute,
                                                                    usbl.user_session_billing_log_id
                                                             from mv_user_session_billing_log usbl
                                                                    left join main_item_billing ib on ib.item_billing_id = usbl.item_billing_id
                                                                    left join main_item item on item.item_id = ib.item_id
                                                                    left join main_item_ticket ticket on item.item_ticket_id = ticket.id
                                                                    left join main_ticket_available_period avail_period
                                                                              on ticket.id = avail_period.itm_ticket_id
                                                           ) p
                                                    ) intervals
                                             ) s1
                                       ) s2
                                 ) s3
                            group by left_edge, user_session_billing_log_id
                          ) agg_periods
                   ) t
            ) t3
              join mv_user_session_billing_log usbl on usbl.user_session_billing_log_id = t3.user_session_billing_log_id
              join main_item_billing ib on ib.item_billing_id = usbl.item_billing_id
     ) t4
       right join mv_user_session_billing_log usbl on usbl.user_session_billing_log_id = t4.user_session_billing_log_id
group by usbl.user_session_billing_log_id
;