JavaScript is required to use Bungie.net

#Community

originally posted in:BungieNetPlatform
Edited by dazarobbo: 1/16/2014 2:53:39 AM
26

A Year of Topics: 2013 - 2014

Last year I decided to have a look at the topics that were being made on bungie.net. At first I just looked at [url=http://www.bungie.net/7_The-27652-topics-since-Bungienext-launched-Update-/en/Groups/Post?id=60237224&groupId=39966#referred-]the first 97 days after bungie.next launched[/url], and then [url=http://www.bungie.net/7_Number-of-topics-per-day-has-dropped-but-is-increa/en/Groups/Post?id=60748719&groupId=39966#referred-]from a year between mid-July 2012 to 2013[/url]. Now that we've had a year of bungie.next, I thought I might do this kind of thing again and see what's been happening. How I got the data (tech lingo in here): [spoiler]I fired up cURL via PHP in CLI mode and made requests to GetTopicsPaged starting at page index 0, sorted by most recent with no tag filter, and continued to do so until the API responded with no more pages (!Response.hasMore). I added a header to request GZipped responses to reduce network data size then decompressed locally (to around about 321MB). All up it took around about 3 hours to grab 5255 responses. However, because of this "lag time", it's impossible to take a snapshot of the state of the database because new topics would have been made which may have pushed topics off the indexable pages (although it wouldn't have been too many). Responses were then imported into MySQL which is where I'm querying the data from (I was considering having a play with Hadoop though...).[/spoiler] [b][u]However, a quick note before I begin[/u][/b]. Because of the way I've imported data into my local database and how the forums are described with metadata via the responses I've gathered, they might not be 100% accurate. I have also imported user, group, and post data which I picked up along the way, which means it is incomplete, so take any of the information below with a grain of salt. Also, all dates below are formatted and calculated according to UTC. Looked at topic data that began at [url=http://www.bungie.net/en/Forum/Post?id=5298298]5298298[/url] and ended at [url=http://www.bungie.net/en/Forum/Post?id=63292213]63292213[/url].[spoiler]select concat( 'Looked at topic data that began at [url=http://www.bungie.net/en/Forum/Post?id=', min(p.postId), ']', min(p.postId), '[/url] ', 'and ended at [url=http://www.bungie.net/en/Forum/Post?id=', max(p.postId), ']', max(p.postId), '[/url].' ) from bungie.alltopics p;[/spoiler] Total number of topics: 131,360[spoiler]select concat('Total number of topics: ', format(count(*), 0)) from bungie.alltopics;[/spoiler] Total number of related topics: 4,467[spoiler]select count(*) from bungie.related_topics;[/spoiler] Total number of unique tags (includes group tags): 26,851[spoiler]select count(*) from bungie.tags;[/spoiler] Total number of unique topic tags (number from all tags used on topics): 26,284[spoiler]select count(*) from ( select 0 from bungie.tags t join bungie.post_tags pt on pt.tagId = t.tagId group by pt.tagId ) r;[/spoiler] Total number of tags on all topics (ie. topic A has #foo and topic B has #foo): 239,115[spoiler]select count(*) from bungie.tags t join bungie.post_tags pt on pt.tagId = t.tagId;[/spoiler] Total number of tags on all groups (number from all tags used on groups): 1,675[spoiler]select count(*) from ( select 0 from bungie.tags t join bungie.group_tags gt on gt.tagId = t.tagId group by gt.tagId ) r;[/spoiler] Total number of unique group tags (ie. group A has #foo and group B has #foo): 23,546[spoiler]select count(*) from bungie.tags t join bungie.group_tags gt on gt.tagId = t.tagId;[/spoiler] Total number of group topics in the public forum: 7,845[spoiler]SELECT count(*) FROM bungie.posts p where p.groupOwnerId is not null;[/spoiler] Top 20 most frequently used tags on topics:[quote][url=http://www.bungie.net/en/Forum/Topics?tg=#offtopic]#offtopic[/url] - 65,671 [url=http://www.bungie.net/en/Forum/Topics?tg=#destiny]#destiny[/url] - 23,776 [url=http://www.bungie.net/en/Forum/Topics?tg=#gaming]#gaming[/url] - 23,247 [url=http://www.bungie.net/en/Forum/Topics?tg=#flood]#flood[/url] - 8,571 [url=http://www.bungie.net/en/Forum/Topics?tg=#community]#community[/url] - 3,332 [url=http://www.bungie.net/en/Forum/Topics?tg=#support]#support[/url] - 2,677 [url=http://www.bungie.net/en/Forum/Topics?tg=#bungie]#bungie[/url] - 2,024 [url=http://www.bungie.net/en/Forum/Topics?tg=#feedback]#feedback[/url] - 1,379 [url=http://www.bungie.net/en/Forum/Topics?tg=#halo]#halo[/url] - 1,356 [url=http://www.bungie.net/en/Forum/Topics?tg=#theflood]#theflood[/url] - 1,022 [url=http://www.bungie.net/en/Forum/Topics?tg=#music]#music[/url] - 997 [url=http://www.bungie.net/en/Forum/Topics?tg=#politics]#politics[/url] - 946 [url=http://www.bungie.net/en/Forum/Topics?tg=#funny]#funny[/url] - 883 [url=http://www.bungie.net/en/Forum/Topics?tg=#news]#news[/url] - 843 [url=http://www.bungie.net/en/Forum/Topics?tg=#roach]#roach[/url] - 742 [url=http://www.bungie.net/en/Forum/Topics?tg=#weededdragon1]#weededdragon1[/url] - 687 [url=http://www.bungie.net/en/Forum/Topics?tg=#help]#help[/url] - 677 [url=http://www.bungie.net/en/Forum/Topics?tg=#xbox]#xbox[/url] - 670 [url=http://www.bungie.net/en/Forum/Topics?tg=#ps4]#ps4[/url] - 669 [url=http://www.bungie.net/en/Forum/Topics?tg=#xboxone]#xboxone[/url] - 656 [/quote][spoiler]select concat( '[url=http://www.bungie.net/en/Forum/Topics?tg=', lower(t.name), ']', lower(t.name), '[/url] - ', format(count(*), 0) ) from bungie.tags t join bungie.post_tags pt on pt.tagId = t.tagId group by t.tagId order by count(*) desc limit 0, 20;[/spoiler] Top 20 groups posting public topics:[quote][url=http://www.bungie.net/en/Groups/Detail?groupId=18533]TFS The Floods Sanctuary[/url] - 380 [url=http://www.bungie.net/en/Groups/Detail?groupId=18909]Sociopaths United[/url] - 372 [url=http://www.bungie.net/en/Groups/Detail?groupId=16555]Destiny[/url] - 304 [url=http://www.bungie.net/en/Groups/Detail?groupId=39972]The Black Garden[/url] - 246 [url=http://www.bungie.net/en/Groups/Detail?groupId=12337]Sapphire[/url] - 199 [url=http://www.bungie.net/en/Groups/Detail?groupId=40080]Guardian Radio[/url] - 195 [url=http://www.bungie.net/en/Groups/Detail?groupId=16959]Secular Sevens[/url] - 190 [url=http://www.bungie.net/en/Groups/Detail?groupId=39813]Art and Stuff[/url] - 177 [url=http://www.bungie.net/en/Groups/Detail?groupId=19422]Outer Heaven[/url] - 146 [url=http://www.bungie.net/en/Groups/Detail?groupId=19014]Halo Forum[/url] - 130 [url=http://www.bungie.net/en/Groups/Detail?groupId=19291]The Bird People[/url] - 93 [url=http://www.bungie.net/en/Groups/Detail?groupId=62366]Pokémon Central[/url] - 76 [url=http://www.bungie.net/en/Groups/Detail?groupId=40683]FWC Core[/url] - 67 [url=http://www.bungie.net/en/Groups/Detail?groupId=40101]Hunters of Destiny[/url] - 57 [url=http://www.bungie.net/en/Groups/Detail?groupId=61913]Destiny Beta Group [/url] - 49 [url=http://www.bungie.net/en/Groups/Detail?groupId=40961]The Last Assassins[/url] - 48 [url=http://www.bungie.net/en/Groups/Detail?groupId=19034]The Garage[/url] - 46 [url=http://www.bungie.net/en/Groups/Detail?groupId=40142]The Travelers Tavern[/url] - 46 [url=http://www.bungie.net/en/Groups/Detail?groupId=39975]Pure Gaming[/url] - 45 [url=http://www.bungie.net/en/Groups/Detail?groupId=40889]Guardians of Legend[/url] - 44[/quote][spoiler]select concat( '[url=http://www.bungie.net/en/Groups/Detail?groupId=', g.groupId, ']', g.name, '[/url] - ', format(count(*), 0) ) from bungie.posts p join bungie.groups g on g.groupId = p.groupOwnerId where p.groupOwnerId is not null group by p.groupOwnerId order by count(*) desc limit 0, 20;[/spoiler] Number of posts as replies to all topics: 3,035,204[spoiler]select format(sum(p.topicReplyCount), 0) from bungie.alltopics p;[/spoiler] Topic with the most replies: [url=http://www.bungie.net/en/Forum/Post?id=60050775]I'm ending this thread.[/url] - 4,377[spoiler]select concat( '[url=http://www.bungie.net/en/Forum/Post?id=', p.postId, ']', p.subject, '[/url] - ', format(p.topicReplyCount, 0) ) from bungie.alltopics p where p.topicReplyCount = (select max(topicReplyCount) from bungie.alltopics);[/spoiler] Average number of replies a topic has: 23[spoiler]select format(avg(p.topicReplyCount), 0) from bungie.alltopics p;[/spoiler] Most rated topic: [url=http://www.bungie.net/en/Forum/Post?id=59798057]Something is falling towards you. Or are you falling towards it?[/url] - 166 [spoiler]select concat( '[url=http://www.bungie.net/en/Forum/Post?id=', p.postId, ']', p.subject, '[/url] - ', format(p.ratingCount, 0) ) from bungie.alltopics p where p.ratingCount = (select max(ratingCount) from bungie.allTopics);[/spoiler] Average number of ratings a topic receives: 1[spoiler]select format(avg(p.ratingCount), 0) from bungie.alltopics p;[/spoiler] Top 20 days by topics created:[quote]2013-01-11 - 1,231 2013-06-11 - 1,091 2013-06-14 - 708 2013-02-17 - 637 2013-06-12 - 627 2013-07-03 - 619 2013-01-12 - 594 2013-06-13 - 590 2013-06-10 - 583 2013-10-27 - 566 2013-11-15 - 564 2013-02-18 - 561 2013-10-02 - 556 2013-06-17 - 551 2013-06-19 - 536 2013-06-28 - 530 2013-07-09 - 530 2013-06-25 - 528 2013-07-07 - 523 2013-06-20 - 521[/quote][spoiler]select concat( date(t.creationDate), ' - ', format(count(*), 0) ) from bungie.alltopics t group by date(t.creationDate) order by count(*) desc limit 0, 20;[/spoiler] [url=http://www.bungie.net/en/Groups/Post?groupId=39966&id=63315916&path=1]More[/url]. [url=http://www.bungie.net/en/Groups/Post?groupId=39966&id=63315915&path=1]Even more[/url]. [url=http://www.bungie.net/en/Groups/Post?groupId=39966&id=63337870&path=1]Subject line word frequencies[/url].

Posting in language:

 

Play nice. Take a minute to review our Code of Conduct before submitting your post. Cancel Edit Create Fireteam Post

View Entire Topic
  • This seemed like a ton of work. Thanks for taking the time to do it. [quote]Top 20 people who post too many topics:[/quote] Interesting. It obviously does not take their trolls accounts into the equation.

    Posting in language:

     

    Play nice. Take a minute to review our Code of Conduct before submitting your post. Cancel Edit Create Fireteam Post

You are not allowed to view this content.
;
preload icon
preload icon
preload icon