Thief of Catan¶
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
-- ...