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

CodePudding user response：

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'.

I think you don't need to use those left joins. Please refer to the below script.

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

AA AS (
SELECT 
  DISTINCT customer_id,
  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 latest
GROUP BY 1,2,3,4,5
)

SELECT 
  DISTINCT customer_id,
  latest_order,
  reference_more_than_30D,
  reference_more_than_31D,
  reference_more_than_90D,
  CASE 
    WHEN latest_order >= reference_more_than_31D THEN 'r'
    WHEN latest_order <= reference_more_than_30D THEN 'a'
    ELSE 'l' END
  AS status
FROM AA

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.

\set variable

hank=> \set name hank
hank=> \set time '2018-02-06 10:09:00'   
hank=>  select * from tb2 where c2=:'name' and c3>=:'time';
 c1 |  c2  |             c3             
---- ------ ----------------------------
  1 | hank | 2018-02-06 10:08:00.78750

How can I set now() to '2021-07-01' ? . I need to make an assumption of the current date to be '2021-07-01'.

added

CREATE OR REPLACE FUNCTION get_data (v_Date TIMESTAMP WITHOUT TIME ZONE) 
 RETURNS TABLE (
 latest_order TIMESTAMP WITHOUT TIME ZONE,
 reference_more_than_30D TIMESTAMP WITHOUT TIME ZONE,
 reference_more_than_31D TIMESTAMP WITHOUT TIME ZONE,
 reference_more_than_90D TIMESTAMP WITHOUT TIME ZONE
) 
AS $$
BEGIN
 RETURN QUERY SELECT v_Date, 
       v_Date - INTERVAL '30 days',
       v_Date - INTERVAL '31 days',
       v_Date - INTERVAL '91 days';
END; 
$$ LANGUAGE plpgsql;

select * from get_data('2021-07-01');

CodePudding user response：

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