join_to_union_all__conversion.sql

WITH FULL_SET AS (
   SELECT username, hit_count, NULL AS user_address, NULL AS user_mobile, NULL AS user_email_optin
   FROM web.user_activity_summary
   UNION ALL
   SELECT username, NULL, user_address, NULL, NULL
   FROM web.user_attributes_1
   WHERE ua_1.attribute_name = 'MAIN_ADDRESS'
   UNION ALL
   SELECT username, NULL, NULL, user_mobile, NULL
   FROM web.user_attributes_2
   UNION ALL
   SELECT username, NULL, NULL, NULL, user_email_optin
   FROM web.user_attributes_3
)

SELECT username
     --MIN, MAX, doesn't really matter
     , MAX(hit_count)        AS hit_count
     , MAX(user_address)     AS user_address
     , MAX(user_mobile)      AS user_mobile
     , MAX(user_email_optin) AS user_email_optin
FROM FULL_SET
GROUP BY username
HAVING hit_count NOT NULL -- this is your left join. Remove this clause for a FULL OUTER JOIN