My Table structure & data–

CREATE TABLE BRAND_OFFERS
(
    ID NUMBER(15,0)
    , NAME VARCHAR2(80 CHAR)
    , OPEN DATE
    , CLOSE DATE
    , ENROLLED DATE
);


-- expired offers
insert into BRAND_OFFERS values(1,  'Holiday1',  date'2024-03-20', date'2024-03-30', date'2024-03-22');
insert into BRAND_OFFERS values(2,  'Holiday2',  date'2024-03-20', date'2024-03-25', null);
insert into BRAND_OFFERS values(3,  'Holiday3',  date'2024-04-25', date'2024-04-30', date'2024-04-27');
insert into BRAND_OFFERS values(4,  'Holiday4',  date'2024-05-21', date'2024-05-26', date'2024-05-23');
insert into BRAND_OFFERS values(5,  'Holiday5',  date'2024-05-22', date'2024-05-25', null);

-- active offers
insert into BRAND_OFFERS values(6,  'Holiday6',  date'2024-05-15', date'2024-06-10', date'2024-05-26');
insert into BRAND_OFFERS values(7,  'Holiday7',  date'2024-05-20', date'2024-06-10', date'2024-05-21');
insert into BRAND_OFFERS values(8,  'Holiday8',  date'2024-05-20', date'2024-06-10', null);
insert into BRAND_OFFERS values(9,  'Holiday9',  date'2024-05-20', date'2024-06-10', null);
insert into BRAND_OFFERS values(10, 'Holiday10', date'2024-05-20', date'2024-06-10', date'2024-05-22');

-- future offers
insert into BRAND_OFFERS values(11, 'Holiday11', date'2024-05-30', date'2024-06-05', null);
insert into BRAND_OFFERS values(12, 'Holiday12', date'2024-06-01', date'2024-06-05', null);
insert into BRAND_OFFERS values(13, 'Holiday13', date'2024-06-01', date'2024-06-08', null);

Requirement– Basically, the Festival Offers are divided into below categories:

1. Offer Expired – Offer Close date < sysdate
2. Offer Active and Enrolled – sysdate is between Close & Open date, inclusive. Already Enrolled.
3. Offer Active – sysdate is between Close & Open date, inclusive. But no Enrollment.
4. Offer Future – Offer Open date > sysdate

I need to display the values from BRAND_OFFERS table, Ordered By as per above categories. First Expired, then Active, then Future offers.

Oracle Query to achieve this-

with initial_op as (
    select id, name,
        OPEN as open_date,
        CLOSE as closure_date,
        ENROLLED as enroll_date,
        case 
            when CLOSE < sysdate
                then 'Offer Expired'
            when OPEN > sysdate
                then 'Offer Future'
            when ENROLLED is not null
                then 'Offer Active and Enrolled'
            else 'Offer Active but yet to Enroll'
        end offer_status,
        
        case 
            when CLOSE < sysdate
                then 1
            when ENROLLED is not null
                then 2
            else 3
        end seq
    from BRAND_OFFERS ),
    
x as (select * from initial_op where seq = 1
        order by closure_date ),
y as (select * from initial_op where seq = 2
        order by enroll_date ),
z as (select * from initial_op where seq = 3
        order by open_date)

select * from x
    union all
select * from y    
    union all
select * from z;

This query gives me the desired output. The same query, instead of multiple with & union all, I tried with order by (multiple) case when... but not getting the correct order.

--not working
order by 
    case when CLOSE < sysdate then CLOSE
         when ENROLLED is not null then ENROLLED
         else OPEN
    end

Can the working query be optimized further?

fiddle