soyouwantasqlquery.sql

SELECT
  bt.id AS user_id
  , start_date_join.value::date AS start_date
  , end_date_join.value::date AS end_date
  , coverage_level_join.value AS coverage_level
FROM ${baseTable} bt
LEFT OUTER JOIN ${dataTable} start_date_join
  ON start_date_join.user_id = bt.id
  AND start_date_join.field = 'start_date'
  AND NOT EXISTS (SELECT 1 FROM ${dataTable} field_after
                  WHERE field_after.field = 'start_date'
                    AND field_after.user_id = start_date_join.user_id
                    AND (field_after.effective_date > start_date_join.effective_date
                         OR (field_after.effective_date = start_date_join.effective_date
                             AND field_after.created_at > start_date_join.effective_date)
LEFT OUTER JOIN ${dataTable} end_date_join
  ON end_date_join.user_id = bt.id
  AND end_date_join.field = 'end_date'
  AND NOT EXISTS (SELECT 1 FROM ${dataTable} field_after
                  WHERE field_after.field = 'end_date'
                    AND field_after.user_id = end_date_join.user_id
                    AND (field_after.effective_date > end_date_join.effective_date
                         OR (field_after.effective_date = end_date_join.effective_date
                             AND field_after.created_at > end_date_join.effective_date)
LEFT OUTER JOIN ${dataTable} coverage_level_join
  ON coverage_level_join.user_id = bt.id
  AND coverage_level_join.field = 'coverage_level'
  AND NOT EXISTS (SELECT 1 FROM ${dataTable} field_after
                  WHERE field_after.field = 'coverage_level'
                    AND field_after.user_id = coverage_level_join.user_id
                    AND (field_after.effective_date > coverage_level_join.effective_date
                         OR (field_after.effective_date = coverage_level_join.effective_date
                             AND field_after.created_at > coverage_level_join.effective_date)