Query: hackernews_jobs_by_type

select
  case
    when title ilike '%designer%' then 'Designer'
    when title ~* 'cloud.*engineer' then 'Cloud Engineer'
    when title ~* 'data.*analyst' then 'Data Analyst'
    when title ~* 'data.*engineer' then 'Data Engineer'
    when title ~* 'front.*end' then 'Front-end Engineer'
    when title ~* 'full.*stack' then 'Full Stack Engineer'
    when title ~* 'machine.*learning'
    or title ~* '\sML' then 'Machine Learning Engineer'
    when title ~* 'software.*engineer' then 'Software Engineer'
    when title ~* 'support.*engineer' then 'Support Engineer'
    when title ~* 'web.*developer' then 'Web Developer'
    else 'Other'
  end as job_type,
  count(*)
from
  hackernews_job
group by
  job_type