In the first challenge in this series I presented some data that I had put together to analyze the posting history of users on my largest board. Rather than post the exact SQL used I thought it would be fun to leave it as an exercise for my blog readers. Today I’ll reveal my solution.

The Data

Here’s what the data looked like:

+-------------+----------+----------+----------+----------+----------+----------+
| total_posts | w1_posts | w2_posts | w3_posts | w4_posts | w5_posts | w6_posts |
+-------------+----------+----------+----------+----------+----------+----------+
|          56 |        3 |        4 |        8 |        9 |       16 |       16 |
|         589 |       39 |       53 |       89 |      106 |      152 |      150 |
|           7 |        1 |        0 |        0 |        0 |        3 |        3 |
|           2 |        0 |        1 |        0 |        0 |        1 |        0 |
|          10 |        0 |        1 |        3 |        3 |        1 |        2 |
|         266 |       36 |       37 |       61 |       43 |       34 |       55 |
|          14 |        1 |        1 |        3 |        0 |        1 |        8 |
|           5 |        0 |        0 |        0 |        0 |        0 |        5 |
|          15 |        2 |        3 |        2 |        1 |        3 |        4 |
|         236 |       58 |       53 |       49 |       31 |       19 |       26 |
|           5 |        0 |        2 |        1 |        0 |        2 |        0 |
|          46 |       27 |        0 |        2 |        9 |        8 |        0 |
|           3 |        2 |        0 |        0 |        0 |        0 |        1 |
|           4 |        2 |        2 |        0 |        0 |        0 |        0 |
+-------------+----------+----------+----------+----------+----------+----------+

Here was the desired output:

+------+----------+
| wk   | count(*) |
+------+----------+
|    1 |     1139 |
|    2 |      407 |
|    3 |      198 |
|    4 |       85 |
|    5 |       64 |
|    6 |       46 |
+------+----------+

This challenge is really quite simple, as long as you know the proper function to use.

I Saw The Sign…

One of the fairly standard functions that I use is sign(). I say “fairly standard” because it is available on most of the databases I have worked with. It’s a very simple function but that doesn’t stop it from being quite versatile at the same time. The definition of sign() is as follows:

sign(X) = 1 if X > 0
sign(X) = 0 if X = 0
sign(X) = -1 if X < 0
sign(X) is null if X Is Null

So X can be any of an infinite number of values from minus infinity to plus infinity, and the sign() function will return a finite number of values. How does this help?

In this challenge all I want to know is if someone posted. It's impossible for someone to have a negative or null post count. So the results of a sign() function will be exactly zero or one. Hmmm, binary. I like binary.

The Solution

So here is my solution that generates the output as shown above from the data I provided:

select     sign(w1_posts)+sign(w2_posts)+sign(w3_posts)+sign(w4_posts)+sign(w5_posts)+sign(w6_posts) as wk
,       count(*)
from    sql_challenge_01
group by 1

What this sign() function does in this case is reduce any positive post count to one, and leave any zero post count at zero. If you then sum those values together you can count the number of weeks that included posts by a user. To do that, I include count(*) as the second column in my query.

Let me take the first three rows of the data and show how this works:

+-------------+----------+----------+----------+----------+----------+----------+
| total_posts | w1_posts | w2_posts | w3_posts | w4_posts | w5_posts | w6_posts |
+-------------+----------+----------+----------+----------+----------+----------+
|          56 |        3 |        4 |        8 |        9 |       16 |       16 |
|         589 |       39 |       53 |       89 |      106 |      152 |      150 |
|           7 |        1 |        0 |        0 |        0 |        3 |        3 |

Each of the first two rows have a positive value for each weekly value. So the sign() function will reduce them to 1. The net result is 1+1+1+1+1+1 or 6. That means that user posted in each of the last six weeks. The third row has 3 positive values and 3 zero values. The net is 1+0+0+0+1+1 or 3. That user has posted in three of the last six weeks. Simple, eh?

Note that I don't care which weeks the user has posted in, only that there was at least one post. By creating the column as shown in the solution SQL above I will generate a number from 1 to 6. The "group by" clause pulls those rows together and the count(*) counts the number of rows that match.

Another Sign

I mentioned that I use this function frequently, and so I thought I would share another use of the same function. I want to run one query that will show me how many users I have, how many activated users I have, and how many posting users I have. A user count is simple. The user_active field is already a binary, so I can simply sum() that field to count activated users. The user_posts field can have numbers from zero to infinity (it should not have negative numbers, but it can, and I ignore that in this solution). In order to count, I need to reduce that infinite number of values down to something binary. Like this:

select     count(user_id) as total_users
,       sum(user_active) as active_users
,       sum(sign(user_posts)) as posting_users
from    phpbb_users;

There are other ways this could have been done. But I believe this is probably the easiest.

Did anyone else get something different? If so, please share. SQL Challenge #2 is much harder than this one, and will come out in the next few days. The rules are the same, solve the challenge using a single query, no php code is allowed.