im new to SQL, i wanted to ask:
I have combined multiple tables with CTE and join and resulting on this Image here.
From this table, I wanted to detect and count how many workers changed the category from the 1st or 2nd job.
For example, Jonathan Carey has 'Sales Lapangan' as his first job_category, and changed to 'other' on his 2nd job, i wanted to count this job_category change as one.
I tried Case when, and while but i'm getting more confused.
This is my syntax for the table i created:
with data_apply2 as(with data_apply as(with all_apply as(with job_id as(select job_category,
row_number() over(order by job_category) as job_id
from job_post
group by job_category)
select jp.*, job_id.job_id from job_post jp
join job_id
on job_id.job_category=jp.job_category)
select ja.worker_id, wk.name, ja.id as id_application, aa.job_category, aa.job_id
from job_post_application ja
join all_apply aa
on aa.id=ja.job_post_id
join workers wk
on wk.id = ja.worker_id
order by worker_id,ja.id)
select *,
row_number() over(partition by worker_id order by worker_id) as worker_num
from data_apply)
Thank You
You can group by worker and check the number of distinct job categories:
SELECT worker_id,
COUNT(DISTINCT job_category) > 1 category_change
FROM data_apply
GROUP BY worker_id;