softwareheritage=> select count(*) from (select url as child, (select count(*) from origin_visit where origin=origin.id) as child_visits, encode(metadata, 'escape')::json->'parent'->'full_name' as parent, (select count(*) from origin_visit, origin as o where origin_visit.origin=o.id and o.url= concat('https://github.com/'::text, (encode(metadata, 'escape')::json->'parent'->>'full_name')::text)) as parent_visits from raw_extrinsic_metadata, origin where digest(url, 'sha1')=decode(substring(target, 11), 'hex') and target>'swh:1:ori:' and target < 'swh:1:orz' and format='application/vnd.github.v3+json' and encode(metadata, 'escape') not like '%\\3%' and encode(metadata, 'escape') not like '%\\\\%' and encode(metadata, 'escape')::json->'parent'->'full_name' is not null) as t where t.child_visits=1 and t.parent_visits>0;
^[[A^[[A
count
-------
10642
(1 row)
softwareheritage=> select count(*) from (select url as child, (select count(*) from origin_visit where origin=origin.id) as child_visits, encode(metadata, 'escape')::json->'parent'->'full_name' as parent, (select count(*) from origin_visit, origin as o where origin_visit.origin=o.id and o.url= concat('https://github.com/'::text, (encode(metadata, 'escape')::json->'parent'->>'full_name')::text)) as parent_visits from raw_extrinsic_metadata, origin where digest(url, 'sha1')=decode(substring(target, 11), 'hex') and target>'swh:1:ori:' and target < 'swh:1:orz' and format='application/vnd.github.v3+json' and encode(metadata, 'escape') not like '%\\3%' and encode(metadata, 'escape') not like '%\\\\%' and encode(metadata, 'escape')::json->'parent'->'full_name' is not null) as t;