Streak calculation

Streak calculation was, surprisingly, the hardest part about this project. On the one hand, I know for sure that streaks are necessary and if I didn’t implement it, I’d thwart a good amount of users from enjoying the game.

At the same time, I didn’t wanna run a php script loop that is inefficient since resources are valuable. I wanted to use the SQL implementation to calculate the streak.

My first idea was simple yet profoundly flawed: When a user plays a game, check the current streak in the database and update it to 1 if the previous streak was broken or if not, add 1 to the current streak.

The issue with this is we had to sync the unsigned users data when they login. For example, if a guest plays #3, #4 and #5, the game should sync the results of those game when they log in or register.

For reference, here’s what the table for calculating streaks looked like:

CREATE TABLE wp_potd_current_streak (
  start int
  end int
  user_id int,
  foreign key (user_id) references users(user_id)
)

I know, I know, not the prettiest sight. The problem was that this SQL code is not even correct. Many many times, it would simply display results that are incorrect.

After lots of thinking and researching, for some reason apparently nobody had thought of implementing streaks before or at-least wrote about them, I stumbled upon SQL cursors.

What are SQL cursors?

An SQL cursor is very simple: It is a cursor that is pointed at a particular row, you could advance the cursor and read its data or close it.

For example, say you have a cursor at row 5, you could advance it continously until you reach the end of all the available rows. Keep in mind that while you do this, you only have access to one row at a time so you could aggregate and do all of the cool things you wanna do.

What’s the catch, you might ask? SQL cursors don’t exist in WordPress’s wpdb. So it means we’re back to square one.

Of course, I had experience with using SQL rows in Go’s database/sql but no matter how hard I searched, there was no equivalent in WordPress land.

Apparently, it is present within the standard PHP library PDO.

Back to writing SQL queries

SQL has always been a write-bottlenecked or even write-only language for me. Over the years, I purposely avoided writing SQL and would look for alternatives everywhere. I’d prefer mangodb, couchdb and others even if they were less performant just so I could have more flexbility with the schema and fetching data.

Regardless, I came across a cool little feature which was derived tables and SQL variables. Basically, MariaDB and MySQL don’t have a function called ROW_NUMBER() (present in other databases like PostgresSQL) which calculates which row is calculated.

For the sake of demonstration, imagine we have a database full of the standard ANSI colors which are 10 in total. If we were to sort by color name ascending, the results would be like so:

+--------+
| color  |
+--------+
| Black  |
| Blue   |
| Brown  |
| Gray   |
| Green  |
| Orange |
| Purple |
| Red    |
| White  |
| Yellow |
+--------+

If we were to use ROW_NUMBER(), the output would be like so:

+--------------+--------+
| ROW_NUMBER() | color  |
+--------------+--------+
|            1 | Black  |
|            2 | Blue   |
|            3 | Brown  |
|            4 | Gray   |
|            5 | Green  |
|            6 | Orange |
|            7 | Purple |
|            8 | Red    |
|            9 | White  |
|           10 | Yellow |
+--------------+--------+

If we were to sort by descending:

+--------------+--------+
| ROW_NUMBER() | color  |
+--------------+--------+
|            1 | Yellow |
|            2 | White  |
|            3 | Red    |
|            4 | Purple |
|            5 | Orange |
|            6 | Green  |
|            7 | Gray   |
|            8 | Brown  |
|            9 | Blue   |
|           10 | Black  |
+--------------+--------+

Now, the query for this doesn’t use ROW_NUMBER() but the variables and derived tables, take a look at the following query:

SELECT ( @row_num := @row_num + 1 ) AS `ROW_NUMBER()`,
       c.*
FROM   colors c,
       (SELECT @row_num := 0) n
ORDER  BY c.color DESC; 

It is this exist mechanism that allowed for the better streak calculation

SQL Variables + Derived Tables to derive streaks

Let’s define what a streak is: A streak is a the most amount of wins consecutively since today’s puzzles.

If somebody won today’s puzzles and yesterday’s but lost the day before yesterday, the streak would be 2.

To build the SQL query, we start with today’s puzzle number. We could retrieve it by fetching it from the algorithm. Afterwards, we would compare this value with the consecutive won puzzle numbers.

Now, one cool fact about SQL that I didn’t know about before embarking on this project is nested SQL SELECT statements. Here’s an example:

SELECT color FROM (SELECT color FROM colors WHERE color NOT LIKE '_r%') ORDER BY RAND();

This SQL query fetches all the colors that don’t have the letter r as its second character and after that it shuffles the colors.

So with this information, we want to fetch today’s puzzle and if it was won.

If it was won and the streak was not zero, add one to the streak, if not, return 1(i.e. the streak starts from today).

Then, calculate the streak. The streak would use MySQL’s version of ROW_NUMBER but with a catch: It descends as the rows pass and it has a start value of today’s puzzle number.

If today’s puzzle number is 33 and we have five consecutive wins, the results would be like so:

+--------------+-----+
| ROW_NUMBER() | won |
+--------------+-----+
|           33 |   1 |
|           32 |   1 |
|           31 |   1 |
|           30 |   1 |
|           29 |   1 |
+--------------+-----+

However, the query we’ve described so far does not know if the wins are so consecutive, it only knows if the puzzles are, well, won.

The way to calculate if the wins are consecutive is to compare the ROW_NUMBER to the puzzle_number and the query should stop if they aren’t equal. When fetching the puzzle_number, make sure that the order is by descending the puzzle number.

Finally, calculate the amount of rows you have and voilà, you have a correct streak.