postgresql - CTE Error - Error Code: 0, SQL State: 42P01 -

i'm attempting create cte displays total amount of interviews each churned customer has completed on specified period of time.

here i've come w/ far:

     chg_acct(acct_id, org_id, name, old_status, new_status, seq_desc, seq_asc, crm_id, cr, vertical) (         select a.id, o.id, o.name, s.old_value, s.new_value,         rank() on (partition a.id order s.created_at desc),         rank() on (partition a.id order s.created_at),         crm_id, s.created_at, o.vertical         accounts         join         organizations o on o.id = a.organization_id         join         slowly_changing_dimensions s         on s.resource_id = a.id , s.resource_table_name = 'accounts' , s.resource_attribute = 'status'         s.created_at::date between '2015-01-01' , '2016-01-25'         , o.name not 'zz%' , lower(o.name) not '%lino%'),          canceled_orgs (org_id) (         select a.org_id                 chg_acct         inner join chg_acct b on a.acct_id = b.acct_id         a.seq_desc = 1 , b.seq_asc = 1         , a.old_status <> b.new_status , b.new_status = 'canceled'),          completed_elements_interviews (org_id,total_completed_elements_interviews)  (         select          cj.organization_id org_id,         count (score) total_completed_elements_interviews,         cj.organization_id org_id           interview_documents           join candidate_jobs cj on i.candidate_job_id = cj.id         score not null         , interview_type_id = 4         group org_id),          completed_achievement_interviews (org_id,total_completed_achievements_interviews) (         select          cj.organization_id org_id,         count (a.is_completed) total_completed_achievements_interviews            achievement_screens           join candidate_jobs cj on a.candidate_job_id = cj.id         a.is_completed = 'true'         group org_id),          completed_phone_interviews (org_id,total_completed_phone_interviews) (         select          cj.organization_id org_id,         count (score) total_completed_phone_interviews,         cj.organization_id org_id           interview_documents           join candidate_jobs cj on i.candidate_job_id = cj.id         score not null         , interview_type_id = 1         group org_id)        select o.name, o.created_at, o.id,total_completed_phone_interviews,total_completed_achievements_interviews,       total_completed_element_interviews canceled_orgs  left join organizations o on completed_phone_interviews.org_id = o.id left join organizations o on completed_achievements_interviews.org_id = o.id left join organizations o on completed_elements_interviews.org_id = o.id group o.id, o.name, o.created_at 

currently, i'm receiving following error message:

[error code: 0, sql state: 42p01] error: missing from-clause entry table "completed_phone_interviews".

each temporary result set returns data appears i'm not joining correctly within select statement follows cte.

any appreciated.

your join clauses in main query off. try join table organizations 3 times using same alias against relations not yet defined in main query:

with (lots_of_ctes) select o.name, o.created_at, o.id, total_completed_phone_interviews,        total_completed_achievements_interviews, total_completed_element_interviews canceled_orgs  left join organizations o on completed_phone_interviews.org_id = o.id left join organizations o on completed_achievements_interviews.org_id = o.id left join organizations o on completed_elements_interviews.org_id = o.id group o.id, o.name, o.created_at;

instead, want:

with (lots_of_ctes) select o.name, o.created_at, o.id, p.total_completed_phone_interviews,        a.total_completed_achievements_interviews, e.total_completed_element_interviews canceled_orgs c left join organizations o on o.id = c.org_id left join completed_phone_interviews p on p.org_id = o.id left join completed_achievements_interviews on a.org_id = o.id left join completed_elements_interviews e on e.org_id = o.id group o.id, o.name, o.created_at;

otherwise, in first , third completed_... ctes duplicate org_id column.