postgresql query for getting all the values which pass through a state

Question

i have a table with 4 columns id primary key, created_at(date stamp), order_id, order_state_id

i want to make a query in which i have grouped by day for each day order_id, created_at where order_state_id could have only a list of value i made something like that

    select * 
    from marketplace_management.orders mmo 
    inner join (
      select max(created_at),order_id,date(created_at+ interval '2 hours') as zian 
      from marketplace_management.order_state_updates
      group by zian,order_id
      having max(order_state_id) <4
    ) stategr 
       on mmo.id =stategr.order_id 
      and date(mmo.created_at+ interval '2 hours')=stategr.zian 
      and mmo.created_at between '2022-03-17 22:00:00' AND '2022-03-18 22:00:00'

basically i want to get rows in which column order_State_id is 1, 2, or 3 for a day even that tomorrow the column get in another state

Answer 1

select *
from marketplace_management.orders mmo
inner join
   (select inter1.order_id,inter1.zian,states,
       case when position(',' in states)=0 then states
       else substring(states,0,position(',' in states))end as  "ult_stare"
    from
       (select mmosu.order_id,
         string_agg(cast(mmosu.order_state_id as varchar),','
        order by created_at desc) as states,
         date(mmosu.created_at+ interval '2 hours') as zian
        from marketplace_management.order_state_updates mmosu
        group by date(mmosu.created_at+ interval '2 hours'),mmosu.order_id)inter1
   ) laststate
   on mmo.id=laststate.order_id and                                                                     date(mmo.created_at+ interval '2 hours')=laststate.zian and
                                                                                        mmo.created_at between '2022-03-17 22:00:00' AND '2022-03-18 22:00:00' and
                                                                                        laststate.ult_stare in ('1','2','3')