Queries for Survey (based on pilot)

Notes

Ignoring the following users since that was Sarah testing things:

select DISTINCT user_id from responses where response LIKE '%sarah%';
+----------------------------------+
| user_id                          |
+----------------------------------+
| 29c5a9be4cbaa00fb4f57cbc81aa91b0 |
| b483eddd3b5579dfd82047e2eedfeb89 |
| ac32fd38a7d1ac0de863fbe1042878bc |
| 6c0bbd517edee7814b69f2ce5ae41d73 |
+----------------------------------+

Get total unique participants

select count(distinct(user_id)) from responses
where user_id <> '29c5a9be4cbaa00fb4f57cbc81aa91b0'
and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73';

+--------------------------+
| count(distinct(user_id)) |
+--------------------------+
|                        2 |
+--------------------------+

Get background questions

select * from questions where qtype = 'bg';
+----+-------------------------------------------------------------------------------------------------------------+-------+-----------+--------------+---------------------+---------------------+
| id | question_text                                                                                               | qtype | mandatory | responseType | created_at          | updated_at          |
+----+-------------------------------------------------------------------------------------------------------------+-------+-----------+--------------+---------------------+---------------------+
|  1 | Is developing software part of your job?                                                                    | bg    |         1 |              | 2018-12-05 22:05:39 | 2018-12-05 22:05:39 |
|  2 | What is your job title?                                                                                     | bg    |         1 | string       | 2018-12-05 22:05:39 | 2018-12-05 22:05:39 |
|  3 | For how many years have you been developing software?                                                       | bg    |         1 | string       | 2018-12-05 22:05:39 | 2018-12-05 22:05:39 |
|  4 | What is your area of software development?                                                                  | bg    |         1 | string       | 2018-12-05 22:05:39 | 2018-12-05 22:05:39 |
|  5 | Have you used StackOverflow to search for information before? || yes || no                                  | bg    |         1 | mc           | 2018-12-05 22:05:39 | 2018-12-05 22:05:39 |
|  6 | Have you contributed to StackOverflow before ? (questions, answers, comments, discussion etc.) || yes || no | bg    |         1 | mc           | 2018-12-05 22:05:39 | 2018-12-05 22:05:39 |
+----+-------------------------------------------------------------------------------------------------------------+-------+-----------+--------------+---------------------+---------------------+

Distribution of Background of participants

Is Developing SW part of your job?

select response, count(*) from responses
    -> where question_id = 1
    -> and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
    -> and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
    -> and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
    -> GROUP BY response;
+----------+----------+
| response | count(*) |
+----------+----------+
| Yes      |        2 |
+----------+----------+

What is your job title

select response, count(*) from responses                                                                        -> where question_id = 2
    -> and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
    -> and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
    -> and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
    -> GROUP BY response;
+-------------------------------+----------+
| response                      | count(*) |
+-------------------------------+----------+
| Software Development Engineer |        1 |
| Student                       |        1 |
+-------------------------------+----------+
2 rows in set (0.00 sec)

For How many years have you been developing SW?

select response, count(*) from responses
    -> where question_id = 3
    -> and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
    -> and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
    -> and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
    -> GROUP BY response;
+----------+----------+
| response | count(*) |
+----------+----------+
| 3        |        2 |
+----------+----------+

What is your area of SW development?

 select response, count(*) from responses
    -> where question_id = 4
    -> and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
    -> and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
    -> and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
    -> GROUP BY response;
+----------------------+----------+
| response             | count(*) |
+----------------------+----------+
| Backend              |        1 |
| mobile, data science |        1 |
+----------------------+----------+

Have you used StackOverflow to search for information before?

select response, count(*) from responses
    -> where question_id = 5
    -> and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
    -> and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
    -> and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
    -> GROUP BY response;
+----------+----------+
| response | count(*) |
+----------+----------+
| yes      |        2 |
+----------+----------+

Have you contributed to SO before?

select response, count(*) from responses
    -> where question_id = 6
    -> and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
    -> and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
    -> and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
    -> GROUP BY response;
