Abuse Hunter

-- 按邮箱域名统计注册用户数，找出 Top 异常域名
SELECT
  SUBSTRING(email FROM '@(.+)$') AS domain,
  COUNT(*) AS user_count,
  MIN(created_at) AS first_signup,
  MAX(created_at) AS last_signup,
  EXTRACT(EPOCH FROM MAX(created_at) - MIN(created_at)) / 3600 AS span_hours
FROM users
GROUP BY domain
HAVING COUNT(*) > 10
ORDER BY user_count DESC
LIMIT 20;

# WHOIS 查域名创建时间和注册商
whois <domain> | grep -iE "creation|registrar|name server"
# DNS 检查：有没有网站，有没有邮件配置
dig <domain> MX +short
dig <domain> A +short
# 是否有 ICP 备案（仅中国域名）
curl -s "https://api.vvhan.com/api/icp?url=<domain>" | jq .

-- 按天统计注册量（可疑域名）
SELECT
  DATE(created_at) AS reg_date,
  COUNT(*) AS daily_count
FROM users
WHERE email LIKE '%@<suspect_domain>'
GROUP BY reg_date
ORDER BY reg_date;

-- 按小时统计（找注册高峰）
SELECT
  DATE(created_at) AS reg_date,
  EXTRACT(HOUR FROM created_at) AS reg_hour,
  COUNT(*) AS hourly_count
FROM users
WHERE email LIKE '%@<suspect_domain>'
GROUP BY reg_date, reg_hour
HAVING COUNT(*) > 10
ORDER BY hourly_count DESC;

-- 注册间隔分析（关键！）
WITH ordered AS (
  SELECT created_at,
    LAG(created_at) OVER (ORDER BY created_at) AS prev_at
  FROM users
  WHERE email LIKE '%@<suspect_domain>'
)
SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM created_at - prev_at)) AS median_interval_sec,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY EXTRACT(EPOCH FROM created_at - prev_at)) AS p90_interval_sec
FROM ordered
WHERE prev_at IS NOT NULL;

-- 前缀长度分布
SELECT
  LENGTH(SPLIT_PART(email, '@', 1)) AS prefix_len,
  COUNT(*) AS cnt
FROM users
WHERE email LIKE '%@<suspect_domain>'
GROUP BY prefix_len
ORDER BY cnt DESC;

-- 前缀字符模式分类
SELECT
  CASE
    WHEN SPLIT_PART(email, '@', 1) ~ '^[a-z]+$' THEN 'letters_only'
    WHEN SPLIT_PART(email, '@', 1) ~ '^[0-9]+$' THEN 'digits_only'
    WHEN SPLIT_PART(email, '@', 1) ~ '^[a-z0-9]{6}$' THEN '6char_alnum'
    WHEN SPLIT_PART(email, '@', 1) ~ '^[a-z0-9]{5}$' THEN '5char_alnum'
    ELSE 'other'
  END AS prefix_pattern,
  COUNT(*) AS cnt
FROM users
WHERE email LIKE '%@<suspect_domain>'
GROUP BY prefix_pattern
ORDER BY cnt DESC;

-- UA 丰富度对比（可疑域名 vs 全量用户）
-- 可疑域名
SELECT
  COUNT(DISTINCT session_id) AS total_sessions,
  COUNT(DISTINCT user_agent) AS unique_uas,
  ROUND(COUNT(DISTINCT user_agent)::NUMERIC / NULLIF(COUNT(DISTINCT session_id), 0), 4) AS ua_diversity
FROM sessions s
JOIN users u ON s.user_id = u.id
WHERE u.email LIKE '%@<suspect_domain>';

-- 全量用户（基线）
SELECT
  COUNT(DISTINCT session_id) AS total_sessions,
  COUNT(DISTINCT user_agent) AS unique_uas,
  ROUND(COUNT(DISTINCT user_agent)::NUMERIC / NULLIF(COUNT(DISTINCT session_id), 0), 4) AS ua_diversity
FROM sessions s
JOIN users u ON s.user_id = u.id
WHERE u.email NOT LIKE '%@<suspect_domain>';

-- 每用户 session 数分布
SELECT
  u.email LIKE '%@<suspect_domain>' AS is_suspect,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY session_count) AS median_sessions,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY session_count) AS p90_sessions
FROM (
  SELECT user_id, COUNT(*) AS session_count
  FROM sessions GROUP BY user_id
) sc
JOIN users u ON sc.user_id = u.id
GROUP BY is_suspect;

-- 注册到首次使用的间隔
SELECT
  u.email LIKE '%@<suspect_domain>' AS is_suspect,
  COUNT(*) AS users_with_usage,
  PERCENTILE_CONT(0.5) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM first_usage - u.created_at)
  ) AS median_activation_sec,
  PERCENTILE_CONT(0.9) WITHIN GROUP (
    ORDER BY EXTRACT(EPOCH FROM first_usage - u.created_at)
  ) AS p90_activation_sec
FROM users u
JOIN (
  SELECT user_id, MIN(created_at) AS first_usage
  FROM usage_events GROUP BY user_id
) ue ON u.id = ue.user_id
GROUP BY is_suspect;

-- 使用覆盖率
SELECT
  u.email LIKE '%@<suspect_domain>' AS is_suspect,
  COUNT(*) AS total_users,
  COUNT(ue.user_id) AS users_with_usage,
  ROUND(COUNT(ue.user_id)::NUMERIC / COUNT(*), 4) AS usage_rate
FROM users u
LEFT JOIN (
  SELECT DISTINCT user_id FROM usage_events
) ue ON u.id = ue.user_id
GROUP BY is_suspect;

-- 可疑域名积分消耗汇总
SELECT
  COUNT(*) AS accounts_with_credits,
  SUM(total_granted) AS total_credits_granted,
  SUM(total_consumed) AS total_credits_consumed,
  SUM(total_expired) AS total_credits_expired,
  ROUND(SUM(total_consumed)::NUMERIC / NULLIF(SUM(total_granted), 0), 4) AS consumption_rate
FROM credit_summary cs
JOIN users u ON cs.user_id = u.id
WHERE u.email LIKE '%@<suspect_domain>';

-- 模型调用成本 Top 10
SELECT
  model_name,
  COUNT(*) AS call_count,
  COUNT(DISTINCT user_id) AS unique_users,
  ROUND(SUM(cost_usd)::NUMERIC, 2) AS total_cost
FROM usage_events ue
JOIN users u ON ue.user_id = u.id
WHERE u.email LIKE '%@<suspect_domain>'
GROUP BY model_name
ORDER BY total_cost DESC
LIMIT 10;

# 滥用排查报告

## 目标域名：xxx.yyy
## 排查时间：YYYY-MM-DD
## 综合评分：X / 12（✅ / ⚠️ / 🚨）

### 各维度评分
| 维度 | 得分 | 关键发现 |
|------|------|---------|
| ... | ... | ... |

### 关键时间线
- Day 1: ...
- Day N: ...

### 影响评估
- 涉及账号数：
- 消耗积分：
- 推理成本：

### 处置建议
1. 即时：...
2. 短期：...
3. 长期：...