Ошибка 979: не выражение GROUP BY

Вам нужно поместить выбранные столбцы (в данном случае CDC.pat_id) в предложение ГРУППА ПО вместо col, в котором вы применяете функцию агрегирования.

SELECT
  CDC.pat_id,
  MAX(SOC.contact_date)
FROM
  hb61.cdcsaar_patients CDC
  JOIN clarity.social_hx SOC ON CDC.pat_id = SOC.pat_id
  LEFT OUTER JOIN clarity.smoking_cess_hx CESS ON CDC.pat_id = CESS.pat_id
  LEFT OUTER JOIN clarity.social_hx_alc_use ALC on CDC.pat_id = ALC.pat_id
GROUP BY
  CDC.pat_id;

Вам необходимо поместить любые столбцы, к которым вы нет применяете агрегатную функцию, в предложение GROUP BY (в данном случае CDC.pat_id, а не SOC.contact_date).