Get the highest answer rate question from a table survey_log
with these columns: uid, action, question_id, answer_id, q_num, timestamp.
uid means user id; action has these kind of values: "show", "answer", "skip"; answer_id is not null when action column is "answer", while is null for "show" and "skip"; q_num is the numeral order of the question in current session.
Write a sql query to identify the question which has the highest answer rate.
Example:
Input:
+------+-----------+--------------+------------+-----------+------------+
| uid | action | question_id | answer_id | q_num | timestamp |
+------+-----------+--------------+------------+-----------+------------+
| 5 | show | 285 | null | 1 | 123 |
| 5 | answer | 285 | 124124 | 1 | 124 |
| 5 | show | 369 | null | 2 | 125 |
| 5 | skip | 369 | null | 2 | 126 |
+------+-----------+--------------+------------+-----------+------------+
Output:
+-------------+
| survey_log |
+-------------+
| 285 |
+-------------+
Explanation:
question 285 has answer rate 1/1, while question 369 has 0/1 answer rate, so output 285.
Note: The highest answer rate meaning is: answer number's ratio in show number in the same question.
drop table survey_log
create table survey_log (uid int,action varchar(25),question_id int,answer_id int,q_num int,timestamp int)
insert into survey_log values(5,'show',285,null,1,123)
insert into survey_log values(5,'answer',285,124124,1,124)
insert into survey_log values(5,'show',369,null,2,125)
insert into survey_log values(5,'skip',369,null,2,126)
select * from survey_log
with cte1 as(
select question_id,count(*) as answer_cnt from survey_log
where action='answer'
group by question_id
),cte2 as(
select question_id,count(*) as question_cnt from survey_log
where action='show'
group by question_id
)select top 1 c1.question_id
from cte1 c1 join cte2 c2 on c1.question_id=c2.question_id
order by (answer_cnt/question_cnt) desc
select top 1 question_id from
(
select question_id,
sum(case when action='answer' then 1 else 0 end) as answer_cnt,
sum(case when action='show' then 1 else 0 end) as question_cnt
from survey_log
group by question_id
) as tpl
order by answer_cnt/question_cnt desc
#seems it is incorrect answer
select top 1 question_id
from survey_log
group by question_id
order by count(answer_id)/sum(case when action='show' then 1 else 0 end) desc