Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE domains (domain text, parent text);
INSERT INTO domains (domain, parent) VALUES
  ('stackoverflow.com', NULL)
, ('test.stackoverflow.com', 'stackoverflow.com')
, ('github.com', NULL)
, ('example.com', NULL)
, ('google.com', NULL)
;
CREATE TABLE events (name text, attributes jsonb, moment timestamp with time zone);
INSERT INTO events (name, attributes, moment) VALUES
  ('event1', '{"domain": "example.com"}', '2011-01-01')
, ('event1', '{"domain": "github.com"}', '2012-01-01')
, ('event1', '{"domain": "stackoverflow.com"}', '2013-01-01')
, ('event1', '{"domain": "example.com"}', '2014-01-01')
, ('event1', '{"domain": "stackoverflow.com"}', '2015-01-01')
;
5 rows affected

5 rows affected

SELECT d.domain, e.moment
FROM   domains d
LEFT   JOIN (
   SELECT DISTINCT ON (1)
          attributes ->> 'domain' AS domain, moment
   FROM   events
   WHERE  name = 'event1'
   ORDER  BY 1, moment
   ) e USING (domain)
WHERE  d.parent IS NULL
ORDER  BY e.moment NULLS FIRST, d.domain;


domain
moment


google.com
null

example.com
2011-01-01 00:00:00+00

github.com
2012-01-01 00:00:00+00

stackoverflow.com
2013-01-01 00:00:00+00


SELECT d.domain, e.moment
FROM   domains d
LEFT   JOIN (
   SELECT attributes ->> 'domain' AS domain, min(moment) AS moment
   FROM   events
   WHERE  name = 'event1'
   GROUP  BY 1
   ) e USING (domain)
WHERE  d.parent IS NULL
ORDER  BY e.moment NULLS FIRST, d.domain;


domain
moment


google.com
null

example.com
2011-01-01 00:00:00+00

github.com
2012-01-01 00:00:00+00

stackoverflow.com
2013-01-01 00:00:00+00


-- ???
SELECT *
FROM
(
SELECT *, row_number() OVER (PARTITION BY events.attributes->>'domain' ORDER BY moment ASC) rn FROM domains left join events on events.attributes ->> 'domain' = domains.domain AND events.name = 'event1' WHERE parent is null
) a
WHERE rn = 1
ORDER BY domain, moment asc nulls first


domain
parent
name
attributes
moment
rn


example.com
null
event1
{"domain": "example.com"}
2011-01-01 00:00:00+00
1

github.com
null
event1
{"domain": "github.com"}
2012-01-01 00:00:00+00
1

google.com
null
null
null
null
1

stackoverflow.com
null
event1
{"domain": "stackoverflow.com"}
2013-01-01 00:00:00+00
1

domain	moment
google.com	null
example.com	2011-01-01 00:00:00+00
github.com	2012-01-01 00:00:00+00
stackoverflow.com	2013-01-01 00:00:00+00

domain	parent	name	attributes	moment	rn
example.com	null	event1	{"domain": "example.com"}	2011-01-01 00:00:00+00	1
github.com	null	event1	{"domain": "github.com"}	2012-01-01 00:00:00+00	1
google.com	null	null	null	null	1
stackoverflow.com	null	event1	{"domain": "stackoverflow.com"}	2013-01-01 00:00:00+00	1