-
Notifications
You must be signed in to change notification settings - Fork 0
/
sql-artigo-csedu2016
79 lines (61 loc) · 2.07 KB
/
sql-artigo-csedu2016
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
66
67
68
69
70
71
72
73
74
75
76
77
78
79
-- # QUERY NECESSARIA PARA A ESCRITA DO ARTIGO DO CSEDU 2016 #
-- * TIPOS DE QUESTOES CAPTURADAS
-- Perguntas com mais de 3 repostas (OK)
-- Perguntas que contem uma melhor resposta (OK)
DECLARE @type_question int = 1; -- questao
DECLARE @type_answer int = 2; -- resposta
DECLARE @min_amount_answer int = 3; -- min. quantidade de respostas por questao
WITH
------------------ INICIO DO GET_QUESTIONS
GET_QUESTIONS (
id_question,
answer_count,
AcceptedAnswerId,
body_question,
id_author_question,
name_author_question,
reputation_author_question
)
AS
(
SELECT TOP 1687
post.Id AS id_question,
post.AnswerCount as answer_count,
AcceptedAnswerId,
post.Body AS body_question,
post.OwnerUserId AS id_author_question,
usr.DisplayName AS name_author_question,
usr.Reputation AS reputation_author_question
--
FROM Posts post JOIN Users usr on post.OwnerUserId = usr.Id
--
WHERE post.PostTypeId = @type_question -- tipo do post (pergunta/resposta)
AND post.AnswerCount > @min_amount_answer -- numero minimo de respostas
AND post.AcceptedAnswerId != '' -- questões com uma melhor resposta selecionada
)
------------------ FIM DO GET_QUESTIONS
,
------------------ INICIO DO GET_ANSWERS
GET_ANSWERS
AS
(
SELECT
post.Id AS id_answer,
post.Body AS body_answer,
post.OwnerUserId AS id_author_answer,
usr.DisplayName AS name_author_answer,
usr.Reputation AS reputation_author_answer,
post.Score AS score_answer,
post.ParentId AS id_parent
--
FROM Posts post JOIN Users usr on post.OwnerUserId = usr.Id
--
WHERE post.PostTypeId = @type_answer -- tipo do post (pergunta/resposta)
)
------------------ FIM DO GET_ANSWERS
------------------ GET ALL
SELECT get_questions.*, get_answers.*,
(case when get_questions.AcceptedAnswerId = get_answers.id_answer then 'TRUE' else 'FALSE' end) as accepted_answer
FROM GET_QUESTIONS get_questions
LEFT JOIN GET_ANSWERS get_answers ON get_questions.id_question = get_answers.id_parent order by
get_questions.id_question ASC, get_answers.score_answer DESC;