SQL example for self-join

join_table.sql

SELECT
  prev.investment_id,
  prev.from as prev_from,
  prev.to as prev_to,
  next.from as next_from,
  next.to as next_to,
  to_date(cast(prev.created_at as TEXT), 'YYYY-MM-DD') as prev_created_at,
  to_date(cast(next.created_at as TEXT), 'YYYY-MM-DD') as next_created_at
FROM investment_state_transitions as prev
INNER JOIN investment_state_transitions as next
ON
  prev.investment_id = next.investment_id
  AND
  prev.to = next.from
WHERE
  prev.to = 'confirmed_bid'
  AND
  date(prev.created_at) <= '2016-05-22'
  AND
  date(next.created_at) > '2016-05-22'
ORDER BY prev.investment_id ASC