How to flag id's by a condition?-CodePudding

I want to create a column that flags an id it has a straight order process. i.e. id’s which don’t have order_status pending or info_required.

e.g. id a has pending, so is_straight will be false. b has no pending or info_required, so it should be true.

Here is the example data:

    WITH t1 AS (
SELECT 'a' AS id, 'created' AS status, '2021-11-02 15:04:07'::timestamp AS created_at UNION ALL
SELECT 'a' AS id, 'created' AS status, '2021-11-03 13:23:34'::timestamp AS created_at UNION ALL
SELECT 'a' AS id, 'pending' AS status, '2021-11-07 04:04:46'::timestamp AS created_at UNION ALL
SELECT 'a' AS id, 'successful' AS status, '2021-11-07 13:25:05'::timestamp AS created_at UNION ALL
SELECT 'b' AS id, 'created' AS status, '2021-11-11 16:19:07'::timestamp AS created_at UNION ALL
SELECT 'b' AS id, 'successful' AS status, '2021-11-13 17:57:55'::timestamp AS created_at UNION ALL
SELECT 'c' AS id, 'created' AS status, '2021-11-15 01:09:23'::timestamp AS created_at UNION ALL
SELECT 'c' AS id, 'info_required' AS status, '2021-11-17 11:06:00'::timestamp AS created_at UNION ALL
SELECT 'c' AS id, 'successful' AS status, '2021-11-21 23:35:46'::timestamp AS created_at
    )

CodePudding user response：

Using windowed