Ding! Experience Points are Weird

I don't remember where I was when I hit level 60, but I hit level 70 while fighting on some tower in Shadowmoon Valley. I hit level 80 in the middle of Scholazar Basin. I hit level 85 while helping the Earthen Ring in the Twilight Highlands. I hit level 90 while killing bug people in the Dread Wastes. 

While these places are all very different, they have one thing in common: I dropped whatever I was doing, went to go do something else, and questioned when, if ever, I would go back. This is my experience with all of my characters when they reach the level cap, and I wager it's what happens to most everyone else, as well.

And that's weird.

It's weird to have the story flow interrupted by these non-story events that send me away. That tell me I'm done, even though the story isn't over. It seems like it would be more natural and much cooler if these important game moment synced up with important story moments. The main culprit in this is experience point. Every thing you do gives you experience points, as long as it's something that's deemed suitably difficult (the enemy/quest isn't too low of a level).

In some ways the game experience would be improved if leveling were tied to moments instead of a bar filling, but there's far too much that would complicate the matter. What about experience gained through PVP? Is grinding completely purposeless? What about people trying to level as pacifists? What about experience gain bonuses (heirlooms, rested XP, etc)?

Perhaps quests and PVP and killing NPCs can still grant experience and still potentially give you levels, but certain quests that signify major moments will make you character level, regardless of how far away the next level is, as long as your current level is at or below a certain point. Making it so that you will only level up if you're below a certain level will help keep people from manipulating this system to gross effect.

This could make the questing experience more interesting, by making those big moments more impactful. It would seem that it would need to be a rare occurrence, at most once per zone, to try and keep people from exploiting it. A question still remains about what to do with level caps. Would you want to save those for some expansion defining moment? Or would you want them to just occur at some major moment like other levels could? It's a tough question and there are pluses to both sides.

All said, I'm tired of reaching these very important game events during very minor story events. Sometimes they aren't even during any sort of story event at all. It's certainly an interesting idea and a change that I would welcome. What do you think? 

SQL Part 2 - Counting and More

In the previous section, I only showed how to pull user-level data from the tables in question, which people usually aren't interested in because there are generally too many users for you to care about their individual actions. Suppose we wanted to know how many logins to WoW there were on January 1st. The query for this would be

SELECT
     count(*)
FROM logins
WHERE
     game_id=1 AND login_time::date='2013-1-1'1;

The count(*)2 function gives the number of rows that meet the criterion, and therefore (in this case) return one record with one column, with the value there being the count. You don't have to put a * in the parenthesis, you can also put a field in there. If we had put count(user_id) it would have counted the number of rows where user_id isn't NULL. NULL is a special value a field may have that means that no information was supplied. In this case, it would return the same result, since every login record should contain a user_id.

The above example is a bit silly, since if someone logged in multiple times on January 1st, they would have more than one record in logins on that date. Chances are we want a count of the distinct users that logged in on that particular date, which we would code as

SELECT
     count(distinct user_id)
FROM logins
WHERE
     game_id=1 AND login_time::date='2013-1-1';

Adding the distinct modifier inside the count() will have it count how many different user_ids appear in the selection. Combining all of this, we can figuring how many users logged into WoW on January 1st and how many times they logged in on that day, on average, as

SELECT
     count(distinct user_id) as "users",
     count(*)/count(distinct user_id) as "logins/user"
FROM logins
WHERE
     game_id=1 AND login_time::date='2013-1-1';

The first column will tell us how many people logged in on January 1st and the second column tells us the average number of times those users logged in on that date. It does so by taking the total number of logins and dividing by how many people did those logins. The 'as "X"' parts give our calculated columns names. This is something you don't have to do with a calculated column, since the DBMS will supply some generic name for it, but you should do. You can also do it for non-calculated columns. The double-quotes aren't necessarily except in some situations, like if you want a space in the name (eg: "logins per user").

This was a bit of a potpourri section to cover a couple topics before we dig into something more complicated next time.

