Arrive at start date and end date with in the same table with multiple orders across different user id's

Question

I have two tables one with unique id and a timestamp that tells when did a user first enter a store and the other with unique id time stamp and list of orders placed by each unique id with their ranks partitioned by the unique id and ordered by the orders placed to rank the orders placed.

Now i need an output where i will have the unique id order number and the rank with two new columns start time and end time where start time will be the first time in case the order rank is 1 and end time will be the time order is placed and for rank 2 the start time will be the time when first order is placed and end time will be the time when second order is placed and so on.

below is tables for reference

Table 1

Table 2

Final Table

Any inputs is much appreciated. Thanks in advance

Answer 1

You could do this with a self-join:

select 
    t2.id,
    t2.order_id,
    t2.order_rank,
    coalesce(t11.order_time, t2.order_time) start_time,
    t1.order_time end_tume
from table1 t1
inner join table2 t2 
    on t2.id = t1.id
left  join (select distinct order_time, order_rank, id from table1) t11 
    on t11.id = t1.id and t11.order_rank = t1.order_rank - 1

The left join attempts to bring the order time of the "previous" record that ahs the same id. If it is not available, we fall back on the date from table2.