Overall Role Fit and Career Alignment Questions

-- Returns leads flagged as qualified per rules
SELECT l.lead_id,
       l.lead_source,
       l.company_employees,
       l.annual_revenue,
       GREATEST(l.last_activity_date, o.last_touch) AS last_touch,
       CASE
         WHEN l.opt_out = true THEN false
         WHEN l.lead_status IN ('Disqualified','Converted') THEN false
         WHEN l.lead_source NOT IN ('Website','Outbound') THEN false
         WHEN (l.company_employees >= 50 OR l.annual_revenue >= 1000000)
              AND (GREATEST(l.last_activity_date, o.last_touch) >= CURRENT_DATE - INTERVAL '14 days')
         OR (o.is_open = true) THEN true
         ELSE false
       END AS is_qualified
FROM leads l
LEFT JOIN (
  SELECT lead_id,
         MAX(activity_date) AS last_touch,
         MAX(CASE WHEN status = 'Open' THEN true ELSE false END) AS is_open
  FROM activities_or_opps
  GROUP BY lead_id
) o ON o.lead_id = l.lead_id
WHERE /* optionally filter active leads */ l.created_at <= CURRENT_DATE;