Postgresql: Select first row in group by (with a twist) -

Postgresql: Select first row in group by (with a twist) -

i having great difficulty writing sql next problem:

i have next database columns in table "answers"

user_id integer, question_id integer, session_id text, crated_date timestamp, correct_answer boolean

now have list of sessions , count right , wrong first answers every question in session. each user may reply same question many times during 1 session, know how many questions answered correctly/incorrecly first time appeared in session. column created_date determines order of answers. result trying obtain should have next format:

session_id text, user_id integer, questions_answered_correctly_first_time_in_session integer, questions_answered_incorrectly_first_time_in_session integer, questions_answered_correctly_after_first_time_in_session integer, questions_answered_incorrectly_after_first_time_in_session integer

any help appreciated :)

i not 100% sure work, give go:

note, on fly constructed idea, have not @ looked @ performance, there might improve way.

with first_answers (select session_id, question_id, min(created_date) created_date, true first_answer answers grouping session_id, question_id) select first.session_id, first.user_id, sum(case when coalesce(first_answer, false) , correct_answer 1 else 0 end), sum(case when coalesce(first_answer, false) , not correct_answer 1 else 0 end), sum(case when not coalesce(first_answer, false) , correct_answer 1 else 0 end), sum(case when not coalesce(first_answer, false) , not correct_answer 1 else 0 end) answers left bring together first_answers using (session_id, user_id, created_date) grouping session_id

postgresql