On Cycles

Flower - a PS3 game that's been upgraded for PS4

I got my Playstation 4 the other day and it was just a little over seven years ago that the Playstation 3 came out. At the time I was a junior in college and had snagged a reservation by camping out in front of my local Gamestop with about 5 or 6 other people, including two of my college suitemates. It was a fun experience but definitely not something I plan on doing again, especially since I did the same for the Wii two days later. I had a lot of trouble staying awake the day after camping out for the Wii.

Thinking back on who/where I was at that time and all that's happened since then has been really interesting. It's easy for me to think of the PS3 launch as having been "not long ago" but when I think of everything that's happened it starts to seem more like "really long ago". I've graduated twice, had 5/6 different jobs, lived in 7 different places, gotten married, moved across the country, been to Blizzcon 3 times, and so much more. Seven years ago I had never played WoW, my parents were still thinking they'd retire to the retire to the country, and I'd never had a cat. And despite being "liberal", I was completely ignorant about social justice issues (which I hear is pretty typical).

This is all probably not surprising since it was over 25% of my life ago, but it's really easy to forget how much can happen in a period of time that seems so short.

It's kind of weird to only be thinking about this because a new video game console came out but I think it totally makes sense. As a gamer, these consoles and the experiences I have on them are not only significant to me but they also form the background of my life experiences. When I think about a game or a console I don't just remember the things that happened in the game but also the who and where I was and the what was happening in my life at the time I was playing. For example if I think about Kingdom Hearts or Final Fantasy X I think about talking with my friend in the high school parking lot. If I think about Metal Gear Solid 4 or Mega Man 9 I remember living in my parent's house after college and in the first couple months on grad school. Journey wan't just a fantastic gaming experience but I also remember having the front door open to the house we were renting at the time and waiting for Sarah to get home from work. With an MMO it's possible to have distinct attachments to expansions because of the real life experiences that were happening during each of them.

I think this is why we can get nostalgic for old games even if they aren't good, even if newer games in that series are "better". By starting up that game and playing it you can transport yourself back in time to when you were first playing it. Sonic games will always be tied to when my brother and I shared a room when we were very young, before he moved out into his own room and we began to drift apart. SSX reminds me of the Christmas when we had an ice storm in Arkansas and we had to stay with a family friend until the power came back.

It's said that smell is the sense that has the strongest tie to memory. Have you ever smelled a food and just been transported back to some great childhood memory of eating something tasty? Perhaps this is because smell is often used to identify things that might be poisonous or otherwise bad for us if we were to try and eat them. But wouldn't it make sense that action has a stronger tie to memory. Playing an old game can not only be fun but it has the ability to take you back in time.

This console launch has me remembering who I was in college, and thinking about everything that's happened in the interim. Much has happened and I've really grown a lot as a person in the mean time. I've met a lot of people and done a lot of great things. A console cycle can sometimes feel short, but a lot can actually happened. I can't help but wonder what's going to happen between now and the next generation of consoles.

Board Games!

This is actually an old picture, it's gotten much worse.

Anyone who follows me on twitter probably has noticed that I've been talking about tabletop games quite a bit lately. In the past couple of months Sarah and I have added significantly to our collection. For an idea of what I'm talking about, look to the right. It's gotten much worse since then.

We have 56 games, in total. Not all of them are in that picture, because some of them are actually behind the others. For example, you can see Munchkin there but we actually have other versions of Munchkin, they're just stashed behind Carcassonne, Ghost Stories, and Yahtzee.

There's quite a variety there, too. You see classic games like Risk and Monopoly, but there's plenty of other games too. There's the cooperative fire-fighting simulator Flash Point: Fire Rescue. There's the popular Eurogame about connecting train routes Ticket to Ride. There's the literally-only-sixteen-cards get-your-love-letter-to-the-princess simulator Love Letter (a truly excellent game). There's also the dexterity-challenging magnet-balancing game Polarity.

This might seem like a sudden shift for me but it's really a natural extension of a trend that's been going on for roughly a decade.

