# Canonical SQL Templates

Use `${bizdate}` as `yyyyMMdd`. Do not mix these templates' CTEs across scenarios.

## S1: Playback screenshot user list

```sql
WITH adview_events AS (
  SELECT dt event_dt, machinecode mid, subsessionid, CAST(clienttimestamp AS BIGINT) adview_ts
  FROM loghubods.ad_action_log_own
  WHERE dt BETWEEN '20260401' AND '${bizdate}' AND businesstype='adView'
    AND machinecode IS NOT NULL AND machinecode<>'' AND subsessionid IS NOT NULL AND subsessionid<>'' AND clienttimestamp IS NOT NULL AND clienttimestamp<>''
), capture_events AS (
  SELECT dt event_dt, machinecode mid, loginuid uid, subsessionid, CAST(clienttimestamp AS BIGINT) capture_ts
  FROM loghubods.simpleevent_log
  WHERE dt BETWEEN '20260401' AND '${bizdate}' AND businesstype='userCaptureScreen'
    AND machinecode IS NOT NULL AND machinecode<>'' AND subsessionid IS NOT NULL AND subsessionid<>'' AND clienttimestamp IS NOT NULL AND clienttimestamp<>''
), close_events AS (
  SELECT dt event_dt, machinecode mid, subsessionid, CAST(clienttimestamp AS BIGINT) close_ts
  FROM loghubods.ad_action_log_own
  WHERE dt BETWEEN '20260401' AND '${bizdate}' AND businesstype='adCloseBtnTap'
    AND machinecode IS NOT NULL AND machinecode<>'' AND subsessionid IS NOT NULL AND subsessionid<>'' AND clienttimestamp IS NOT NULL AND clienttimestamp<>''
), matched AS (
  SELECT DISTINCT a.mid,c.uid,c.capture_ts
  FROM adview_events a JOIN capture_events c ON a.event_dt=c.event_dt AND a.mid=c.mid AND a.subsessionid=c.subsessionid AND c.capture_ts>=a.adview_ts
  WHERE NOT EXISTS (SELECT 1 FROM close_events x WHERE x.event_dt=a.event_dt AND x.mid=a.mid AND x.subsessionid=a.subsessionid AND x.close_ts>a.adview_ts AND x.close_ts<=c.capture_ts)
), latest_uid AS (
  SELECT mid,uid FROM (SELECT mid,uid,ROW_NUMBER() OVER(PARTITION BY mid ORDER BY capture_ts DESC) rn FROM matched) t WHERE rn=1
)
SELECT 'adPlay_capture' AS `type`, mid, uid, 0 AS risk_level FROM latest_uid;
```

For the count, keep all CTEs and replace the final select with `SELECT COUNT(*) FROM latest_uid`.

## S2: Landing screenshot user list

```sql
WITH landing_capture_events AS (
  SELECT machinecode mid, loginuid uid, CAST(clienttimestamp AS BIGINT) capture_ts
  FROM loghubods.ad_action_log_own
  WHERE dt BETWEEN '20260401' AND '${bizdate}' AND businesstype='adUserCaptureScreen'
    AND machinecode IS NOT NULL AND machinecode<>'' AND clienttimestamp IS NOT NULL AND clienttimestamp<>''
), latest_uid AS (
  SELECT mid,uid FROM (SELECT mid,uid,ROW_NUMBER() OVER(PARTITION BY mid ORDER BY capture_ts DESC) rn FROM landing_capture_events) t WHERE rn=1
)
SELECT 'adlanding_capture' AS `type`, mid, uid, 0 AS risk_level FROM latest_uid;
```

## S3/S4: Dynamic-tier output contract

Build `risk_events(mid, uid, event_dt, event_ts, ...)` with the scenario-specific keys documented in `risk-strategies.md`, then use this exact suffix:

```sql
, user_risk AS (
  SELECT mid, COUNT(*) behavior_cnt, MAX(event_dt) last_event_dt FROM risk_events GROUP BY mid
), latest_uid AS (
  SELECT mid,uid FROM (
    SELECT mid,uid,ROW_NUMBER() OVER(PARTITION BY mid ORDER BY event_ts DESC) rn FROM risk_events
  ) t WHERE rn=1
), current_risk_users AS (
  SELECT r.mid,u.uid,
    CASE
      WHEN r.behavior_cnt>=3 THEN '${type_3}'
      WHEN r.behavior_cnt=2 AND r.last_event_dt BETWEEN TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyymmdd'),-29,'dd'),'yyyymmdd') AND '${bizdate}' THEN '${type_2}'
      WHEN r.behavior_cnt=1 AND r.last_event_dt BETWEEN TO_CHAR(DATEADD(TO_DATE('${bizdate}','yyyymmdd'),-2,'dd'),'yyyymmdd') AND '${bizdate}' THEN '${type_1}'
    END AS risk_type
  FROM user_risk r LEFT JOIN latest_uid u ON r.mid=u.mid
)
SELECT risk_type AS `type`,mid,uid,0 AS risk_level
FROM current_risk_users WHERE risk_type IS NOT NULL;
```

### S3 risk_events builder

Use `ad_action_log_own.adView` with `ownAdSystemType='ownPlatform'`; join `simpleevent_log.userActiveEnd` on same day, mid, sessionid, and subsessionid; require `0 <= active_end_ts-adview_ts <= 10000`; exclude close and homepage pageview in that interval; require later same-session `useractive_log.path='pages/swiper/index'`. Set `event_ts=active_end_ts`.

Types: `${type_1}=adview10s_hide_return_risk_1_forbidden_3`, `${type_2}=adview10s_hide_return_risk_2_forbidden_30`, `${type_3}=adview10s_hide_return_risk_3_forbidden_forever`.

### S4 risk_events builder

Use own-platform `adSelfLandingView` and `adSelfLandingHide` on same day, mid, sessionid, subsessionid, pqtid; require `0 <= hide_ts-view_ts <= 10000`; exclude pqtid present in `ad_own_open_conv`; require later same-session `useractive_log.path` in the four documented landing paths. Set `event_ts=hide_ts`.

Types: `${type_1}=adlanding10s_hide_no_open_return_risk_1_forbidden_3`, `${type_2}=adlanding10s_hide_no_open_return_risk_2_forbidden_30`, `${type_3}=adlanding10s_hide_no_open_return_risk_3_forbidden_forever`.

## Shared-table write behavior

For a target table with `type, mid, uid, risk_level`, use `INSERT OVERWRITE` only after unioning: unrelated existing types, existing permanent type-3 users, newly computed type-3 users, and current dynamic type-1/type-2 users excluding permanent mids. Do not retain expired type-1/type-2 rows.