CASE WHEN function & date function to change now() > to an assumption date

WITH latest AS (
SELECT 
  DISTINCT customer_id,
  MAX(submitted_on) AS latest_order
FROM orders 
GROUP BY 1
),

AA AS (
SELECT 
  DISTINCT o.customer_id,
  latest.latest_order,
  now() - INTERVAL '91 days' AS reference_more_than_90D,
  now() - INTERVAL '31 days' AS reference_more_than_31D,
  now() - INTERVAL '30 days' AS reference_more_than_30D
FROM orders AS o
LEFT JOIN latest 
ON o.customer_id = latest.customer_id
GROUP BY 1,2,3,4,5
)

SELECT 
  DISTINCT o.customer_id,
  latest.latest_order,
  AA.reference_more_than_30D,
  AA.reference_more_than_31D,
  AA.reference_more_than_90D,
  CASE 
  WHEN latest.latest_order >= AA.reference_more_than_31D THEN 'r'
  WHEN latest.latest_order <= AA.reference_more_than_30D THEN 'a'
  ELSE 'l'
END AS status
FROM orders AS o
LEFT JOIN latest
ON  o.customer_id = latest.customer_id
LEFT JOIN AA
ON o.customer_id = AA.customer_id

With above this is the output

1. Seems that my CASE WHEN function isn't working right as 2021-04-29 is an older date than 2022-06-17/2022-06-16 and 2022-04-17 > The status should reflect 'l'
2. how should i change now() > to an assumption date like 2021-07-01 in this case ? Taking into consideration i only have 2021 & 2020 orders to look at

Thanks

I would suggest avoiding the use of now() as this includes millisecond precision. Instead you probably only need date precision so you could use current_date instead. Plus you really don't need select distinct at all, nor do you need multiple joins etc.

WITH AA
AS (
    SELECT customer_id
        , CURRENT_DATE - INTERVAL '91 days' AS reference_more_than_90D
        , CURRENT_DATE - INTERVAL '31 days' AS reference_more_than_31D
        , CURRENT_DATE - INTERVAL '30 days' AS reference_more_than_30D
        , MAX(submitted_on) AS latest_order
    FROM orders
    GROUP BY customer_id
    )
SELECT AA.customer_id
    , AA.latest_order
    , AA.reference_more_than_30D
    , AA.reference_more_than_31D
    , AA.reference_more_than_90D
    , CASE 
        WHEN AA.latest_order >= AA.reference_more_than_31D
            THEN 'r'
        WHEN AA.latest_order <= AA.reference_more_than_30D
            THEN 'a'
        ELSE 'l'
        END AS STATUS
FROM AA

If you ned to use a preset date instead of current_date then just use a date literal instead e.g.

SELECT 
       customer_id
     , '2021-07-01'::timestamp - INTERVAL '91 days' AS reference_more_than_90D
     , '2021-07-01'::timestamp - INTERVAL '31 days' AS reference_more_than_31D
     , '2021-07-01'::timestamp - INTERVAL '30 days' AS reference_more_than_30D
     , MAX(submitted_on) AS latest_order
    FROM orders
    GROUP BY customer_id
    )
SELECT AA.customer_id
    , AA.latest_order
    , AA.reference_more_than_30D
    , AA.reference_more_than_31D
    , AA.reference_more_than_90D
    , CASE 
        WHEN AA.latest_order >= AA.reference_more_than_31D
            THEN 'r'
        WHEN AA.latest_order <= AA.reference_more_than_30D
            THEN 'a'
        ELSE 'l'
        END AS STATUS
FROM AA

see this db<>fiddle here