SELECT t1.server_id,device_info,uid,gender
FROM
(SELECT server_id,device_info
FROM stat_meitu.mtxx_oda_all_user_active_info_new
WHERE date_p = 20191110
AND os_p IN ('ios','android')
AND last_date >= 20180101
GROUP BY server_id,device_info) t1

LEFT JOIN

(SELECT gid FROM
user_profile.uprofile_oda_install_applist
WHERE date_p = 20191108
AND (concat_ws(',',installed_applist) LIKE '%Tinder%'
        OR concat_ws(',',installed_applist) LIKE '%探探%'
        OR concat_ws(',',installed_applist) LIKE '%陌陌%'
        OR concat_ws(',',installed_applist) LIKE '%积目%'
        OR concat_ws(',',installed_applist) LIKE '%Soul%')
GROUP BY gid) t2
ON t1.server_id = t2.gid

LEFT JOIN

(SELECT type_value,age_stage_v2,gender,
address.VALUE AS city
FROM user_profile.user_profile_oda
lateral VIEW explode(resident_location_id) followTables AS address
WHERE date_p = 20191109
AND version_p = 'v1'
AND type_p = 'gid'
AND  address.id=1007
AND address.VALUE = 14606) t3 ON t1.server_id = t3.type_value

LEFT JOIN

(SELECT uid,gid
FROM
stat_meitu.mtxx_oda_sdk_uid_gid
WHERE date_p = 20191016
GROUP BY uid,gid
)t4 ON t1.server_id  = t4.gid

WHERE t2.gid IS NOT NULL AND t3.city IS NOT NULL
AND device_info IS NOT NULL AND device_info <> 0
AND LENGTH(device_info) > 1
AND t3.age_stage_v2 IN ('1','2')
GROUP BY t1.server_id,device_info,uid,gender