By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
SELECT
customer_id
, 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
, MAX(submitted_on) AS latest_order
FROM (
select 1001 as customer_id, now() - interval '65 days' as submitted_on union all
select 1001 as customer_id, now() - interval '64 days' as submitted_on union all
select 1001 as customer_id, now() - interval '63 days' as submitted_on
) o
GROUP BY customer_id
union all
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 (
select 2001 as customer_id, now() - interval '65 days' as submitted_on union all
select 2001 as customer_id, now() - interval '64 days' as submitted_on union all
select 2001 as customer_id, now() - interval '63 days' as submitted_on
) o
GROUP BY customer_id
union all
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 (
select 3001 as customer_id, now() - interval '65 days' as submitted_on union all
select 3001 as customer_id, now() - interval '64 days' as submitted_on union all
select 3001 as customer_id, now() - interval '63 days' as submitted_on
| customer_id | reference_more_than_90d | reference_more_than_31d | reference_more_than_30d | latest_order |
|---|---|---|---|---|
| 1001 | 2022-04-18 06:52:51.357063+01 | 2022-06-17 06:52:51.357063+01 | 2022-06-18 06:52:51.357063+01 | 2022-05-16 06:52:51.357063+01 |
| 2001 | 2022-04-18 00:00:00+01 | 2022-06-17 00:00:00+01 | 2022-06-18 00:00:00+01 | 2022-05-16 06:52:51.357063+01 |
| 3001 | 2021-04-01 00:00:00+01 | 2021-05-31 00:00:00+01 | 2021-06-01 00:00:00+01 | 2022-05-16 06:52:51.357063+01 |