Question: Given the char_info table outlined here, calculate the average number of quests that have been completed by a level 90 character. Hint: remember that an average is calculated as the sum of the values divided by how many values there are. Where count() counts how many non-NULL entries are in a selection, the sum() function will add up the values.

Answer: here.

---

¹ The "::date" is necessary because login_time is a timestamp and we want to see if it's equal to a date. In short, the database considers  '2013-1-1 12:31:56' and '2013-1-1' to be different. '2013-1-1' is considered to be equal to '2013-1-1 00:00:00' (the midnight joining 2012-12-31 and 2013-1-1).
² count() and many other similar functions that summarize data are called "aggregate functions".

SQL Training - Part 1: Intro and Basic SELECT Statements

A typical table

SQL is the standard language for extracting and manipulating information from/in databases. While it comes in many varieties generally specific to the database in question, there is a significant shared portion and the differences tend to be more nuanced/less used functions.

The most common statement you'll use is the SELECT statement, which (surprise, surprise) selects data from a table or tables. The basic format of the select statement is

SELECT
     [columns]
FROM [table];

Where [columns] is a comma separated list of which columns you want to pull. For the sake of example, I'm going to be using a hypothetical set of tables that Blizzard might user for their games, since that will make the things we're talking about as common as possible to the typical audience of this blog. The first table we'll talk about will be logins, which contains data about which users logged into which Blizzard games at what time. So it might have 3 columns

date	| game_id	| user_id
1/1/2013	| 1	| 1234567
1/5/2013	| 3	| 3456789
1/7/2013	| 2	| 7636857

This table has three columns, recording who (user_id), logged into which game (game_id) on which date (date). To select all the data in this table, you would code

SELECT
     date,
     game_id,
     user_id
FROM logins;

But you'll probably never need to select all the data in a table. You might only be concerned with all users who logged into WoW (let's call that game_id=1) on 1/1/2013. To do this, you would code

SELECT
     user_id
FROM logins
WHERE
     game_id=1 AND date='1/1/2013';

I omitted the date and game_id columns since we already know what they are based on how we selected the data. They could be left in (and in some cases should).

I lied about how I would set up the logins table. I would instead set up the columns as such:

• logins - tracks players logging into games
• login_time - not just the date but also the time
• game_id - id for the game
• user_id - id for the user
• acct_id - id for the acct they log into

By making login_time have the date and the time, we can better track when people log in and how many times a day they log in. By adding acct_id you can keep track of which account for that game they logged in to. That query would become

SELECT
     user_id
FROM logins
WHERE
     game_id=1 AND login_time::date='1/1/2013';


Question: How would you select when and what games user 2435649 logged into during November?
Answer: here

If you have any questions, feel free to leave them in the comments or to email me. My email can be found on my About page.

NOTES

Note: login_time::date takes the timestamp from the login_time field and removes the time portion, giving you just the date so that the database can accurately tell if it's equal to '1/1/2013'. The way this is done will vary from database to database, but a similar solution should exist for all. Not all databases will necessarily read '1/1/2013' and accept it as a date and may have you do it differently, but for the sake of this, and all further examples, I'll keep doing things this way.

Note: If you're going to select all of the columns in a table you can use * instead of typing all of the column's names. To select all logins for a particular user (1234567) in December you would code

SELECT
     *
FROM logins
WHERE
     user_id=1234567 
     AND login_time::date BETWEEN '12/1/2012' AND '12/31/2012';

Note: The way I've done the spacing and decided where new lines go isn't mandatory and I just do it for easy reading.

My Favorite Games of 2012

I played games this year. Some of them were new to this year. I bet you played new games too at some point. Here are some of my favorites, in no particular order.

Sound Shapes
This Vita title is a pseudo-rhythm platformer with a robust level creation and sharing tool. Playing as a little eyeball and jamming to the beat of the music that drives the beat of the gameplay was really fun, as are many of the levels created by the community. Definitely unlike any other platformer that I've ever played.

