Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


create table notifications (
id serial primary key,
payload jsonb,
created_at date
);

insert into notifications(payload, created_at) values
('{"customer": {"email": "foo@example.com", "externalId": 111 }}', '2022-06-21'),
('{"customer": {"email": "foo@example.com", "externalId": 222 }}', '2022-06-20'),
('{"customer": {"email": "bar@example.com", "externalId": 333 }}', '2022-06-20'),
('{"customer": {"email": "baz@example.com", "externalId": 444 }}', '2022-04-14'),
('{"customer": {"email": "baz@example.com", "externalId": 555 }}', '2022-04-12'),
('{"customer": {"email": "gna@example.com", "externalId": 666 }}', '2022-06-10'),
('{"customer": {"email": "gna@example.com", "externalId": 666 }}', '2022-06-11');
7 rows affected

with cte(email, ext_id_count, max_created_at) as (
select 
    distinct payload -> 'customer' -> 'email', 
    count(distinct payload -> 'customer' -> 'externalId'), 
    max(created_at)
from notifications
group by payload -> 'customer' -> 'email'
)
select email from cte 
where ext_id_count > 1 and max_created_at > current_date - interval '1 month'


email


"foo@example.com"