Forbidden Island, a cooperative game where you play as treasure hunters trying
to get four relics from an island before it sinks.

Seeking new things
I can't really say that I know what caused it. Maybe it was because of my friend the next room over my freshman year of college. Maybe it's because that was the year Katamari Damacy was released. Maybe it's because that was the year that the PSP and the DS were released, and I hadn't really been into portable gaming since the original Pokemon some long time prior. Or possibly it's because of all these things. Ever since that year, however, I've constantly been seeking new gaming experiences. I would rather play small, mediocre, yet novel games as opposed to a full-price game that's well polished yet doesn't bring much new to the table. In the past this has meant playing mobile/handheld games and download-only games, but now it's extending to tabletop games.

Tabletop games really offer a lot of things that video games don't.

Meat Space Nine
Tabletop games are all about playing with your friends right around you. You can see and talk to each other in ways that are hampered by communicating over headset or having to share real estate on a screen. This isn't to say that there aren't great video games that you can play with your friends all on the same couch, Smash Bros. and Towerfall and great examples of such, but this is what board games are all about. It is their jam.

Dungeons & Dragons: Castle Ravenloft

No need for dexterity
Tabletop games are almost always turn-based as well (Escape: The Curse of the Temple notwithstanding). Many people can't play competitive video games because of a reliance on manual dexterity, fast reaction times, having to juggle a lot of information without time to think, or they can get nauseous in the case of a first-person game. This gives tabletop games an extra level of accessibility that video games don't have.

Hackability
Most video games go to great lengths to keep you from playing them in ways that the developers don't intend. You can't make your own rules, except on a social level ("Nobody's allowed to pick Oddjob, okay!?"). You can't add and remove components. You can't do anything, usually. Tabletop games literally cannot avoid this. Don't want to play with a particular rule? GONE. Want to add your own class to the roster of characters? DO IT. Want to add a rule or more content to the game? EASY. Think something is unbalanced? CHANGE IT. They're literally powerless to stop you. This makes them great for budding game designers to experiment with how changing rules affects the gameplay or for hobbyist to make something that they love even better. If I think that Smash Bros isn't balanced well it takes a ton of effort to make it more balanced. If I think a Dungeons and Dragons class is unbalanced, that's easy to fix.

More apparent mathiness
One thing that really appeals to me in particular is, in addition to their hackability, is that their turn-based nature makes it easier to see the math behind the game and optimize your gameplay. For example, in Ticket to Ride, you get 1 point for a 1 train section, 2 for 2, 4 for 3, 7 for 4, 10 for 5 and 15 for 6. Here you can easily see that you get more points per train from doing longer routes and should try and do those if possible. This advantage becomes even more clear when you realize that by playing trains there is an opportunity cost in that any turn spent playing trains is a turn in which you aren't drawing cards. So you could spent two turns playing 3 trains each and be down 6 cards and only have 8 points or you could spend two turns, 1 playing 6 trains and 1 drawing cards, and have 15 points and only be down 4 cards.

I have by no means given up on video games. I still love and play those. Most recently I've been playing a lot of Hearthstone and Spelunky.

Your ideas about WoW players are wrong - engagement bias

Not that kind of engagement

The people you see when you log into WoW, no matter which server you play on, do not comprise a representative sample of the people who play WoW. 

That sentence might seem a bit surprising but I can assure you that it's 100% true. The primary culprit here is engagement bias, which is something you have to consider when you're analyzing a game-as-a-service, like WoW. Suppose 7 million people play WoW in a given week. Let's look at them by how engaged they hypothetically are (as measured by how many days they played that week).

Engagement	Player Count	% played today	# played today	% DAU in bucket
1 Days	1,000,000	14.29%	142,857	3.57%
2 Days	1,000,000	28.57%	285,714	7.14%
3 Days	1,000,000	42.86%	428,571	10.71%
4 Days	1,000,000	57.14%	571,429	14.29%
5 Days	1,000,000	71.43%	714,286	17.86%
6 Days	1,000,000	85.71%	857,143	21.43%
7 Days	1,000,000	100%	1,000,000	25%

