UPDATE qna_table
SET
last_assigned_rater = rater_assign.user
FROM
(SELECT tmp.qna_id as id, tmp.user_ as user
  FROM (SELECT qna_id,  time, user_  FROM qna_state_change_table) tmp
  INNER JOIN
  (SELECT qna_id,  max(time) as last_timestamp FROM qna_state_change_table WHERE to_state_id = 3 OR to_state_id = 4 OR to_state_id = 7 GROUP BY qna_id) last_assigned_time
  ON tmp.time = last_assigned_time.last_timestamp AND tmp.qna_id = last_assigned_time.qna_id ) rater_assign
WHERE
qna_table.id = rater_assign.id;


UPDATE qna_table
SET
last_assigned_manager = manager_assign.user
FROM
(SELECT tmp.qna_id as id, tmp.user_ as user
  FROM (SELECT qna_id,  time, user_  FROM qna_state_change_table) tmp
  INNER JOIN
  (SELECT qna_id,  max(time) as last_timestamp FROM qna_state_change_table WHERE to_state_id = 5 OR to_state_id = 6 OR to_state_id = 8 GROUP BY qna_id) last_assigned_time
  ON tmp.time = last_assigned_time.last_timestamp AND tmp.qna_id = last_assigned_time.qna_id ) manager_assign
WHERE
qna_table.id = manager_assign.id;


UPDATE qna_table
SET
last_answered_ds = last_answer.answered_date
FROM
(SELECT tmp.qna_id as id, to_char(to_timestamp(tmp.time/1e9)::date, 'YYYY/MM/DD') as answered_date
  FROM (SELECT qna_id,  time, user_  FROM qna_state_change_table) tmp
  INNER JOIN
  (SELECT qna_id,  max(time) as last_timestamp FROM qna_state_change_table WHERE (from_state_id = 2 AND to_state_id = 4) OR (from_state_id = 3 AND to_state_id = 4) OR to_state_id = 7 GROUP BY qna_id) last_answered_time
  ON tmp.time = last_answered_time.last_timestamp AND tmp.qna_id = last_answered_time.qna_id ) last_answer
WHERE
qna_table.id = last_answer.id;

