Join the people and company feeds

CREATE OR REPLACE VIEW CURRENT_ROLES AS
SELECT
  p.serialized_data:forager_id::INTEGER   AS person_id,
  p.serialized_data:first_name::STRING    AS first_name,
  p.serialized_data:last_name::STRING     AS last_name,
  p.serialized_data:linkedin_url::STRING  AS linkedin_url,
  r.value:role_title::STRING              AS role_title,
  LOWER(r.value:organization_domain::STRING) AS organization_domain
FROM PERSON_FEED p,
     LATERAL FLATTEN(input => p.serialized_data:roles) r
WHERE r.value:end_date IS NULL
  AND r.value:organization_domain IS NOT NULL;

CREATE OR REPLACE VIEW PEOPLE_AT_COMPANIES AS
SELECT
  r.person_id,
  r.first_name,
  r.last_name,
  r.role_title,
  o.serialized_data:forager_id::INTEGER   AS organization_id,
  o.serialized_data:name::STRING          AS organization_name,
  o.serialized_data:industry::STRING      AS industry,
  o.serialized_data:employees_range::STRING AS employees_range
FROM CURRENT_ROLES r
JOIN ORGANIZATION_FEED o
  ON LOWER(o.serialized_data:domain::STRING) = r.organization_domain;

SELECT organization_name, COUNT(*) AS engineers
FROM PEOPLE_AT_COMPANIES
WHERE role_title ILIKE '%engineer%'
GROUP BY organization_name
ORDER BY engineers DESC
LIMIT 20;