google-bigquerygoogle-cloudsql

Thief of Catan

Ben Gorman

Ben Gorman

Life's a garden. Dig it.

Challenge

You recently developed a new board game similar to Settlers of Catan, but you're having trouble promoting it. You decide to scrape data from an online tournament site in an effort to generate a list of prospective buyers for your game. At the end of this process, you have the following two tables:

tournaments
| game              | player_ids |
|:------------------|:-----------|
| Settlers of Catan | 4,7,6,3    |
| Risk              | 3,1,8,9    |
| Settlers of Catan | 3,12,9,7   |
| Monopoly          | 4,12,2,3   |
| Monopoly          | 11,4,3,7   |
| Monopoly          | 4,9,5,10   |
| Risk              | 8,5,2,1    |
| Settlers of Catan | 8,4,7,2    |
players
| id | name     |
|---:|:---------|
|  1 | Willow   |
|  2 | Arya     |
|  3 | Isabelle |
|  4 | Julian   |
|  5 | Jayden   |
|  6 | Madelyn  |
|  7 | Lola     |
|  8 | Kai      |
|  9 | Leilani  |
| 10 | Gabriel  |
| 11 | Vincent  |
| 12 | Rowan    |

Your target market is anyone who has played Settlers of Catan. Idenitfy those people.

Starter code

WITH
 
tournaments AS (
  SELECT "Settlers of Catan" AS game, "4,7,6,3" AS player_ids
  UNION ALL SELECT "Risk" AS game, "3,1,8,9" AS player_ids
  UNION ALL SELECT "Settlers of Catan" AS game, "3,12,9,7" AS player_ids
  UNION ALL SELECT "Monopoly" AS game, "4,12,2,3" AS player_ids
  UNION ALL SELECT "Monopoly" AS game, "11,4,3,7" AS player_ids
  UNION ALL SELECT "Monopoly" AS game, "4,9,5,10" AS player_ids
  UNION ALL SELECT "Risk" AS game, "8,5,2,1" AS player_ids
  UNION ALL SELECT "Settlers of Catan" AS game, "8,4,7,2" AS player_ids
),
 
players AS (
  SELECT 1 AS id, "Willow" as name
  UNION ALL SELECT 2 AS id, "Arya" as name
  UNION ALL SELECT 3 AS id, "Isabelle" as name
  UNION ALL SELECT 4 AS id, "Julian" as name
  UNION ALL SELECT 5 AS id, "Jayden" as name
  UNION ALL SELECT 6 AS id, "Madelyn" as name
  UNION ALL SELECT 7 AS id, "Lola" as name
  UNION ALL SELECT 8 AS id, "Kai" as name
  UNION ALL SELECT 9 AS id, "Leilani" as name
  UNION ALL SELECT 10 AS id, "Gabriel" as name
  UNION ALL SELECT 11 AS id, "Vincent" as name
  UNION ALL SELECT 12 AS id, "Rowan" as name
)
 
-- Your code here
-- ...

Solution

This content is gated

Subscribe to one of the products below to gain access