CodeMockLab/check_ai_bank_status.sql at master · ink-hz/CodeMockLab · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
-- 检查AI内置题库的50道题目状态
WITH ai_bank_questions AS (
  -- 从architectureDesign类别提取题目
  SELECT
    jsonb_array_elements_text("simulatedInterview"->'architectureDesign') as question_content,
    'architectureDesign' as category
  FROM "ResumeAIProfile"
  WHERE "simulatedInterview" IS NOT NULL
    AND "simulatedInterview"->'architectureDesign' IS NOT NULL

  UNION ALL

  -- 从leadership类别提取题目
  SELECT
    jsonb_array_elements_text("simulatedInterview"->'leadership') as question_content,
    'leadership' as category
  FROM "ResumeAIProfile"
  WHERE "simulatedInterview" IS NOT NULL
    AND "simulatedInterview"->'leadership' IS NOT NULL

  UNION ALL

  -- 从techDepth的Kubernetes分支提取题目
  SELECT
    jsonb_array_elements_text("simulatedInterview"->'techDepth'->'Kubernetes') as question_content,
    'techDepth-Kubernetes' as category
  FROM "ResumeAIProfile"
  WHERE "simulatedInterview" IS NOT NULL
    AND "simulatedInterview"->'techDepth'->'Kubernetes' IS NOT NULL

  UNION ALL

  -- 从techDepth的Flink分支提取题目
  SELECT
    jsonb_array_elements_text("simulatedInterview"->'techDepth'->'Flink') as question_content,
    'techDepth-Flink' as category
  FROM "ResumeAIProfile"
  WHERE "simulatedInterview" IS NOT NULL
    AND "simulatedInterview"->'techDepth'->'Flink' IS NOT NULL

  UNION ALL

  -- 从techDepth的TensorFlow分支提取题目
  SELECT
    jsonb_array_elements_text("simulatedInterview"->'techDepth'->'TensorFlow') as question_content,
    'techDepth-TensorFlow' as category
  FROM "ResumeAIProfile"
  WHERE "simulatedInterview" IS NOT NULL
    AND "simulatedInterview"->'techDepth'->'TensorFlow' IS NOT NULL
)

-- 统计结果
SELECT
  abq.category,
  COUNT(*) as total_questions,
  COUNT(CASE WHEN q."modelAnswer" IS NOT NULL AND q."modelAnswer" != '' THEN 1 END) as has_answer,
  COUNT(CASE WHEN q."modelAnswer" IS NULL OR q."modelAnswer" = '' THEN 1 END) as no_answer,
  ROUND(
    COUNT(CASE WHEN q."modelAnswer" IS NOT NULL AND q."modelAnswer" != '' THEN 1 END) * 100.0 / COUNT(*),
    1
  ) as answer_coverage_percent
FROM ai_bank_questions abq
LEFT JOIN "Question" q ON q.content = abq.question_content
GROUP BY abq.category
ORDER BY abq.category;