参见示例

with  res0 as ( -- test results
select a.user_id,a.test_id,a.question_id,a.given_answer_id,t.valid
from user_answers a
inner join test_data t on t.test_id=a.test_id and t.question_id=a.question_id
   and t.answer_id=given_answer_id 
)
,res as (-- one answer for one question
select a.user_id,a.test_id,a.question_id
  ,case when sum(case when valid='false' then -1 else 0 end)<0 then 'false' 
  else 'true' end valid
from res0 a
group by user_id,test_id,a.question_id
)
,resg as( -- result grouped
  select user_id,test_id
    ,sum(case when valid='true' then 1 else 0 end)valid_qty
    ,sum(case when valid='false' then 1 else 0 end)notvalid_qty
  from res
  group by user_id,test_id
)
,tests as( -- all tests and question count for every test
select test_id
  ,count(distinct question_id) test_question_qty
from test_data
group by test_id
)
select u.user_id,t.test_id
  ,coalesce(valid_qty,0) question_correct_count
  ,coalesce(test_question_qty,0)-coalesce(valid_qty,0) question_wrong_count
  ,test_question_qty,coalesce(valid_qty,0) valid_qty
  ,coalesce(notvalid_qty,0) notvalid_qty
  ,coalesce(test_question_qty,0)-coalesce(valid_qty,0)
       -coalesce(notvalid_qty,0) as notanswered_qty
from  (select distinct user_id from user_answers) u
left join tests t on 1=1 
left join resg g on g.user_id=u.user_id and t.test_id=g.test_id
;

字符串
| 测试ID|正确计数|错误计数|试题数量|有效|无效|未回答| notanswered |
| --|--|--|--|--|--|--| ------------ |
| t1|一个|二个|三个|一个|一个|一个| 1 |
| t2| 0个|一个|一个|0个|一个|0个| 0 |
| t1| 0个|三个|三个|0个|0个|三个| 3 |
| t2|一个|0个|一个|一个|0个|0个| 0 |