Connections

WITH ground AS (
  SELECT DISTINCT p.org AS org, t.type AS kind, t.tag AS what
  FROM person_tags t JOIN people p ON p.id = t.person
  WHERE t.type IN ('Industry', 'Technology', 'Idea')
    AND p.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
  UNION
  SELECT DISTINCT p.org, 'Topic', pt.topic
  FROM person_topics pt JOIN people p ON p.id = pt.person
  WHERE p.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
),
offers AS (
  SELECT DISTINCT p.org AS org, t.tag AS service
  FROM person_tags t JOIN people p ON p.id = t.person
  WHERE t.type = 'Service'
    AND p.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
),
founder_led AS (
  SELECT DISTINCT n.org AS org FROM nodes n
  WHERE n.type = 'Person' AND n.role_class = 'founder' AND n.org IS NOT NULL
    AND n.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
)
SELECT a.org AS org_a, b.org AS org_b, COUNT(*) AS shared,
       GROUP_CONCAT(a.kind || ':' || a.what, ' · ') AS on_what
FROM ground a JOIN ground b ON a.kind = b.kind AND a.what = b.what AND a.org < b.org
GROUP BY a.org, b.org
HAVING COUNT(*) >= 3
ORDER BY shared DESC, org_a, org_b
LIMIT 40;

WITH ground AS (
  SELECT DISTINCT p.org AS org, t.type AS kind, t.tag AS what
  FROM person_tags t JOIN people p ON p.id = t.person
  WHERE t.type IN ('Industry', 'Technology', 'Idea')
    AND p.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
  UNION
  SELECT DISTINCT p.org, 'Topic', pt.topic
  FROM person_topics pt JOIN people p ON p.id = pt.person
  WHERE p.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
),
offers AS (
  SELECT DISTINCT p.org AS org, t.tag AS service
  FROM person_tags t JOIN people p ON p.id = t.person
  WHERE t.type = 'Service'
    AND p.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
),
founder_led AS (
  SELECT DISTINCT n.org AS org FROM nodes n
  WHERE n.type = 'Person' AND n.role_class = 'founder' AND n.org IS NOT NULL
    AND n.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
)
SELECT o.service AS service,
       (SELECT GROUP_CONCAT(org, ' · ') FROM (SELECT DISTINCT org FROM offers x WHERE x.service = o.service ORDER BY org)) AS providers,
       (SELECT COUNT(*) FROM founder_led f WHERE f.org NOT IN (SELECT org FROM offers x WHERE x.service = o.service)) AS founder_led_without_it,
       (SELECT GROUP_CONCAT(org, ' · ') FROM (SELECT f.org FROM founder_led f
          WHERE f.org NOT IN (SELECT org FROM offers x WHERE x.service = o.service) ORDER BY f.org LIMIT 12)) AS for_example
FROM offers o
GROUP BY o.service
ORDER BY COUNT(DISTINCT o.org) DESC, service;

WITH ground AS (
  SELECT DISTINCT p.org AS org, t.type AS kind, t.tag AS what
  FROM person_tags t JOIN people p ON p.id = t.person
  WHERE t.type IN ('Industry', 'Technology', 'Idea')
    AND p.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
  UNION
  SELECT DISTINCT p.org, 'Topic', pt.topic
  FROM person_topics pt JOIN people p ON p.id = pt.person
  WHERE p.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
),
offers AS (
  SELECT DISTINCT p.org AS org, t.tag AS service
  FROM person_tags t JOIN people p ON p.id = t.person
  WHERE t.type = 'Service'
    AND p.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
),
founder_led AS (
  SELECT DISTINCT n.org AS org FROM nodes n
  WHERE n.type = 'Person' AND n.role_class = 'founder' AND n.org IS NOT NULL
    AND n.org NOT IN (SELECT name FROM orgs WHERE placeholder = 1)
)
SELECT o.org AS provider, o.service AS offer, f.org AS founder_led, COUNT(g2.what) AS shared,
       GROUP_CONCAT(g2.kind || ':' || g2.what, ' · ') AS on_what
FROM offers o
JOIN founder_led f ON f.org <> o.org
JOIN ground g1 ON g1.org = o.org
JOIN ground g2 ON g2.org = f.org AND g2.kind = g1.kind AND g2.what = g1.what
WHERE o.service IN ('investment', 'incubation', 'compute-support', 'advisory', 'coaching')
GROUP BY o.org, o.service, f.org
HAVING COUNT(g2.what) >= 2
ORDER BY shared DESC, provider, founder_led
LIMIT 40;