Analyzing OngoingWorlds posts

The previous post used Scrapy to extract post data from the website OngoingWorlds. Here are a few conclusions from that spider crawl:

I collected the game ID, post ID and date/time for each post from the play-by-email roleplaying community OngoingWorlds into an Sqlite3 database. Even with this very limited dataset, some interesting queries can be run:

Most popular games (by number of posts)

SELECT game_id, COUNT(*) FROM post GROUP BY game_id ORDER BY COUNT(*) DESC LIMIT 10;

Rank	Game	Total posts
1	Blue Dwarf	15040
2	Hero High	3453
3	2778 A.D.	2894
4	The Land of Ecilith	2276
5	The Avengers~Lower Levels	2111
6	Heroes Association	1288
7	Hunted	1265
8	Circle of Nine	1176
9	Fairy Tail ZERO	1125
10	MLP fans!	1118

Most popular games (per hour of the day)

SELECT
game_id AS GameID,
  (
  SELECT strftime("%H", timestamp) AS Hour
  FROM post AS inner
  WHERE inner.game_id = outer.game_id
  GROUP BY Hour
  ORDER BY COUNT(*) DESC
  LIMIT 1) AS MostPopularHour,
COUNT(*) TotalPosts
FROM post AS outer
GROUP BY GameID
ORDER BY MostPopularHour, TotalPosts DESC

For easier viewing I exported the result to a CSV spreadsheet, as follows:

sqlite3 -header -csv results.db 'SELECT game_id AS GameID, (SELECT strftime("%H", timestamp) AS Hour FROM post AS inner WHERE inner.game_id = outer.game_id GROUP BY Hour ORDER BY COUNT(*) DESC LIMIT 1) AS MostPopularHour, COUNT(*) TotalPosts FROM post AS outer GROUP BY GameID ORDER BY MostPopularHour, TotalPosts DESC' > results-agg.csv

Hour of the day (CEST)	Game
0	The Elite Club
1	The Hotel
2	Hero High
3	The Avengers~Lower Levels
4	The Land of Ecilith
5	Redwill Home for Unusual and Odd Children
6	Cure
7	Advanced
8	Battle of the Bands
9	Vale of Shadows
10	Pokemon Azure
11	The open trail
12	BRAIN GAMES: Raido Ravens
13	Bloody Gifts
14	Circle of Nine
15	Academy for Super Humans
16	MLP fans!
17	Gakuen Statalia
18	The Verse - Other Adventures in the Firefly Universe
19	Magic Agents
20	Blue Dwarf
21	Another West
22	Heroes Association
23	Day After, Hero’s Past

Most popular posting hours (CEST)