This SQL exercise is a part of a section dedicated for data analysts , providing data analysis tasks that require both business understanding and technical skills.

 

About this Exercise

From shooters to strategy, builders to bingo. Facebook platform allows users from all around the world to play, watch, and share their games.
The following dataset simulates some of data stored by Facebook, and includes the following tables :

  • facebook_games – lists the available games
  • facebook_members – contains the different members / Facebook users
  • friendships – lists the connections between the various Facebook members
  • games_and_players – displays the games each player is playing

 

Diagram



 

Download

The database creation script can be downloaded using the following link

 

Exercises

The following exercises are constructed in ascending difficulty order. First ones are pretty easy, made for warm-up. The advanced exercises, on the other hand, can be quite challenging.

 
Basic Analysis

  1. How many members have joined facebook on 2013?
  2. How many facebook members have less than three friends?
  3. How many facebook members have no friends?
  4. List the number of games for each game category

 
Advanced Analysis

  1. What is the average amount_spent by each player id?
  2. What is the average amount_spent by each gender in France?
  3. How many facebook users haven’t registered to any game?
  4. What is the most profitable game?
  5. What is the most profitable game in France?
  6. What is the most favorite game category (by the number of users)?
  7. List Tom Stewart’s friends
  8. List the games Tom Stewart’s friends are playing
  9. Create a distinct list of all games played by Tom Stewart and his friends
  10. List all 2nd degree friends of Jami Whatson

 

Solutions