Spelunky
Another platformer? Well, I happen to like them, and this one was exceptional. Spelunky's procedurally generated levels, challenging design, and tight controls and tuning make it exceptionally fun. Unlockable characters, plenty of secrets, and rewarding learning opportunities have made this a game that I've been playing for well over half a year, still haven't beat, and am still having fun.

Thrill Digger
This minigame in last year's The Legend of Zelda: Skyward Sword became quite the obsession of mine, so much so that I coded up a version of it to put here. This Minesweeper variant adds more uncertainty to the game that I couldn't help but obsess over.

Journey
Never have I had a game impact me so emotionally. Simple gameplay, beautiful scenery, unique multiplayer, and wordless storytelling combine to make a truly amazing experience. The story of your character and the history that you discover over the course of the game play out beautifully and gave me so much to ponder and I'm still not exactly sure of all the details.

Diablo III
The very controversial third entry in the Diablo franchise was one of my favorite games of the year. Many people didn't like how the auction house affected the gameplay and complained about a lack of depth to the endgame, but as someone who never got to the highest level, I never had a dull moment. My only complaint that I could levy would be that the Act II becomes quite tiresome much faster than the other acts. Being able to find a new way to play the game at any point in time gave me an experience that never got dull.

Nintendo Land
Nintendo launched a new console late in 2012 and the Wii Sports analog for it was Nintendo Land. A collection of minigames that use the Game Pad in different ways to highlight the new types of gameplay afforded by it is incredibly fun. If Nintendo can follow up on half of the great gameplay types exhibited here then the system should do just fine.

Frog Fractions
This indie flash game take the educational genre for quite a spin. This is the best way to learn fractions, as you'll go on a journey across so many different classic game genres. I have but one hint, when you get the turtle, use it to go underwater. This game is quite the love letter to gaming. The game is fully playable at the link above.

The Unfinished Swan
This game starts as a literal blank canvas, a stark white space upon which you fire paint to discover the shape of your surroundings. Later levels also exhibit new and interesting gameplay. This game tells a beautiful story in a very interesting way with wonderful gameplay. Definitely worth checking out.

Things I wish I had played: FTL, Mark of the Ninja, The Walking Dead, Persona 4 Golden, Theatrhythm

Things I wish I had played more of: Mists of Pandaria, Tokyo Jungle

Christmas Travel Woes

I suppose it all started innocently enough. We figured we'd go home to visit the family for Christmas, since we spent Thanksgiving here. We had our flights booked and everything. It all started to go downhill when it turned out that Sarah's brother wouldn't be around, and therefore couldn't cat sit for us. So we had to find a cat sitter. The second thing to go wrong was when I had picked our outbound flight, since the BART apparently doesn't run that early on a Sunday, so we had to pick from a week of surface parking our a cab. Cab was slightly cheaper.

But that's all okay, we (I'll) do better next time. We got there, got through security and got to our first flight of the day, which took us to Denver. We arrived in Denver, I grabbed us some food at the airport McDonald's and Sarah sat at what was to be the gate for our next flight. The printed boarding time on our tickets passed without the plane starting to board. Then the announcement came that our flight had been canceled and that we should head to customer service to get rerouted. This was odd since Denver had perfectly fine weather at the time. Instead of doing that I called customer service, and after an hour on the phone (mostly spent waiting for the person on the line to look up information and confirm things) we had our substitute flight booked, although it wouldn't be until the next day.