Here we see that if you look at the people who play on a particular day (DAU - Daily Active User), there is a distinct bias towards users who have a higher weekly engagement. Side note: players who play in a given week are called WAU. Even though the WAU are evenly distributed among the engagement buckets, the DAU are heavily skewed towards the highly engaged. Then again, WAU isn't how Blizzard likely defines 'player' for WoW, they likely use subscribers as the definition of the player, since that's how they get their money and the $15 a low-engaged player gives them is the same as the $15 a heroic raider sends them.

What this means is that the people that you see every day in the game aren't really a good representation of WoW's subscriber base. People aren't as engaged with the game as they appear to be. From a development and design standpoint, the highly-engaged users are the least likely to let their subscription lapse, so features are often made to appeal to the casual crowd/make casual players more engaged. If you look at the history of WoW this is what you'll see. Even heroic raiding was oriented around this because it allowed them to make regular raiding easier and more accessible to the casual player.

This is just one example of engagement bias, which is a recurring problem in user-centric data analysis and therefore is a recurring problem in the games-as-a-service industry. Engagement bias is the phenomenon that more active users are often more likely to be counter/sampled.

Back when I was working on analyzing the results of my 2011 WoW Survey, one question I wanted to answer was "What are the correlations between classes?" meaning that I wanted to know which classes a player was more or less likely to play if they played another class. For example, "Are people who play Warlocks more or less likely to play a Death Knight than someone who plays a Rogue?"

Suppose that the average respondent to my survey listed two different classes among the ones that they play. At the time, this means that roughly 20% of respondents played any particular class (class representation actually varied wildly). When I pulled the percent of Warlock players that ALSO played Paladins I found a much higher number, 40% or greater. This baffled me for a long time. For each combination of classes, this same thing happened, the percentage of X players that also played Y was higher than the percent of the general population that played class Y.

Why was this?

Among the people that I surveyed, they varied widely among the number of characters they played. Some people only listed 1 or 2 characters, some listed 10 or more. When I selected all the players who played a Warlock, the highly-engaged players (those with more characters) were more likely to be in that group than the low-engaged players (those with few characters). So the group of Warlock players had, on average, more characters than the general population. So when I calculated how many of them also played Paladins, I received a much higher number than with the general population.

Of course, there was something else that would skew the results of my analysis. I got my data not via the actual numbers but by getting survey results that mainly came from MMO-Champion. Since these are people that are participating in the WoW community, they are going to tend to be more engaged than the general WoW playing population.

Engagement bias is just one of the many things you have to keep in mind when you're analyzing game players. For example, during my WoW survey, I also found that MMO-Champion users tend to skew more male than respondents from other sources that I've used. For this reason and more when I was doing my analysis I was careful to make sure to state that the numbers were not to be taken as absolute facts, but as being "directional", meaning that it'll likely indicate what the differences between two groups or what may be more or less popular for a group even if the exact values aren't true for the overall population.

This is just one of the slew of problems that you run into when doing user-facing data analysis, something which I'll be covering in a later post.

Wildstar just might get me to switch from WoW

I know I just got back into WoW but WildStar looks really phenomenal. WildStar is a beautiful looking MMO that's currently in development. It looks absolutely fantastics. The characters look very expressive and the environments look fantastic. It's currently in beta right now and I'm really enjoying seeing how it turns out. There are two factions, the Dominion and the Exiles. Right now it looks like there are currently 6 classes, of which four have been revealed and they all look really cool.

One of WildStar's features is one that I wish WoW had, player housing. In WildStar, your house floats on a rock in the sky and is highly customizable and interactive. There are several different house models, it can get attacked, your friends can visit it, and you can return to it from anywhere at anytime. This will be a really great place for me to log in and log out so I don't log in and have the first thing I see be a mass of people. People stress me out, and having this space to get into the game will be really great.

