Monday, October 20, 2014

Final Fantasy VI

As Alexa Corriea pointed on out twitter:
I was 8 years old at the time, but I can't for the life of me remember when I actually got the game. I don't remember a lot of things, it turns out. I do, however, remember playing the game quite a bit. To say that Final Fantasy VI is a big part of development as a gamer would be an understatement. It, EarthBound, Chrono Trigger, Secret of Mana, and Illusion of Gaia combined to form a quintet of RPGs that were and are very important to me to this day.

FF6 struck me with it's story and character, filled with twists and turns, a large cast of interesting characters, and brilliant villains. I loved the characters so much that I used to pretend that I was a member of their team, hanging out with them on board the Blackjack or the Falcon. Part of that was because I was a fairly solitary kid. I didn't have very many friends, nor did I hang out with them very much outside of school. It's not that I was a reject, I just didn't try to make friends or try to hang out with them. I was very happy in my world and in the worlds of the games that I played. I suppose I was also pretty publicly a nerd, and I didn't really know how to talk to people, and I had trouble making eye contact, but I was doing alright by it.

I think part of the reason the characters are so strong is because you meet them in the World of Balance, regain them in the World of Ruin and find out how they react to this disaster, and then dive into their past and history in their optional sidequest. I miss sidequests, I think they're really important to developing the game world's story.

My brother and I both played FF6 a lot, even together. We watched each other play and offered tips. We didn't play too many video games together once we got much older. We drifted apart, he got his own room, we stopped playing as many games together. Eventually he'd start misbehaving, doing drugs, causing trouble, and making family life difficult. Things have gotten better but we're still distant and I still reminisce about those old days when we'd play together.

I played Final Fantasy VI over and over all the way up and through junior high, periodically dipping back into the game for nostalgia trips when I felt I needed them. I moved on to other things in high school, when we got a PlayStation 2 and FF9, FFX, and Kingdom Hearts were the RPGs that I played. When art went off to college and wanted to take the SNES with him, I obliged. When he dropped out after a semester and moved back home. A lot of the SNES games didn't come back, particularly the RPGs that I loved, that we had bonded over. When I questioned him about where they had gone, he said that he had loaned them to people and hadn't got them back. I pressed him about getting them back, but he always pushed it off. I not think that he probably sold them. I can only imagine what he did with the money. I've never really talked with him about this. We don't ever talk about that time in our family's life.

I almost always played with Sabin and Edgar in my party. I don't know if it's because they're strong or because I just wanted to see brothers that were distant yet loved each other.

I started playing piano in 5th grade and my former kindergarten teacher was my first instructor. I took lessons all the way through high school. For a brief period I took lessons from a jazz piano instructor. Once while there for a lesson I saw a book of piano music that belonged to one of her students. It was a collection of sheet music for FF6's soundtrack. I asked begged her to ask her student where he got the music and when she found out and told me, I ordered a copy immediately. Once I started college I didn't do a good job of staying in practice. Pretty much the only music I would keep playing was music from my FF6 collection and a collection of songs from across all the Final Fantasy games.

The music of FF6 is very deeply ingrained in me. Sometimes I feel that the way that I can best express emotion is by playing its music on the piano. I've purchased it's soundtrack in various forms and arrangements time and time again. It takes me back in time, helps me remember, reminds me of friends I haven't spoken to in a long time. It takes me back to when I was playing the game growing up. FF6 is so important to me. It's hard to say that my life would be different had it never existed, but as it stands I find it hard to imagine it'd be the same.

Sunday, November 17, 2013

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.

Wednesday, October 9, 2013

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.

Friday, June 7, 2013

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).

EngagementPlayer Count% played today# played today% DAU in bucket
1 Days1,000,00014.29%142,8573.57%
2 Days1,000,00028.57%285,7147.14%
3 Days1,000,00042.86%428,57110.71%
4 Days1,000,00057.14%571,42914.29%
5 Days1,000,00071.43%714,28617.86%
6 Days1,000,00085.71%857,14321.43%
7 Days1,000,000100%1,000,00025%

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.

Thursday, June 6, 2013

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.

Wednesday, June 5, 2013

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?

Tuesday, June 4, 2013

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.

Monday, June 3, 2013

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.

Thursday, January 24, 2013

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? 

Wednesday, January 23, 2013

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.


1 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).
2 count() and many other similar functions that summarize data are called "aggregate functions".