While I was on the phone we went ahead and got in the customer service line, just in case. The person on the phone told us to stay in line so we could get food and accommodation vouchers. We made some friends in line, some of whom we ran into the next day in Dallas. Three hours after getting off the phone (we're at a total of 4 hours since the flight was supposed to board) we finally reach the front of the line and get our vouchers. The attendant we had at the desk was very pleased that we had already booked our replacement flight with the phone customer service. We went to retrieve our bags and took a shuttle (also free) to our hotel for the night.

The hotel was nice and getting to spend an evening alone with Sarah was very pleasant. We returned to the airport the next day and made our way to Little Rock via Dallas. We arrived at around 5:30 on Christmas Eve and therefore missed my family's get-together, which I don't particularly regret, since I wasn't looking forward to fielding the same set of questions several different times.

Christmas with my parents was all fine and normal. We set out early Christmas Day for Tuckerman, AR (population : 18XX) to do Christmas with Sarah's family. That night it snowed. Bad. Some place got a foot of snow, and Tuckerman saw no less than 8 inches, easily.

The next day I set out with a flat-tipped shovel to make sure that the ruts that the more adventurous drivers had dug in the road went down to the pavement and we're just packing the ice. Our plan was to leave the next day and the last thing I wanted to do was try and drive on on packed snow that had melted slightly and refrozen to become ice. (The day AFTER it snows is usually the worst for snows, because of that).

We were able to leave unimpeded, their street comes off the highway, which was well cleared because of the traffic. We got back to Little Rock to spend an extra couple days with my family before coming home, even though large portions of the city were still without power.

We left the Saturday after Christmas and it was quite eventful. When Frontier was switching out my flight, the messed up something with my American Airlines return flight which took the counter attendant about 40 minutes to sort out. She really did great work though. We got to Dallas and got on our next flight where they attendants asked Sarah if they could see her ticket after we had already been seated. She gave them her boarding pass and they asked if she had any other tickets. No, of course, what else would she have. They elaborated, "Do you have a paper ticket?" which is a silly question because the boarding pass is made of paper and says TICKET AND BOARDING PASS at the top of it, so we have no idea what they could have possibly wanted. We think they were trying to kick her off the flight. We saw that they were holding another ticket with the same seat number on it but "Priority Boarding" also on it.

We did eventually get home to our kitties, who missed us very, very much and got to spend a couple days together before I had to return to work. That trip had worked out very differently than we could have anticipated that it would.

New Year

Us at my company's holiday party

The New Year has happened, and I'm already back into the swing of things. The days off were nice, even if our travel was quite eventful (in the bad sort of way). I'm currently about four-and-a-half months into my tenure at my current job as a data analyst at Zynga. I really enjoy my job. It has its ups and downs, but all jobs do. I enjoy my work, the only issue might be that my team just needs another analyst, for which we are currently hiring. My coworkers are quite enjoyable. Even though we're distributed across the company on various teams, I enjoy the company of my fellow analysts at our regular meetings or when we meet for lunch.

Twenty-twelve was quite the eventful year for us. We got married, moved across the country, and I started a new job. All of these are big things on the "Most Stressful Events of Your Life" list, but it's worked out pretty well. We've had to downsize our living space in order to fit into the San Francisco Bay Area residential market, going from a small house to an apartment. Our apartment is nice and so is the complex, but it's definitely just a temporary arrangement.

Being married has been interesting. It hasn't changed who we are or how we interact in any major way, but it's this slight thing that's always there. The plain band on my finger is always there to remind me that there is this amazing woman who loves me, even when I make mistakes. She's wonderful and sometimes I wonder why she puts up with my dumb butt.

This blog has stood neglected due to my lack of time. To be honest much of my blogging I would do at work at my last job because I had the spare time, which I don't now. This just means that I'm not so bored at work any more, which is a good thing.

I take the BART to and from work each day, with roughly one hour and fifteen minutes of total commute each way. I usually spend my time on the train playing a game on my Vita. My current obsession has been Patapon. I used to walk to and from the BART stations but it's been colder so that's fallen a bit to the wayside and I've been taking the shuttle from the station to work. The temperatures here don't vary as much as they did in Arkansas, especially in San Francisco. There's very little temperature variance there. The ocean and the Bay have quite a stabilizing effect.

Life is good. I like where I am and the direction I'm heading. My work is great, I'm learning so many useful skills and people really seem to value my opinions (which is always nice). I'm positive about the future. I think 2013 is going to be a good year.