query-risk-user-features/sql/dataworks_single_user_180d_all_in_one.sql

-- DataWorks节点参数示例：
-- mid=weixin_openid_xxx
-- anchor_dt=20260810
-- window_start_dt=20260211
-- window_end_dt=20260809
-- 窗口为锚点前180个自然日，不包含锚点当天。
-- 注意：各指标还受源表实际分区保留期限制。2026-08-14检查时，
-- video_play_log最早分区为20260715，video_action_log_applet最早分区为20260416；
-- 如果window_start_dt早于源表最早分区，有效播放和分享只能统计现存分区。

WITH input_user AS (
  SELECT
    '${mid}' AS mid,
    '${anchor_dt}' AS anchor_dt,
    '${window_start_dt}' AS window_start_dt,
    '${window_end_dt}' AS window_end_dt
),
active_metrics AS (
  SELECT
    i.mid,
    COUNT(DISTINCT a.dt) AS active_day_cnt,
    CONCAT_WS(',', COLLECT_SET(CASE
      WHEN a.opengid IS NOT NULL AND a.opengid <> '' THEN a.opengid
    END)) AS source_opengid_list
  FROM input_user i
  LEFT JOIN loghubods.useractive_log a
    ON a.machinecode = i.mid
   AND a.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND a.businesstype = 'path'
  GROUP BY i.mid
),
real_play_metrics AS (
  SELECT i.mid, COUNT(p.mid) AS real_play_cnt
  FROM input_user i
  LEFT JOIN loghubods.video_play_log p
    ON p.mid = i.mid
   AND p.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND p.businesstype = 'videoRealPlay'
  GROUP BY i.mid
),
share_metrics AS (
  SELECT i.mid, COUNT(v.mid) AS video_share_cnt
  FROM input_user i
  LEFT JOIN loghubods.video_action_log_applet v
    ON v.mid = i.mid
   AND v.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND v.business = 'videoShareFriend'
   AND v.businesstype = 'videoShareFriend'
  GROUP BY i.mid
),
click_events AS (
  SELECT i.mid, s.clickobjectid, s.hotsencetype, s.shareid
  FROM input_user i
  JOIN loghubods.user_share_log s
    ON s.machinecode = i.mid
   AND s.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND s.topic = 'click'
),
click_metrics AS (
  SELECT
    mid,
    COUNT(DISTINCT CASE WHEN clickobjectid IS NOT NULL AND clickobjectid <> ''
      THEN clickobjectid END) AS card_click_object_uv,
    SUM(CASE WHEN clickobjectid IS NOT NULL AND clickobjectid <> ''
      THEN 1 ELSE 0 END) AS card_click_object_pv,
    SUM(CASE WHEN hotsencetype = '1007' THEN 1 ELSE 0 END) AS source_1007_cnt,
    SUM(CASE WHEN hotsencetype = '1008' THEN 1 ELSE 0 END) AS source_1008_cnt,
    SUM(CASE WHEN hotsencetype = '1058' THEN 1 ELSE 0 END) AS source_1058_cnt,
    SUM(CASE WHEN hotsencetype = '1074' THEN 1 ELSE 0 END) AS source_1074_cnt,
    SUM(CASE WHEN hotsencetype IN ('1067', '1095') THEN 1 ELSE 0 END)
      AS source_1067_1095_cnt,
    CONCAT_WS(',', COLLECT_SET(CASE WHEN shareid IS NOT NULL AND shareid <> ''
      THEN shareid END)) AS source_shareid_list
  FROM click_events
  GROUP BY mid
),
other_scene_counts AS (
  SELECT mid, hotsencetype, COUNT(*) AS scene_cnt
  FROM click_events
  WHERE hotsencetype IS NOT NULL AND hotsencetype <> ''
    AND hotsencetype NOT IN ('1007','1008','1058','1074','1067','1095')
  GROUP BY mid, hotsencetype
),
other_scene_metrics AS (
  SELECT
    mid,
    CONCAT_WS(',', COLLECT_SET(CONCAT(hotsencetype, ':', CAST(scene_cnt AS STRING))))
      AS other_scene_list
  FROM other_scene_counts
  GROUP BY mid
),
open_conv_pqtids AS (
  SELECT pqtid, MIN(dt) AS first_conv_dt
  FROM loghubods.ad_own_open_conv
  WHERE dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
    AND pqtid IS NOT NULL AND pqtid <> ''
  GROUP BY pqtid
),
ad_metrics AS (
  SELECT
    i.mid,
    COUNT(a.machinecode) AS own_ad_click_cnt,
    COUNT(DISTINCT CASE WHEN o.pqtid IS NOT NULL THEN a.pqtid END) AS long_press_scan_cnt
  FROM input_user i
  LEFT JOIN loghubods.ad_action_log_own a
    ON a.machinecode = i.mid
   AND a.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND a.businesstype = 'adClick'
   AND a.ownadsystemtype = 'ownPlatform'
  LEFT JOIN open_conv_pqtids o
    ON o.pqtid = a.pqtid
   AND o.first_conv_dt BETWEEN a.dt AND i.window_end_dt
  GROUP BY i.mid
),
source_share_events AS (
  SELECT
    i.mid,
    s.shareid,
    MIN(CAST(s.clienttimestamp AS BIGINT)) AS share_ts
  FROM input_user i
  JOIN loghubods.user_share_log s
    ON s.machinecode = i.mid
   AND s.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND s.topic = 'share'
   AND s.shareid IS NOT NULL AND s.shareid <> ''
   AND s.clienttimestamp RLIKE '^[0-9]+$'
  GROUP BY i.mid, s.shareid
),
return_click_events AS (
  SELECT
    c.shareid,
    c.machinecode AS return_mid,
    CAST(c.clienttimestamp AS BIGINT) AS click_ts
  FROM loghubods.user_share_log c
  JOIN (SELECT DISTINCT shareid FROM source_share_events) s
    ON c.shareid = s.shareid
  WHERE c.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
    AND c.topic = 'click'
    AND c.machinecode IS NOT NULL AND c.machinecode <> ''
    AND c.clienttimestamp RLIKE '^[0-9]+$'
),
return_metrics AS (
  SELECT
    i.mid,
    COUNT(DISTINCT s.shareid) AS window_shareid_cnt,
    COUNT(CASE WHEN c.click_ts >= s.share_ts AND c.return_mid <> s.mid
      THEN c.return_mid END) AS return_click_pv,
    COUNT(DISTINCT CASE WHEN c.click_ts >= s.share_ts AND c.return_mid <> s.mid
      THEN c.return_mid END) AS return_people_cnt
  FROM input_user i
  LEFT JOIN source_share_events s ON s.mid = i.mid
  LEFT JOIN return_click_events c ON c.shareid = s.shareid
  GROUP BY i.mid
),
video_rows AS (
  SELECT
    i.mid,
    v.dt,
    v.machineinfo_system,
    v.machineinfo_brand,
    v.machineinfo_model,
    v.clientip,
    CAST(v.clienttimestamp AS BIGINT) AS event_ts
  FROM input_user i
  JOIN loghubods.video_action_log_applet v
    ON v.mid = i.mid
   AND v.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND v.business IN ('videoView','videoPlay','videoShareFriend','videoClick')
),
attribute_metrics AS (
  SELECT
    mid,
    CONCAT_WS(',', COLLECT_SET(CASE WHEN machineinfo_system IS NOT NULL
      AND machineinfo_system <> '' THEN machineinfo_system END)) AS operating_system_list,
    CONCAT_WS(',', COLLECT_SET(CASE
      WHEN COALESCE(machineinfo_brand, '') <> '' OR COALESCE(machineinfo_model, '') <> ''
      THEN CONCAT_WS(' ', machineinfo_brand, machineinfo_model) END)) AS device_model_list,
    COUNT(DISTINCT CASE
      WHEN COALESCE(machineinfo_brand, '') <> '' OR COALESCE(machineinfo_model, '') <> ''
      THEN CONCAT_WS(' ', machineinfo_brand, machineinfo_model) END) AS device_model_cnt
  FROM video_rows
  GROUP BY mid
),
daily_ip_ranked AS (
  SELECT
    mid,
    dt,
    clientip,
    ROW_NUMBER() OVER (PARTITION BY mid, dt ORDER BY event_ts DESC) AS rn
  FROM video_rows
  WHERE clientip IS NOT NULL AND clientip <> ''
),
city_metrics AS (
  SELECT
    mid,
    CONCAT_WS(',', COLLECT_SET(CASE WHEN city IS NOT NULL AND city <> '' THEN city END))
      AS city_list,
    COUNT(DISTINCT CASE WHEN city IS NOT NULL AND city <> '' THEN city END) AS city_cnt
  FROM (
    SELECT mid, ANALYSISIP(clientip, 'city') AS city
    FROM daily_ip_ranked
    WHERE rn = 1
  ) t
  GROUP BY mid
),
capture_adview_events AS (
  SELECT i.mid, a.dt AS event_dt, a.subsessionid,
         CAST(a.clienttimestamp AS BIGINT) AS adview_ts
  FROM input_user i JOIN loghubods.ad_action_log_own a
    ON a.machinecode = i.mid
   AND a.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND a.businesstype = 'adView'
  WHERE a.subsessionid IS NOT NULL AND a.subsessionid <> ''
    AND a.clienttimestamp RLIKE '^[0-9]+$'
),
capture_events AS (
  SELECT i.mid, s.dt AS event_dt, s.subsessionid,
         CAST(s.clienttimestamp AS BIGINT) AS capture_ts
  FROM input_user i JOIN loghubods.simpleevent_log s
    ON s.machinecode = i.mid
   AND s.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND s.businesstype = 'userCaptureScreen'
  WHERE s.subsessionid IS NOT NULL AND s.subsessionid <> ''
    AND s.clienttimestamp RLIKE '^[0-9]+$'
),
capture_close_events AS (
  SELECT i.mid, a.dt AS event_dt, a.subsessionid,
         CAST(a.clienttimestamp AS BIGINT) AS close_ts
  FROM input_user i JOIN loghubods.ad_action_log_own a
    ON a.machinecode = i.mid
   AND a.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND a.businesstype = 'adCloseBtnTap'
),
capture_video_view_events AS (
  SELECT i.mid, v.dt AS event_dt, v.subsessionid,
         CAST(v.clienttimestamp AS BIGINT) AS event_ts
  FROM input_user i JOIN loghubods.video_action_log_applet v
    ON v.mid = i.mid
   AND v.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND v.business = 'videoView' AND v.businesstype = 'videoView'
   AND v.pagesource RLIKE 'category$'
),
capture_video_click_events AS (
  SELECT i.mid, s.dt AS event_dt, s.subsessionid,
         CAST(s.clienttimestamp AS BIGINT) AS event_ts
  FROM input_user i JOIN loghubods.simpleevent_log s
    ON s.machinecode = i.mid
   AND s.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND s.businesstype = 'videoClick'
),
capture_ad_click_events AS (
  SELECT i.mid, a.dt AS event_dt, a.subsessionid,
         CAST(a.clienttimestamp AS BIGINT) AS event_ts
  FROM input_user i JOIN loghubods.ad_action_log_own a
    ON a.machinecode = i.mid
   AND a.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND a.businesstype = 'adClick'
),
matched_capture_events AS (
  SELECT DISTINCT a.mid, a.event_dt, a.subsessionid, c.capture_ts
  FROM capture_adview_events a
  JOIN capture_events c
    ON a.mid = c.mid AND a.event_dt = c.event_dt
   AND a.subsessionid = c.subsessionid AND c.capture_ts >= a.adview_ts
  WHERE NOT EXISTS (
    SELECT 1 FROM capture_close_events x
    WHERE x.mid = a.mid AND x.event_dt = a.event_dt
      AND x.subsessionid = a.subsessionid
      AND x.close_ts > a.adview_ts AND x.close_ts <= c.capture_ts
  )
  AND NOT EXISTS (
    SELECT 1 FROM capture_video_view_events x
    WHERE x.mid = a.mid AND x.event_dt = a.event_dt
      AND x.subsessionid = a.subsessionid
      AND x.event_ts > a.adview_ts AND x.event_ts <= c.capture_ts
  )
  AND NOT EXISTS (
    SELECT 1 FROM capture_video_click_events x
    WHERE x.mid = a.mid AND x.event_dt = a.event_dt
      AND x.subsessionid = a.subsessionid
      AND x.event_ts > a.adview_ts AND x.event_ts <= c.capture_ts
  )
  AND NOT EXISTS (
    SELECT 1 FROM capture_ad_click_events x
    WHERE x.mid = a.mid AND x.event_dt = a.event_dt
      AND x.subsessionid = a.subsessionid
      AND x.event_ts > a.adview_ts AND x.event_ts <= c.capture_ts
  )
),
capture_metrics AS (
  SELECT mid, COUNT(*) AS ad_play_capture_cnt
  FROM matched_capture_events
  GROUP BY mid
),
landing_capture_metrics AS (
  SELECT
    i.mid,
    COUNT(DISTINCT CASE WHEN a.machinecode IS NOT NULL THEN CONCAT(
      a.dt, '#', COALESCE(a.subsessionid, ''), '#', COALESCE(a.clienttimestamp, '')
    ) END) AS ad_landing_capture_cnt
  FROM input_user i
  LEFT JOIN loghubods.ad_action_log_own a
    ON a.machinecode = i.mid
   AND a.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND a.businesstype = 'adUserCaptureScreen'
  GROUP BY i.mid
),
bg_adview_events AS (
  SELECT i.mid, a.dt AS event_dt, a.sessionid, a.subsessionid,
         CAST(a.clienttimestamp AS BIGINT) AS adview_ts
  FROM input_user i JOIN loghubods.ad_action_log_own a
    ON a.machinecode = i.mid
   AND a.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND a.businesstype = 'adView' AND a.ownadsystemtype = 'ownPlatform'
  WHERE a.sessionid IS NOT NULL AND a.sessionid <> ''
    AND a.subsessionid IS NOT NULL AND a.subsessionid <> ''
    AND a.clienttimestamp RLIKE '^[0-9]+$'
),
active_end_events AS (
  SELECT i.mid, s.dt AS event_dt, s.sessionid, s.subsessionid,
         CAST(s.clienttimestamp AS BIGINT) AS active_end_ts
  FROM input_user i JOIN loghubods.simpleevent_log s
    ON s.machinecode = i.mid
   AND s.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND s.businesstype = 'userActiveEnd'
   AND s.endroutepath = 'pages/swiper/index'
  WHERE s.sessionid IS NOT NULL AND s.sessionid <> ''
    AND s.subsessionid IS NOT NULL AND s.subsessionid <> ''
    AND s.clienttimestamp RLIKE '^[0-9]+$'
),
foreground_session_max AS (
  SELECT i.mid, u.dt AS event_dt, u.sessionid,
         MAX(CAST(u.clienttimestamp AS BIGINT)) AS last_foreground_ts
  FROM input_user i JOIN loghubods.useractive_log u
    ON u.machinecode = i.mid
   AND u.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND u.businesstype = 'path' AND u.path = 'pages/swiper/index'
  WHERE u.sessionid IS NOT NULL AND u.sessionid <> ''
    AND u.clienttimestamp RLIKE '^[0-9]+$'
  GROUP BY i.mid, u.dt, u.sessionid
),
bg_close_events AS (
  SELECT i.mid, a.dt AS event_dt, a.subsessionid,
         CAST(a.clienttimestamp AS BIGINT) AS close_ts
  FROM input_user i JOIN loghubods.ad_action_log_own a
    ON a.machinecode = i.mid
   AND a.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND a.businesstype = 'adCloseBtnTap'
),
home_pageview_events AS (
  SELECT i.mid, s.dt AS event_dt, s.subsessionid,
         CAST(s.clienttimestamp AS BIGINT) AS pageview_ts
  FROM input_user i JOIN loghubods.simpleevent_log s
    ON s.machinecode = i.mid
   AND s.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND s.businesstype = 'pageView' AND s.pagesource RLIKE 'category_55$'
),
mini_program_open_events AS (
  SELECT i.mid, a.dt AS event_dt, a.subsessionid,
         CAST(a.clienttimestamp AS BIGINT) AS mini_open_ts
  FROM input_user i JOIN loghubods.ad_action_log_own a
    ON a.machinecode = i.mid
   AND a.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND a.businesstype = 'miniProgramOnOpen'
),
bg_video_view_events AS (
  SELECT i.mid, v.dt AS event_dt, v.sessionid,
         CAST(v.clienttimestamp AS BIGINT) AS video_view_ts
  FROM input_user i JOIN loghubods.video_action_log_applet v
    ON v.mid = i.mid
   AND v.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND v.business = 'videoView' AND v.businesstype = 'videoView'
),
matched_background_events AS (
  SELECT DISTINCT a.mid, a.event_dt, a.sessionid, a.subsessionid, e.active_end_ts
  FROM bg_adview_events a
  JOIN active_end_events e
    ON a.mid = e.mid AND a.event_dt = e.event_dt
   AND a.sessionid = e.sessionid AND a.subsessionid = e.subsessionid
   AND e.active_end_ts BETWEEN a.adview_ts AND a.adview_ts + 10000
  JOIN foreground_session_max f
    ON f.mid = a.mid AND f.event_dt = a.event_dt AND f.sessionid = a.sessionid
   AND f.last_foreground_ts > e.active_end_ts
  WHERE NOT EXISTS (
    SELECT 1 FROM bg_close_events x
    WHERE x.mid = a.mid AND x.event_dt = a.event_dt
      AND x.subsessionid = a.subsessionid
      AND x.close_ts > a.adview_ts AND x.close_ts <= e.active_end_ts
  )
  AND NOT EXISTS (
    SELECT 1 FROM home_pageview_events x
    WHERE x.mid = a.mid AND x.event_dt = a.event_dt
      AND x.subsessionid = a.subsessionid
      AND x.pageview_ts > a.adview_ts AND x.pageview_ts <= e.active_end_ts
  )
  AND NOT EXISTS (
    SELECT 1 FROM mini_program_open_events x
    WHERE x.mid = a.mid AND x.event_dt = a.event_dt
      AND x.subsessionid = a.subsessionid
      AND x.mini_open_ts > a.adview_ts AND x.mini_open_ts <= e.active_end_ts
  )
  AND NOT EXISTS (
    SELECT 1 FROM bg_video_view_events x
    WHERE x.mid = a.mid AND x.event_dt = a.event_dt
      AND x.sessionid = a.sessionid
      AND x.video_view_ts > e.active_end_ts
      AND x.video_view_ts <= f.last_foreground_ts
  )
),
background_metrics AS (
  SELECT mid, COUNT(*) AS ad_play_background_return_cnt
  FROM matched_background_events
  GROUP BY mid
),
landing_view AS (
  SELECT i.mid, a.dt AS event_dt, a.sessionid, a.subsessionid, a.pqtid,
         CAST(a.clienttimestamp AS BIGINT) AS view_ts
  FROM input_user i JOIN loghubods.ad_action_log_own a
    ON a.machinecode = i.mid
   AND a.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND a.businesstype = 'adSelfLandingView'
   AND a.ownadsystemtype = 'ownPlatform'
  WHERE a.sessionid IS NOT NULL AND a.sessionid <> ''
    AND a.subsessionid IS NOT NULL AND a.subsessionid <> ''
    AND a.pqtid IS NOT NULL AND a.pqtid <> ''
    AND a.clienttimestamp RLIKE '^[0-9]+$'
),
landing_hide AS (
  SELECT i.mid, a.dt AS event_dt, a.sessionid, a.subsessionid, a.pqtid,
         CAST(a.clienttimestamp AS BIGINT) AS hide_ts
  FROM input_user i JOIN loghubods.ad_action_log_own a
    ON a.machinecode = i.mid
   AND a.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND a.businesstype = 'adSelfLandingHide'
  WHERE a.sessionid IS NOT NULL AND a.sessionid <> ''
    AND a.subsessionid IS NOT NULL AND a.subsessionid <> ''
    AND a.pqtid IS NOT NULL AND a.pqtid <> ''
    AND a.clienttimestamp RLIKE '^[0-9]+$'
),
landing_open_conv AS (
  SELECT DISTINCT pqtid
  FROM loghubods.ad_own_open_conv
  WHERE dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
    AND pqtid IS NOT NULL AND pqtid <> ''
),
return_landing_session_max AS (
  SELECT i.mid, u.dt AS event_dt, u.sessionid,
         MAX(CAST(u.clienttimestamp AS BIGINT)) AS last_return_ts
  FROM input_user i JOIN loghubods.useractive_log u
    ON u.machinecode = i.mid
   AND u.dt BETWEEN '${window_start_dt}' AND '${window_end_dt}'
   AND u.businesstype = 'path'
   AND u.path IN (
     'pages/ad-self-landing/index','pages/marketing-landing/index',
     'pages/promo-page/index','pages/event-landing/index'
   )
  WHERE u.sessionid IS NOT NULL AND u.sessionid <> ''
    AND u.clienttimestamp RLIKE '^[0-9]+$'
  GROUP BY i.mid, u.dt, u.sessionid
),
matched_landing_events AS (
  SELECT DISTINCT v.mid, v.event_dt, v.sessionid, v.subsessionid, v.pqtid, h.hide_ts
  FROM landing_view v
  JOIN landing_hide h
    ON v.mid = h.mid AND v.event_dt = h.event_dt
   AND v.sessionid = h.sessionid AND v.subsessionid = h.subsessionid
   AND v.pqtid = h.pqtid
   AND h.hide_ts BETWEEN v.view_ts AND v.view_ts + 10000
  JOIN return_landing_session_max r
    ON r.mid = v.mid AND r.event_dt = v.event_dt AND r.sessionid = v.sessionid
   AND r.last_return_ts > h.hide_ts
  WHERE NOT EXISTS (
    SELECT 1 FROM landing_open_conv c WHERE c.pqtid = v.pqtid
  )
),
landing_return_metrics AS (
  SELECT mid, COUNT(*) AS landing_no_scan_background_return_cnt
  FROM matched_landing_events
  GROUP BY mid
)
SELECT
  i.mid AS `用户id`,
  i.anchor_dt AS `锚点日期`,
  i.window_start_dt AS `统计开始日期`,
  i.window_end_dt AS `统计结束日期`,
  COALESCE(a.active_day_cnt, 0) AS `活跃天数`,
  COALESCE(c.card_click_object_uv, 0) AS `点击卡片去重次数`,
  COALESCE(c.card_click_object_pv, 0) AS `点击卡片不去重次数`,
  COALESCE(p.real_play_cnt, 0) AS `有效播放次数`,
  COALESCE(s.video_share_cnt, 0) AS `分享次数`,
  COALESCE(r.window_shareid_cnt, 0) AS `分享ID数`,
  COALESCE(r.return_click_pv, 0) AS `分享带回点击次数`,
  COALESCE(r.return_people_cnt, 0) AS `分享带回去重回流人数`,
  COALESCE(ad.own_ad_click_cnt, 0) AS `自营广告点击次数`,
  COALESCE(ad.long_press_scan_cnt, 0) AS `长按扫码转化次数`,
  COALESCE(cp.ad_play_capture_cnt, 0) AS `广告播放中截图次数`,
  COALESCE(lc.ad_landing_capture_cnt, 0) AS `广告落地页截图次数`,
  COALESCE(bg.ad_play_background_return_cnt, 0) AS `广告播放中切后台再返回次数`,
  COALESCE(lr.landing_no_scan_background_return_cnt, 0)
    AS `无扫码落地页切后台再返回次数`,
  COALESCE(c.source_1007_cnt, 0) AS `来源个人分享1007次数`,
  COALESCE(c.source_1008_cnt, 0) AS `来源群分享1008次数`,
  COALESCE(c.source_1058_cnt, 0) AS `来源公众号文章1058次数`,
  COALESCE(c.source_1074_cnt, 0) AS `来源公众号即转1074次数`,
  COALESCE(c.source_1067_1095_cnt, 0) AS `来源小程序投流1067_1095次数`,
  o.other_scene_list AS `其他来源场景值及次数`,
  c.source_shareid_list AS `来源上游shareid`,
  a.source_opengid_list AS `来源群id`,
  am.operating_system_list AS `操作系统`,
  am.device_model_list AS `设备机型`,
  COALESCE(am.device_model_cnt, 0) AS `设备机型数`,
  cm.city_list AS `历史地域`,
  COALESCE(cm.city_cnt, 0) AS `历史地域数`
FROM input_user i
LEFT JOIN active_metrics a ON a.mid = i.mid
LEFT JOIN real_play_metrics p ON p.mid = i.mid
LEFT JOIN share_metrics s ON s.mid = i.mid
LEFT JOIN click_metrics c ON c.mid = i.mid
LEFT JOIN other_scene_metrics o ON o.mid = i.mid
LEFT JOIN ad_metrics ad ON ad.mid = i.mid
LEFT JOIN return_metrics r ON r.mid = i.mid
LEFT JOIN attribute_metrics am ON am.mid = i.mid
LEFT JOIN city_metrics cm ON cm.mid = i.mid
LEFT JOIN capture_metrics cp ON cp.mid = i.mid
LEFT JOIN landing_capture_metrics lc ON lc.mid = i.mid
LEFT JOIN background_metrics bg ON bg.mid = i.mid
LEFT JOIN landing_return_metrics lr ON lr.mid = i.mid;