示例成品 · 平台演示，按左边这组点选真跑出来的
默认假设：
- 用户主表：`scrm_user`
- 行为表：`scrm_user_behavior`
- `send_group`=主动发群，`click_link`=点击链接
- 无点击记录按 999 天计算，便于满足“≥30 天”判定
如果没有独立用户主表，把 `FROM scrm_user u` 替换为 `FROM (SELECT DISTINCT user_id FROM scrm_user_behavior) u`。
```sql
WITH send_agg AS (
SELECT
user_id,
count(*) AS send_msg_cnt_30d,
max(behavior_time) AS last_send_time
FROM scrm_user_behavior
WHERE behavior_type = 'send_group'
AND behavior_time >= today() - 30
GROUP BY user_id
),
click_agg AS (
SELECT
user_id,
count(*) AS click_cnt,
max(behavior_time) AS last_click_time
FROM scrm_user_behavior
WHERE behavior_type = 'click_link'
GROUP BY user_id
),
merged AS (
SELECT
u.user_id,
ifNull(s.send_msg_cnt_30d, 0) AS send_msg_cnt_30d,
if(ifNull(c.click_cnt, 0) = 0,
999,
dateDiff('day', toDate(c.last_click_time), today())) AS last_click_days,
CASE
WHEN s.last_send_time IS NULL AND c.last_click_time IS NULL THEN NULL
WHEN s.last_send_time IS NULL THEN c.last_click_time
WHEN c.last_click_time IS NULL THEN s.last_send_time
WHEN s.last_send_time >= c.last_click_time THEN s.last_send_time
ELSE c.last_click_time
END AS last_active_time
FROM scrm_user u
LEFT JOIN send_agg s ON s.user_id = u.user_id
LEFT JOIN click_agg c ON c.user_id = u.user_id
)
SELECT
user_id,
CASE
WHEN send_msg_cnt_30d <= 1 AND last_click_days >= 30 THEN '高沉默'
WHEN send_msg_cnt_30d <= 1 OR last_click_days >= 30 THEN '中沉默'
ELSE '正常'
END AS silence_level,
last_active_time,
CASE
WHEN send_msg_cnt_30d <= 1 AND last_click_days >= 30 THEN '强唤醒-发券+短信'
WHEN send_msg_cnt_30d <= 1 THEN '轻唤醒-社群内容触达'
WHEN last_click_days >= 30 THEN '轻唤醒-链接召回'
ELSE '无需动作'
END AS action_tag
FROM merged
WHERE send_msg_cnt_30d <= 1 OR last_click_days >= 30
ORDER BY last_active_time ASC NULLS FIRST
```
点左边「开工 · 直接出成品」，出一份你自己的版本（文字免费）