Another really cool looking feature is Paths. Just like WoW, WildStar will have races and classes but in addition to that it will have Paths. Paths are all about the content that you like to do. If you like to fight, be a soldier. If you like seeing all the sights, be an explorer. If you like to learn all the lore, then be a scientist. And if you like to craft things, then be a settler. Soldiers get more combat content, scientists get missions to examine objects, explorers head to remote areas, and settlers build building and other things. Any race/class can be any one of the classes and having them work together provide great benefits to a group.

There are tons of other things that look great about Wildstar, like movement. It not only has jumping, but double-jumping. It also has rolling and dashing. It looks like it's currently targeted for release later this year.

The Next-Gen Systems: The PS4, XBox One, and WiiU

Controller comparison not to scale. The WiiU pad is HUGE

All the shots have been fired now. Microsoft, Sony, and Nintendo have each either debuted or have released their next-generation consoles. The WiiU has the lead this generation, but unfortunately hasn't garnered the user base advantage that the XBox 360 did for the current generation. There are some common themes among the consoles: second-screens, motion control, and social.

Nintendo has embraced the second-screen the most with its inclusion in the default controller for the WiiU. Having already had some experience using it I can say that it allows for some very interesting designs. Nintendo Land really shows off some of its potential, but other games like New Super Mario Bros. U don't do much interesting with it. Though the simplest use of it is as a duplication of the main screen so you can play games TV-less, which I've already found to be great.

The PS4 is doing its second-screen implementation by making Remote Play with the Vita a core feature of the console. Remote Play is one of my favorite features of the PS3 (and something I was really hoping would be a core part of the Vita) and I'm very excited that it will be a more core part of the PS4. One advantage for remote play is that it can work non-locally, allowing you to stream content over the internet and Sony is requiring that Remote Play be worked into each PS4 game.

The XBox One's second-screen comes in the form of SmartGlass, which we don't really know much about but should give applications additional content on a paired tablet device w/ SmartGlass installed.

Each system's motion control is a continuation of what they did for the last generation. The Wii Remote motion control technology (but not the form factor) has been included in the WiiU Gamepad. It's also compatible with the Wii Remotes from the Wii. The PS4 controller has more advanced motion controls included in it than the PS3's Sixaxis had, as well as a motion controller-esque light on the front of the controller. They've also included a touch pad on the controller and it's compatible with the same motion controllers from the PS3. The XBox One is including a more advanced Kinect that comes with the console by default which must be plugged in at all time.

The last shared feature among the consoles is social networking/sharing. Friends were a major addition to the current generation but social networking didn't really explode until after they were all out. The 360 came out in November 2005 and the PS3 and Wii came out in November 2006. Facebook didn't become open until September 2006 and Twitter started in July 2006 but didn't take off until much later. Social networks have become firmly embroiled in our everyday lives now and as such are becoming embedded in our next consoles. Facebook and Twitter are already in the Vita, which combined with the ability to screenshots makes it a great combination.

Nintendo has set up its own social network for the WiiU that's based around various boards for each game, called the Miiverse, users can post screenshots and drawings to the Miiverse and ask for help from each other. The PS4 is getting close with facebook as well allowing users to upload videos of gameplay to various sites or even stream gameplay through UStream. The XBox One will also allow users to upload videos of gameplay to the internet and will no doubt have integrated social network features. They haven't been discussed at length yet, however.

