Games by Year

Time for another project! This checkpoint puts the functions and the summaries of the last two lessons to work, together with everything you learned before them. As in the first checkpoint, the exercises say what the result should be, and how you write the query is up to you. And as there, each exercise also runs your query on other data, so the numbers have to come from the query, not from the tables as you see them now.

The project. In January, Pixel Shop turns three, and the manager is preparing a short talk for the birthday party: Pixel Shop in numbers. Over three exercises, you'll work out the numbers for its three slides: the shop's games, what customers think of them, and the shop's busiest months. As before, the editor starts with the queries of the previous step, and you add the new one below them.

The task. The first slide is about the catalog: how many games came out in each year, and what they cost on average. Show the year of release as year, the number of games as game_count and their average price, rounded to one decimal place, as average_price, from the oldest year. The games that aren't out yet make a group of their own, which says coming soon instead of a year and comes last:

A tip: rows can be grouped by an expression, as you saw with strftime() in the lesson on grouping. And think about what strftime() gives for a game without a release date.