postgresql - How to lookup id values in groups of rows of the same dataset using analytic window SQL function -

--dataset name: jobs week   date    job_id ---------------------- wk1    01/15   300 wk1    01/15   301 wk1    01/15   302 wk2    01/22   300  wk2    01/22   302 wk2    01/22   303 wk2    01/22   304 wk3    01/29   302 wk3    01/29   304 wk3    01/29   305 

i have dataset above. want create 3 additional columns namely:

is_job_id_present_in_wk1

is_job_id_present_in_wk2

is_job_id_present_in_wk3

i want write sql query marks each row 1 or 0 each of 3 new columns. don't want use self join. make use of analytic window function.

for example, first row in given dataset, values is_job_id_present_in_wk1, is_job_id_present_in_wk2 , is_job_id_present_in_wk3 1 (because job_id 300 present in 3 weeks).

for second row in given dataset, values is_job_id_present_in_wk1 1, is_job_id_present_in_wk2 0 , is_job_id_present_in_wk3 0 (because job_id 301 present in week 1).

tried till now:

select week, date, job_id        , case when job_id =                              first_value(case when week='wk1' job_id else null end) over(order job_id rows between current row , current row)   1 else 0 end is_job_id_present_in_wk1  jobs; 

try:

select week, date, job_id,         max( case when week = 'wk1' 1 else 0 end )             on (partition  job_id) is_job_id_present_in_wk1,         max( case when week = 'wk2' 1 else 0 end )             on (partition job_id) is_job_id_present_in_wk2,         max( case when week = 'wk3' 1 else 0 end )             on (partition  job_id) is_job_id_present_in_wk2 jobs; 

try version:

select week, date, job_id        , case when exists( select 1 jobs job1                             job1.job_id = jobs.job_id , job1.week = 'wk1' )               1 else 0 end  is_job_id_present_in_wk1        , case when exists( select 1 jobs job1                             job1.job_id = jobs.job_id , job1.week = 'wk2' )               1 else 0 end  is_job_id_present_in_wk2        , case when exists( select 1 jobs job1                             job1.job_id = jobs.job_id , job1.week = 'wk3' )               1 else 0 end  is_job_id_present_in_wk3  jobs; 

because faster verion analytic functions, esppecially when create composite index on job_id + week columns.