Profile SQL

profile.sql

select
a.id as course_application_id, a.student_id, s.person_id, u.id user_id, u.email, p.name, pi.`nickname`, p.phone, a.status,
pi.birthdate_on, pi.gender, pi.has_disabilities, pi.`disabilities_description`, ad.`zip_code`, ad.neighborhood, ad.city, ad.state,
ps.linkedin_url, ps.facebook_url, c.`portfolio_url`,
c.years_of_experience, c.is_employed, 
c.expected_pay, c.last_pay, pi.`family_income`, 
c.experience_expectations, c.`experience_expectations_other`,
c.`entrepreneurship`, fd.`process_score`, fd.`system_score`, fd.`observations`,
fs.reached_xp_through, fs.`reached_xp_through_other`, fs.`reached_xp_through_alumni`,
(select count(id) from rp_higher_education where student_id = s.id and kind="graduation" and status="completed") as qt_graduation_completed,
(select count(id) from rp_higher_education where student_id = s.id and kind="graduation" and (status="in_course" or status = "interrupted")) as qt_graduation_incompleted,
(select count(id) from rp_higher_education where student_id = s.id and kind="postgraduation" and status="completed") as qt_postgraduation_completed,
(select count(id) from rp_higher_education where student_id = s.id and kind="postgraduation" and (status="in_course" or status = "interrupted")) as qt_postgraduation_incompleted, 
u.sign_in_count, f.pre_subscription_at, f.additional_information_complete_at, f.profile_complete_at, f.technical_exam_complete_at, f.feedback_complete_at,
(select score from rp_profile_question where student_id = s.id and profile_question_group_id = 1) as profile_g1,
(select score from rp_profile_question where student_id = s.id and profile_question_group_id = 2) as profile_g2,
(select score from rp_profile_question where student_id = s.id and profile_question_group_id = 3) as profile_g3,
(select score from rp_profile_question where student_id = s.id and profile_question_group_id = 4) as profile_g4,
(select level from rp_skill where student_id = s.id and skill_id = 1) as html,
(select level from rp_skill where student_id = s.id and skill_id = 2) as css,
(select level from rp_skill where student_id = s.id and skill_id = 3) as javascript,
(select level from rp_skill where student_id = s.id and skill_id = 4) as bootstrap,
(select level from rp_skill where student_id = s.id and skill_id = 5) as sass,
(select level from rp_skill where student_id = s.id and skill_id = 6) as less,
(select level from rp_skill where student_id = s.id and skill_id = 7) as angular,
(select level from rp_skill where student_id = s.id and skill_id = 8) as php,
(select level from rp_skill where student_id = s.id and skill_id = 9) as ruby,
(select level from rp_skill where student_id = s.id and skill_id = 10) as java,
(select level from rp_skill where student_id = s.id and skill_id = 11) as python,
(select level from rp_skill where student_id = s.id and skill_id = 12) as csharp,
(select level from rp_skill where student_id = s.id and skill_id = 13) as nodejs,
(select level from rp_skill where student_id = s.id and skill_id = 14) as objectivec,
(select level from rp_skill where student_id = s.id and skill_id = 15) as swift,
(select level from rp_skill where student_id = s.id and skill_id = 16) as android,
(select level from rp_skill where student_id = s.id and skill_id = 17) as ionic,
(select level from rp_skill where student_id = s.id and skill_id = 18) as xamarin,
(select level from rp_skill where student_id = s.id and skill_id = 19) as servidores_linux,
(select level from rp_skill where student_id = s.id and skill_id = 20) as servidores_windows,
(select level from rp_skill where student_id = s.id and skill_id = 21) as aws,
(select level from rp_skill where student_id = s.id and skill_id = 22) as azure,
(select level from rp_skill where student_id = s.id and skill_id = 23) as `sql`,
(select level from rp_skill where student_id = s.id and skill_id = 24) as nosql,
(select level from rp_skill where student_id = s.id and skill_id = 25) as git
from course_applications a
left join course_application_funnels f on a.id = f.course_application_id
left join students s on s.id = a.student_id
left join people p on p.id = s.person_id
left join person_personal_informations pi on pi.person_id = p.id
left join addresses ad on ad.addressable_id = p.id
left join person_social_networks_informations ps on ps.person_id = p.id
left join student_careers c on c.student_id = s.id
left join user_accounts u on u.person_id = p.id
left join funnels fs on fs.student_id = s.id
left join `course_application_feedbacks` fd on fd.`course_application_id` = a.id
/* left join `technical_exam_submissions` ts on ts.`evaluable_id` = a.id
left join `technical_exam_submission_answers` tsa on tsa.`technical_exam_submission_id` = ts.id
left join `technical_exam_submission_closed_answers` tsoa on tsoa. = tsa.id */
-- where a.status = 'approved' or a.status = 'enrolled'
order by a.id