I've very excited for all the consoles. I'm most excited for the PS4 (I'll admit to fanboyism), but I'm expecting good things from all of them. The video uploading and streaming/sharing features look really intriguing and the second screen functionality I've experienced with the WiiU and with Remote Play for the PS3 give me great hope for how that's going to turn out on these consoles.

With E3 coming up I'm really excited for what we're going to be seeing, more so than I've been in years. I'm hoping to hear more about the gaming features of the XBox One from Microsoft and I'm hoping to see more games from Sony and Nintendo. Which features and consoles are you most excited for?

Getting Back Into WoW After a Long Break

The good old days

It's been a long time since I've played WoW (and it's by no means the first time this has happened). So long, in fact, that when my account lapsed I considered not renewing it. Mists has been a hard expansion for me, though I don't know how much of that has to do with Mists itself and how much of it is a lingering effect of Cataclysm. That being said, I still only have one level 90 and my second highest character is level 86.

Coming back to WoW is hard. Logging into old mains means coming back to bags full of items which you don't remember what they're for, abilities which might not (or definitely will not, depending on how long you've been away) work the same way they used to, you're behind on content, and you're definitely rusty, potentially having lost some of the muscle memory of how to play your class. My level 90 Priest has all these things in her bags and all these abilities I don't remember how to use and there are all these dungeons and scenarios I have no idea how to run. I'm worried that if I tried to run anything I'd be terrible and everybody would hate me. My level 86 Druid is primarily Guardian but the class/spec changed so much in Mists that I don't even know how to play him anymore.

WoW isn't alone in this issue. Any game-as-a-service that updates its content and systems is going to have this problem with returning players having trouble catching up. They've certainly worked on making coming back into the game easier for players over the years. Simplifying the talent and abilities systems have done wonders for that. But the baggage of items, quests, and being behind on content is a harder problem to solve. I could just vendor the items but that's sub-optimal in terms of profitability and my brain tells me, "No! Don't do it!"

The game is complicated now. It may be (debatably) easier now than it was in Vanilla but it's far more complicated. With daily quests, normal and heroic scenarios, normal and heroic dungeons, normal and heroic raids, justice and valor points, LFR, and reputations (some of which are gated) PVE is incredibly more complicated than it has ever been. PVP has evolved significantly from what it was in Vanilla as well.

So I've been eschewing my high-level characters since coming back to WoW and have been leveling some classes I haven't leveled to the level-cap before. I've got a Draenei Prot Warrior (yet ANOTHER attempt at doing so), a Human Affliction Warlock, and a Night Elf Assassination Rogue. None of them are level 30 or above yet but I've really been enjoying the experience. Starting a new character lets me forget about all the things that have changed that keep me from playing the characters that are at higher levels. It lets me forget that things used to be different and that I don't know how the game works anymore.

I haven't really leveled a pure DPS class before, and leveling these has so far been really fun. I hope that this will help me get back into the game and really get back into endgame activities, which I haven't done since early Cataclysm. If you're having trouble getting into WoW lately, or haven't played in a long time, I highly recommend rolling a new character, it's very freeing and I haven't lapsed as quickly as I have in the past. I'm really looking forward to what these new characters will bring me.

3 features I wish WoW had

A little patch of home

WoW is a pretty old game, and that's given it plenty of time to develop many features. There are, however, tons of things that people still want in the game. For a long time this was things like race changes, server transfers, and transmog, all of which we have been given. One thing that people have been wanting for a long time has been private areas, such as player or guild housing.

Housing is something that I would love to see in WoW. I'd really like to see it done right. The house in Halfhill doesn't fit what I really want. I would love it if we could each have our own private housing but it'd be part of a guild neighborhood. The guild neighborhood could have shared projects where the group could work together to add things like a bar, profession areas, things like a moonwell. It would then make a great personal gold and time sink but also a group time sink. It would also be great at giving a group identity and group goals. Maybe if they had player housing there'd be fewer people having RP sex in low-level inns.

The second thing I'd really like to see is the ability to mail items to my bank. One really annoying thing when questing is running out of bag space because there are too many things in your bags that you're saving for the AH or professions. If I could mail items to my bank then I'd be able to alleviate that problem without having access to my bank, which is something they no probably don't want us to have.

The last item is most concerning to me as part of the community. I've made tons outside of the game that also happen to play the game. However, we often can't play with each other unless we go out of our way to level a character of the faction opposite the one that we normally play. I'd love to be able to run dungeons and do raids with Real ID friends. I understand that multi-faction dungeons/raiding could break the immersion a bit but it would be very loved feature by the WoW community.

I can't but wonder if we'll ever see features like these.

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.