+----------+----------+
| response | count(*) |
+----------+----------+
| no       |        2 |
+----------+----------+

Sentences up for evaluation

How many sentences total are we selecting from?

select technique, count(*) from sentences group by technique;
+---------------------------------+----------+
| technique                       | count(*) |
+---------------------------------+----------+
| -1                              |       24 |
| condinsight                     |       19 |
| condinsight-WordPatternBaseline |        4 |
| ifsentence                      |       15 |
| ifsentence-WordPatternBaseline  |        3 |
| WordPatternBaseline             |       16 |
+---------------------------------+----------+

and these sentences fall into X unique threads:

select count(distinct thread_id) from sentences;
+---------------------------+
| count(distinct thread_id) |
+---------------------------+
|                        22 |
+---------------------------+

How many threads and sentences were evaluated by how many users

Number of threads evaluated:

select from responses, sentences, questions
where responses.sentence_id = sentences.id
and responses.question_id = questions.id
and questions.qtype = 'sg'
and sentence_id > 0
and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
group by thread_id;

+-----------+-------------------------+
| thread_id | count(distinct user_id) |
+-----------+-------------------------+
|  28646332 |                       2 |
|  28957285 |                       2 |
|  29775797 |                       2 |
+-----------+-------------------------+

Number of sentences evaluated:

select sentence_id, count(distinct user_id)
from responses, sentences, questions
where responses.sentence_id = sentences.id
and responses.question_id = questions.id
and questions.qtype = 'sg'
and sentence_id > 0
and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
group by sentence_id;

+-------------+-------------------------+
| sentence_id | count(distinct user_id) |
+-------------+-------------------------+
|         102 |                       2 |
|         103 |                       2 |
|         104 |                       2 |
|         105 |                       2 |
|         106 |                       2 |
|         108 |                       2 |
|         109 |                       2 |
|         110 |                       2 |
|         112 |                       2 |
|         113 |                       2 |
+-------------+-------------------------+

Sentence evaluations

For question id 7 (first sentence in individual sentence eval):

select sentence_id, technique, user_id, response
from responses, sentences
where responses.sentence_id = sentences.id
and sentence_id > 0
and question_id = 7
and technique != -1
and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
INTO OUTFILE '/var/lib/mysql-files/q7_responses.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"';

For question Id 8 (second question in individual sentence eval)

select sentence_id, technique, user_id, response
from responses, sentences
where responses.sentence_id = sentences.id
and sentence_id > 0
and question_id = 8
and technique != -1
and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
INTO OUTFILE '/var/lib/mysql-files/q8_responses.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"';

For question Id 9 (third question in individual sentence eval)

select sentence_id, technique, user_id, response
from responses, sentences
where responses.sentence_id = sentences.id
and sentence_id > 0
and question_id = 9
and technique != -1
and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
INTO OUTFILE '/var/lib/mysql-files/q9_responses.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"';

For the question about the thread

select thread_id, user_id, response
from responses, sentences
where responses.sentence_id = sentences.id
and question_id = 10
and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
INTO OUTFILE '/var/lib/mysql-files/q10_responses.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"';

-- not sure why there are some that are mine.. I guess we didn't reset the database correctly then

Exit questions

comments about useful

select user_id, response
from responses
where question_id = 11
and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
INTO OUTFILE '/var/lib/mysql-files/q11_responses.csv'

-- seems 0.. is this a required question?

comments about not useful

select user_id, response
from responses
where question_id = 12
and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
INTO OUTFILE '/var/lib/mysql-files/q12_responses.csv

What to highlight in SO

select user_id, response
from responses
where question_id = 13
and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
INTO OUTFILE '/var/lib/mysql-files/q13_responses.csv

general comments

select user_id, response
from responses
where question_id = 14
and user_id <> 'b483eddd3b5579dfd82047e2eedfeb89'
and user_id <> 'ac32fd38a7d1ac0de863fbe1042878bc'
and user_id <> '6c0bbd517edee7814b69f2ce5ae41d73'
INTO OUTFILE '/var/lib/mysql-files/q14_responses.csv