Number of messages on each thread: select thread, count(thread) from message group by (thread); +--------+---------------+ | thread | count(thread) | +--------+---------------+ | 1 | 3 | | 2 | 1 | | 3 | 10 | | 4 | 37 | | 5 | 20 | | 6 | 1 | | 7 | 1 | | 8 | 5 | | 9 | 6 | | 10 | 7 | | 11 | 2 | | 12 | 6 | | 13 | 25 | | 14 | 1 | | 15 | 13 | | 16 | 1 | | 17 | 4 | | 18 | 19 | | 19 | 4 | | 20 | 16 | | 21 | 5 | | 22 | 5 | | 23 | 1 | | 24 | 2 | | 25 | 1 | | 26 | 7 | | 27 | 1 | | 28 | 2 | | 29 | 3 | | 30 | 8 | | 31 | 4 | | 32 | 5 | | 33 | 4 | | 34 | 10 | | 35 | 16 | | 36 | 5 | | 37 | 4 | | 38 | 13 | | 39 | 3 | | 40 | 3 | | 41 | 5 | | 42 | 1 | | 43 | 1 | | 44 | 3 | | 45 | 7 | | 46 | 4 | | 47 | 4 | | 48 | 2 | | 49 | 1 | | 50 | 6 | | 51 | 1 | | 52 | 3 | | 53 | 5 | | 54 | 4 | | 55 | 5 | | 56 | 11 | +--------+---------------+ 56 rows in set (0.05 sec) Number of messages on each thread, ordered: select thread, count(thread) from message group by (thread) order by count(thread); +--------+---------------+ | thread | count(thread) | +--------+---------------+ | 49 | 1 | | 7 | 1 | | 51 | 1 | | 42 | 1 | | 43 | 1 | | 23 | 1 | | 2 | 1 | | 14 | 1 | | 25 | 1 | | 16 | 1 | | 27 | 1 | | 6 | 1 | | 28 | 2 | | 11 | 2 | | 24 | 2 | | 48 | 2 | | 39 | 3 | | 29 | 3 | | 40 | 3 | | 52 | 3 | | 1 | 3 | | 44 | 3 | | 17 | 4 | | 19 | 4 | | 31 | 4 | | 54 | 4 | | 33 | 4 | | 46 | 4 | | 47 | 4 | | 37 | 4 | | 8 | 5 | | 41 | 5 | | 21 | 5 | | 53 | 5 | | 32 | 5 | | 22 | 5 | | 55 | 5 | | 36 | 5 | | 50 | 6 | | 9 | 6 | | 12 | 6 | | 10 | 7 | | 45 | 7 | | 26 | 7 | | 30 | 8 | | 34 | 10 | | 3 | 10 | | 56 | 11 | | 15 | 13 | | 38 | 13 | | 20 | 16 | | 35 | 16 | | 18 | 19 | | 5 | 20 | | 13 | 25 | | 4 | 37 | +--------+---------------+ 56 rows in set (0.00 sec) median: 4 Number of messages in each role: select role, count(role) from message group by (role); +------------+-------------+ | role | count(role) | +------------+-------------+ | helpful | 215 | | irrelevant | 62 | | new | 56 | | unhelpful | 14 | +------------+-------------+ 4 rows in set (0.01 sec) Number of messages in each role on Perl list: select role, count(role) from message where maillist="perl" group by (role); +------------+-------------+ | role | count(role) | +------------+-------------+ | helpful | 61 | | irrelevant | 3 | | new | 14 | | unhelpful | 6 | +------------+-------------+ 4 rows in set (0.00 sec) Number of messages in each role on Rails list: select role, count(role) from message where maillist="rails" group by (role); +------------+-------------+ | role | count(role) | +------------+-------------+ | helpful | 36 | | irrelevant | 5 | | new | 14 | | unhelpful | 2 | +------------+-------------+ 4 rows in set (0.00 sec) Number of messages in each role on Linux list: select role, count(role) from message where maillist="fedora" or maillist="ubuntu" group by (role); +------------+-------------+ | role | count(role) | +------------+-------------+ | helpful | 118 | | irrelevant | 54 | | new | 28 | | unhelpful | 6 | +------------+-------------+ 4 rows in set (0.01 sec) Number of messages in each role on language list: select role, count(role) from message where maillist="perl" or maillist="rails" group by (role); +------------+-------------+ | role | count(role) | +------------+-------------+ | helpful | 97 | | irrelevant | 8 | | new | 28 | | unhelpful | 8 | +------------+-------------+ 4 rows in set (0.00 sec) Number of unresolved messages: +-----------------+ | count(resolved) | +-----------------+ | 26 | +-----------------+ 1 row in set (0.00 sec) Number of resolved messages: select count(resolved) from thread where resolved=1; +-----------------+ | count(resolved) | +-----------------+ | 30 | +-----------------+ 1 row in set (0.00 sec) Number of unresolved Rails messages: select count(resolved) from thread where resolved=0 and maillist="rails"; +-----------------+ | count(resolved) | +-----------------+ | 7 | +-----------------+ 1 row in set (0.00 sec) Number of resolved Rails messages: select count(resolved) from thread where resolved=1 and maillist="rails"; +-----------------+ | count(resolved) | +-----------------+ | 7 | +-----------------+ 1 row in set (0.00 sec) Number of unresolved Perl messages: select count(resolved) from thread where resolved=0 and maillist="perl";xo +-----------------+ | count(resolved) | +-----------------+ | 5 | +-----------------+ 1 row in set (0.00 sec) Number of resolved Perl messages: select count(resolved) from thread where resolved=1 and maillist="perl"; +-----------------+ | count(resolved) | +-----------------+ | 9 | +-----------------+ 1 row in set (0.00 sec) Seconds required to resolve question select id, unix_timestamp(resolved_time)-unix_timestamp(start_time) from thread where resolved=1; +----+----------------------------------------------------------+ | id | unix_timestamp(resolved_time)-unix_timestamp(start_time) | +----+----------------------------------------------------------+ | 1 | 1734 | | 3 | 13527 | | 4 | 482 | | 10 | 136027 | | 11 | 41664 | | 12 | 8751 | | 15 | 85616 | | 17 | 1131 | | 19 | 4351 | | 20 | 141075 | | 21 | 35138 | | 24 | 148493 | | 26 | 65180 | | 28 | 210552 | | 29 | 28322 | | 30 | 143471 | | 31 | 60240 | | 33 | 8400 | | 34 | 8880 | | 35 | 56340 | | 37 | 7800 | | 39 | 164040 | | 40 | 19200 | | 45 | 23280 | | 46 | 58860 | | 52 | 1440 | | 53 | 41880 | | 54 | 83280 | | 55 | 166680 | | 56 | 639840 | +----+----------------------------------------------------------+ 30 rows in set (0.00 sec) Seconds required to resolve question, in order: select id, unix_timestamp(resolved_time)-unix_timestamp(start_time) as resolved_time from thread where resolved=1 order by resolved_time; +----+---------------+ | id | resolved_time | +----+---------------+ | 4 | 482 | | 17 | 1131 | | 52 | 1440 | | 1 | 1734 | | 19 | 4351 | | 37 | 7800 | | 33 | 8400 | | 12 | 8751 | | 34 | 8880 | | 3 | 13527 | | 40 | 19200 | | 45 | 23280 | | 29 | 28322 | | 21 | 35138 | | 11 | 41664 | | 53 | 41880 | | 35 | 56340 | | 46 | 58860 | | 31 | 60240 | | 26 | 65180 | | 54 | 83280 | | 15 | 85616 | | 10 | 136027 | | 20 | 141075 | | 30 | 143471 | | 24 | 148493 | | 39 | 164040 | | 55 | 166680 | | 28 | 210552 | | 56 | 639840 | +----+---------------+ 30 rows in set (0.00 sec) Median is (41880 + 41664)/2 = 41772 seconds time: 11:36:12 maximum is 177:44:0 (7 days, 9 hours, 44 minutes) minimum is 0:8:2 Seconds required to resolve question for Perl, in order: select id, unix_timestamp(resolved_time)-unix_timestamp(start_time) as resolved_time from thread where maillist='perl' and resolved=1 order by resolved_time; +----+---------------+ | id | resolved_time | +----+---------------+ | 37 | 7800 | | 33 | 8400 | | 34 | 8880 | | 40 | 19200 | | 29 | 28322 | | 35 | 56340 | | 31 | 60240 | | 30 | 143471 | | 39 | 164040 | +----+---------------+ 9 rows in set (0.00 sec) Median is 28322 time: 7:52:2 maximum is 45:34:0 (1 day, 21 hours, 34 minutes) minimum is 2:10:0 Seconds required to resolve question for Rails, in order: select id, unix_timestamp(resolved_time)-unix_timestamp(start_time) as resolved_time from thread where maillist='rails' and resolved=1 order by resolved_time; +----+---------------+ | id | resolved_time | +----+---------------+ | 52 | 1440 | | 45 | 23280 | | 53 | 41880 | | 46 | 58860 | | 54 | 83280 | | 55 | 166680 | | 56 | 639840 | +----+---------------+ 7 rows in set (0.00 sec) Median is 58860 time: 16:21:0 maximum is 177:44:0 (7 days, 9 hours, 44 minutes) minimum is 0:24:0 Seconds required to resolve question for languages, in order: select id, unix_timestamp(resolved_time)-unix_timestamp(start_time) as resolved_time from thread where (maillist='rails' or maillist='perl') and resolved=1 order by resolved_time; +----+---------------+ | id | resolved_time | +----+---------------+ | 52 | 1440 | | 37 | 7800 | | 33 | 8400 | | 34 | 8880 | | 40 | 19200 | | 45 | 23280 | | 29 | 28322 | | 53 | 41880 | | 35 | 56340 | | 46 | 58860 | | 31 | 60240 | | 54 | 83280 | | 30 | 143471 | | 39 | 164040 | | 55 | 166680 | | 56 | 639840 | +----+---------------+ 16 rows in set (0.01 sec) Median is (56340 + 41880)/2 = 49110 time: 13:38:30 maximum is 177:44:0 (7 days, 9 hours, 44 minutes) minimum is 0:24:0 Seconds required to resolve question for Linux, in order: select id, unix_timestamp(resolved_time)-unix_timestamp(start_time) as resolved_time from thread where (maillist='fedora' or maillist='ubuntu') and resolved=1 order by resolved_time; +----+---------------+ | id | resolved_time | +----+---------------+ | 4 | 482 | | 17 | 1131 | | 1 | 1734 | | 19 | 4351 | | 12 | 8751 | | 3 | 13527 | | 21 | 35138 | | 11 | 41664 | | 26 | 65180 | | 15 | 85616 | | 10 | 136027 | | 20 | 141075 | | 24 | 148493 | | 28 | 210552 | +----+---------------+ 14 rows in set (0.00 sec) Median is (41664 + 35138)/2 = 38401 time: 10:40:1 maximum is 58:29:12 (2 days, 10 hours, 29 minutes) minimum is 0:8:2 Number of messages on each thread: select message.thread, count(message.id) from message, thread where message.thread = thread.id group by message.thread; +--------+-------------------+ | thread | count(message.id) | +--------+-------------------+ | 1 | 3 | | 2 | 1 | | 3 | 10 | | 4 | 37 | | 5 | 20 | | 6 | 1 | | 7 | 1 | | 8 | 5 | | 9 | 6 | | 10 | 7 | | 11 | 2 | | 12 | 6 | | 13 | 25 | | 14 | 1 | | 15 | 13 | | 16 | 1 | | 17 | 4 | | 18 | 19 | | 19 | 4 | | 20 | 16 | | 21 | 5 | | 22 | 5 | | 23 | 1 | | 24 | 2 | | 25 | 1 | | 26 | 7 | | 27 | 1 | | 28 | 2 | | 29 | 3 | | 30 | 8 | | 31 | 4 | | 32 | 5 | | 33 | 4 | | 34 | 10 | | 35 | 16 | | 36 | 5 | | 37 | 4 | | 38 | 13 | | 39 | 3 | | 40 | 3 | | 41 | 5 | | 42 | 1 | | 43 | 1 | | 44 | 3 | | 45 | 7 | | 46 | 4 | | 47 | 4 | | 48 | 2 | | 49 | 1 | | 50 | 6 | | 51 | 1 | | 52 | 3 | | 53 | 5 | | 54 | 4 | | 55 | 5 | | 56 | 11 | +--------+-------------------+ 56 rows in set (0.01 sec) Number of messages on each thread, grouped by whether thread is resolved: select message.thread, count(message.id), thread.resolved from message, thread where message.thread = thread.id group by message.thread order by thread.resolved, message.id; +--------+-------------------+----------+ | thread | count(message.id) | resolved | +--------+-------------------+----------+ | 2 | 1 | 0 | | 5 | 20 | 0 | | 6 | 1 | 0 | | 7 | 1 | 0 | | 8 | 5 | 0 | | 9 | 6 | 0 | | 13 | 25 | 0 | | 14 | 1 | 0 | | 16 | 1 | 0 | | 18 | 19 | 0 | | 22 | 5 | 0 | | 23 | 1 | 0 | | 25 | 1 | 0 | | 27 | 1 | 0 | | 32 | 5 | 0 | | 36 | 5 | 0 | | 38 | 13 | 0 | | 41 | 5 | 0 | | 42 | 1 | 0 | | 43 | 1 | 0 | | 44 | 3 | 0 | | 47 | 4 | 0 | | 48 | 2 | 0 | | 49 | 1 | 0 | | 50 | 6 | 0 | | 51 | 1 | 0 | | 1 | 3 | 1 | | 3 | 10 | 1 | | 4 | 37 | 1 | | 10 | 7 | 1 | | 11 | 2 | 1 | | 12 | 6 | 1 | | 15 | 13 | 1 | | 17 | 4 | 1 | | 19 | 4 | 1 | | 20 | 16 | 1 | | 21 | 5 | 1 | | 24 | 2 | 1 | | 26 | 7 | 1 | | 28 | 2 | 1 | | 29 | 3 | 1 | | 30 | 8 | 1 | | 31 | 4 | 1 | | 33 | 4 | 1 | | 34 | 10 | 1 | | 35 | 16 | 1 | | 37 | 4 | 1 | | 39 | 3 | 1 | | 40 | 3 | 1 | | 45 | 7 | 1 | | 46 | 4 | 1 | | 52 | 3 | 1 | | 53 | 5 | 1 | | 54 | 4 | 1 | | 55 | 5 | 1 | | 56 | 11 | 1 | +--------+-------------------+----------+ 56 rows in set (0.00 sec) Number of messages on each thread, grouped by whether thread is resolved and ordered by number of messages: select message.thread, count(message.id) as num_messages, thread.resolved from message, thread where message.thread = thread.id group by message.thread order by thread.resolved, num_messages; +--------+--------------+----------+ | thread | num_messages | resolved | +--------+--------------+----------+ | 27 | 1 | 0 | | 43 | 1 | 0 | | 14 | 1 | 0 | | 16 | 1 | 0 | | 49 | 1 | 0 | | 2 | 1 | 0 | | 51 | 1 | 0 | | 6 | 1 | 0 | | 7 | 1 | 0 | | 23 | 1 | 0 | | 25 | 1 | 0 | | 42 | 1 | 0 | | 48 | 2 | 0 | | 44 | 3 | 0 | | 47 | 4 | 0 | | 32 | 5 | 0 | | 36 | 5 | 0 | | 22 | 5 | 0 | | 8 | 5 | 0 | | 41 | 5 | 0 | | 50 | 6 | 0 | | 9 | 6 | 0 | | 38 | 13 | 0 | | 18 | 19 | 0 | | 5 | 20 | 0 | | 13 | 25 | 0 | | 11 | 2 | 1 | | 28 | 2 | 1 | | 24 | 2 | 1 | | 29 | 3 | 1 | | 1 | 3 | 1 | | 52 | 3 | 1 | | 39 | 3 | 1 | | 40 | 3 | 1 | | 46 | 4 | 1 | | 31 | 4 | 1 | | 17 | 4 | 1 | | 33 | 4 | 1 | | 19 | 4 | 1 | | 37 | 4 | 1 | | 54 | 4 | 1 | | 21 | 5 | 1 | | 53 | 5 | 1 | | 55 | 5 | 1 | | 12 | 6 | 1 | | 45 | 7 | 1 | | 10 | 7 | 1 | | 26 | 7 | 1 | | 30 | 8 | 1 | | 34 | 10 | 1 | | 3 | 10 | 1 | | 56 | 11 | 1 | | 15 | 13 | 1 | | 35 | 16 | 1 | | 20 | 16 | 1 | | 4 | 37 | 1 | +--------+--------------+----------+ 56 rows in set (0.00 sec) median for unresolved: 2.5 median for resolved: 4.5 Number of messages on each thread for Perl, grouped by whether thread is resolved and ordered by number of messages: select message.thread, count(message.id) as num_messages, thread.resolved from message, thread where message.maillist='perl' and message.thread = thread.id group by message.thread order by thread.resolved, num_messages; +--------+--------------+----------+ | thread | num_messages | resolved | +--------+--------------+----------+ | 42 | 1 | 0 | | 41 | 5 | 0 | | 32 | 5 | 0 | | 36 | 5 | 0 | | 38 | 13 | 0 | | 39 | 3 | 1 | | 40 | 3 | 1 | | 29 | 3 | 1 | | 31 | 4 | 1 | | 33 | 4 | 1 | | 37 | 4 | 1 | | 30 | 8 | 1 | | 34 | 10 | 1 | | 35 | 16 | 1 | +--------+--------------+----------+ 14 rows in set (0.01 sec) median for unresolved: 5 median for resolved: 4 Number of messages on each thread for Rails, grouped by whether thread is resolved and ordered by number of messages: select message.thread, count(message.id) as num_messages, thread.resolved from message, thread where message.maillist='rails' and message.thread = thread.id group by message.thread order by thread.resolved, num_messages; +--------+--------------+----------+ | thread | num_messages | resolved | +--------+--------------+----------+ | 43 | 1 | 0 | | 49 | 1 | 0 | | 51 | 1 | 0 | | 48 | 2 | 0 | | 44 | 3 | 0 | | 47 | 4 | 0 | | 50 | 6 | 0 | | 52 | 3 | 1 | | 54 | 4 | 1 | | 46 | 4 | 1 | | 53 | 5 | 1 | | 55 | 5 | 1 | | 45 | 7 | 1 | | 56 | 11 | 1 | +--------+--------------+----------+ 14 rows in set (0.01 sec) median for unresolved: 2 median for resolved: 5 Number of helpful messages on each thread, grouped by list: select count(thread), thread, maillist from message where role='helpful' group by thread order by maillist, count(thread); +---------------+--------+----------+ | count(thread) | thread | maillist | +---------------+--------+----------+ | 1 | 11 | fedora | | 2 | 1 | fedora | | 3 | 17 | fedora | | 3 | 19 | fedora | | 4 | 5 | fedora | | 4 | 21 | fedora | | 5 | 9 | fedora | | 9 | 15 | fedora | | 9 | 3 | fedora | | 24 | 13 | fedora | | 1 | 33 | perl | | 2 | 32 | perl | | 2 | 39 | perl | | 2 | 40 | perl | | 2 | 29 | perl | | 3 | 31 | perl | | 3 | 37 | perl | | 3 | 41 | perl | | 3 | 36 | perl | | 5 | 30 | perl | | 9 | 34 | perl | | 11 | 38 | perl | | 15 | 35 | perl | | 1 | 48 | rails | | 2 | 52 | rails | | 3 | 53 | rails | | 3 | 46 | rails | | 3 | 54 | rails | | 3 | 47 | rails | | 4 | 55 | rails | | 5 | 45 | rails | | 5 | 50 | rails | | 7 | 56 | rails | | 1 | 24 | ubuntu | | 1 | 28 | ubuntu | | 2 | 8 | ubuntu | | 3 | 22 | ubuntu | | 4 | 4 | ubuntu | | 4 | 12 | ubuntu | | 5 | 10 | ubuntu | | 6 | 26 | ubuntu | | 10 | 20 | ubuntu | | 18 | 18 | ubuntu | +---------------+--------+----------+ 43 rows in set (0.00 sec)