soasqlquerywantsyou.sql

SELECT DISTINCT
  bt.id AS user_id
  , (FIRST_VALUE(start_date_join.value) OVER
    (PARTITION BY start_date_join.user_id
     ORDER BY start_date_join.effective_date DESC,
              start_date_join.created_at DESC))::date AS start_date
  , (FIRST_VALUE(end_date_join.value) OVER
    (PARTITION BY end_date_join.user_id
     ORDER BY end_date_join.effective_date DESC,
              end_date_join.created_at DESC))::date AS end_date
  , (FIRST_VALUE(coverage_level_join.value) OVER
    (PARTITION BY coverage_level_join.user_id
     ORDER BY coverage_level_join.effective_date DESC,
              coverage_level_join.created_at DESC)) 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'
LEFT OUTER JOIN ${dataTable} end_date_join
  ON end_date_join.user_id = bt.id
  AND end_date_join.field = 'end_date'
LEFT OUTER JOIN ${dataTable} coverage_level_join
  ON coverage_level_join.user_id = bt.id
  AND coverage_level_join.field = 'coverage_level'