JavaScript is required to use Bungie.net

Community
27
dazarobbo

dazarobbo

Edited: 1/16/2014 2:53:39 AM
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].

Edit Preview Cancel

  • 1
    A Wild Soffish
  • 5
    dazarobbo

    dazarobbo

    1/13/2014 10:18:55 AM Permalink
    Number of topics with media (url, video, image): 31,689[spoiler]select format(count(*), 0) from bungie.alltopics p where length(p.urlLinkOrImage) > 0;[/spoiler] Image formats used in OP's posts: [quote].jpg - 67,606 .png - 15,071 .gif - 7,735 .jpeg - 1,197 .bmp - 28[/quote][spoiler]create temporary table if not exists bungie.image_formats( imgFormat varchar(10) ); insert into image_formats values('.jpg'); insert into image_formats values('.png'); insert into image_formats values('.gif'); insert into image_formats values('.jpeg'); insert into image_formats values('.bmp'); select concat(img.imgFormat, ' - ', format(count(*), 0)) from bungie.alltopics p join image_formats img where p.urlLinkOrImage like concat('%', img.imgFormat, '%') group by img.imgFormat order by count(*) desc;[/spoiler] Top 20 people who post too many topics: [quote][url=http://www.bungie.net/en/View/Profile/index?mid=106460]Onion Beetle [/url] - 1,550 [url=http://www.bungie.net/en/View/Profile/index?mid=3367840]Big Boss[/url] - 1,417 [url=http://www.bungie.net/en/View/Profile/index?mid=23356]MyNameIsCharlie[/url] - 1,172 [url=http://www.bungie.net/en/View/Profile/index?mid=423038]TopWargamer[/url] - 1,089 [url=http://www.bungie.net/en/View/Profile/index?mid=324961]BADMAGIK[/url] - 855 [url=http://www.bungie.net/en/View/Profile/index?mid=744314]Gojira[/url] - 710 [url=http://www.bungie.net/en/View/Profile/index?mid=59027]WeededDragon[/url] - 703 [url=http://www.bungie.net/en/View/Profile/index?mid=2830422]TheChimichanga[/url] - 695 [url=http://www.bungie.net/en/View/Profile/index?mid=139127]The Defiant Few[/url] - 583 [url=http://www.bungie.net/en/View/Profile/index?mid=2637113]Player3Th0mas[/url] - 541 [url=http://www.bungie.net/en/View/Profile/index?mid=167320]lonepaul2441[/url] - 525 [url=http://www.bungie.net/en/View/Profile/index?mid=3813277]Black Heart[/url] - 508 [url=http://www.bungie.net/en/View/Profile/index?mid=3473480]Mister Sparkles [/url] - 495 [url=http://www.bungie.net/en/View/Profile/index?mid=3992460]The Onyx Taco[/url] - 494 [url=http://www.bungie.net/en/View/Profile/index?mid=2366481]Elegiac[/url] - 462 [url=http://www.bungie.net/en/View/Profile/index?mid=484031]AngryBrute[/url] - 425 [url=http://www.bungie.net/en/View/Profile/index?mid=3425829]Fat Man 3000[/url] - 419 [url=http://www.bungie.net/en/View/Profile/index?mid=3777375]jjj205[/url] - 409 [url=http://www.bungie.net/en/View/Profile/index?mid=423269]Spartan Ken 15[/url] - 406 [url=http://www.bungie.net/en/View/Profile/index?mid=3325860]o_____________o[/url] - 404 [/quote][spoiler]select concat( '[url=http://www.bungie.net/en/View/Profile/index?mid=', u.membershipId, ']', u.displayName, '[/url] - ', format(count(*), 0) ) from bungie.alltopics p join users u on u.membershipId = p.authorMembershipId group by p.authorMembershipId order by count(*) desc limit 0, 20;[/spoiler] Top 20 most edited topics:[quote][url=http://www.bungie.net/en/Forum/Post?id=61102938]CSS[/url] - 167 [url=http://www.bungie.net/en/Forum/Post?id=60296558]The 3DS Thread - News, Recommendations, and More![/url] - 160 [url=http://www.bungie.net/en/Forum/Post?id=62593404]Character Class/Race Poll; What will you choose?[/url] - 103 [url=http://www.bungie.net/en/Forum/Post?id=63010352]Santa's naughty list (over 100 names)[/url] - 100 [url=http://www.bungie.net/en/Forum/Post?id=47679257]Tags to find old threads + Top Topics archive [Part 1][/url] - 99 [url=http://www.bungie.net/en/Forum/Post?id=59798855]Official Destiny ARG (ARG: Complete!)[/url] - 99 [url=http://www.bungie.net/en/Forum/Post?id=60690785]Stills from Destiny Gameplay - New Confirmations[/url] - 97 [url=http://www.bungie.net/en/Forum/Post?id=60679220]Flood Census 2013[/url] - 86 [url=http://www.bungie.net/en/Forum/Post?id=62508136]Smi is streaming extremely lazily[/url] - 74 [url=http://www.bungie.net/en/Forum/Post?id=60351818]Alright, let's get some emotionally moving video game music here[/url] - 73 [url=http://www.bungie.net/en/Forum/Post?id=63173995]Realistic Usernames Thread! (75+ and counting asdf)[/url] - 73 [url=http://www.bungie.net/en/Forum/Post?id=60353469]CSS[/url] - 72 [url=http://www.bungie.net/en/Forum/Post?id=61023959]Crustiest of the crusty: Bnet'ers leave you're mark here![/url] - 67 [url=http://www.bungie.net/en/Forum/Post?id=49457946]Solution for redundant tags and sub forums[/url] - 66 [url=http://www.bungie.net/en/Forum/Post?id=59869003]PS4 out holidays 2013, check out the notes I made and discuss[/url] - 63 [url=http://www.bungie.net/en/Forum/Post?id=62298459]Pokemon Central------3DS codes[/url] - 63 [url=http://www.bungie.net/en/Forum/Post?id=60587930]CSS[/url] - 62 [url=http://www.bungie.net/en/Forum/Post?id=62756466]Join the legion of the Doge.[/url] - 60 [url=http://www.bungie.net/en/Forum/Post?id=61975329]Halo Wars Anyone?[/url] - 56 [url=http://www.bungie.net/en/Forum/Post?id=60721271]A factual comparison of the consoles[/url] - 54[/quote][spoiler]select concat( '[url=http://www.bungie.net/en/Forum/Post?id=', t.postId, ']', t.subject, '[/url] - ', t.editCount ) from bungie.alltopics t order by t.editCount desc limit 0, 20;[/spoiler] Number of topics edited not by the user who created it: 4,464[spoiler]select format(count(*), 0) from bungie.alltopics p where p.editorMembershipid != p.authorMembershipId;[/spoiler] Number of "shit" topics (topics with "shit" in the title, LOL): 587[spoiler]select format(count(*), 0) from bungie.alltopics p where lower(p.subject) like '%shit%';[/spoiler] Top 20 most active days for group creation: [quote]2013-08-31 - 18 2013-10-07 - 16 2013-02-18 - 16 2014-01-06 - 12 2013-07-10 - 12 2013-06-11 - 11 2013-10-21 - 11 2013-10-06 - 11 2013-07-08 - 11 2013-06-23 - 11 2013-11-02 - 11 2013-06-04 - 10 2013-06-22 - 10 2013-10-23 - 10 2013-10-03 - 10 2013-02-19 - 9 2013-10-04 - 9 2013-01-10 - 9 2013-09-03 - 9 2013-07-07 - 9[/quote][spoiler]select concat( date(g.creationDate), ' - ', format(count(*), 0) ) from bungie.groups g group by date(g.creationDate) order by count(*) desc limit 0, 20;[/spoiler] Number of groups that mention Destiny in their about section: 422[spoiler]select format(count(*), 0) from bungie.groups g where lower(g.about) like '%destiny%';[/spoiler] Number of groups that have Destiny as part of their group's name: 147[spoiler]select format(count(*), 0) from bungie.groups g where lower(g.name) like '%destiny%';[/spoiler] Number of groups that mention Destiny in either their name or about sections but are not tagged with #Destiny: 239[spoiler]select format(count(*), 0) from bungie.groups g where (lower(g.name) like '%destiny%' or lower(g.about) like '%destiny%') and not exists( select * from bungie.group_tags gt where gt.groupId = g.groupId and gt.tagId = (select tagId from bungie.tags where name = '#Destiny') ) [/spoiler] Top 20 most followed groups: [quote][url=http://www.bungie.net/en/Groups/Detail?groupId=3656]The Spartan I Project[/url] - 11,523 [url=http://www.bungie.net/en/Groups/Detail?groupId=8263]Coup D Bungie[/url] - 6,763 [url=http://www.bungie.net/en/Groups/Detail?groupId=2442]The Bungie Podcast[/url] - 4,736 [url=http://www.bungie.net/en/Groups/Detail?groupId=293]Mjolnir Battle Tactics[/url] - 3,674 [url=http://www.bungie.net/en/Groups/Detail?groupId=2302]Owners of the Katana[/url] - 3,407 [url=http://www.bungie.net/en/Groups/Detail?groupId=16555]Destiny[/url] - 3,344 [url=http://www.bungie.net/en/Groups/Detail?groupId=2812]Facility B5D[/url] - 1,721 [url=http://www.bungie.net/en/Groups/Detail?groupId=12337]Sapphire[/url] - 1,682 [url=http://www.bungie.net/en/Groups/Detail?groupId=39699]Bungie Mail Sack[/url] - 1,352 [url=http://www.bungie.net/en/Groups/Detail?groupId=13461]Minecrafters[/url] - 1,093 [url=http://www.bungie.net/en/Groups/Detail?groupId=602]KOTOR[/url] - 1,028 [url=http://www.bungie.net/en/Groups/Detail?groupId=3338]HLG[/url] - 950 [url=http://www.bungie.net/en/Groups/Detail?groupId=10888]Ask Your Ninja[/url] - 903 [url=http://www.bungie.net/en/Groups/Detail?groupId=19291]The Bird People[/url] - 782 [url=http://www.bungie.net/en/Groups/Detail?groupId=18533]TFS The Floods Sanctuary[/url] - 768 [url=http://www.bungie.net/en/Groups/Detail?groupId=3493]Mythic Members[/url] - 761 [url=http://www.bungie.net/en/Groups/Detail?groupId=19014]Halo Forum[/url] - 750 [url=http://www.bungie.net/en/Groups/Detail?groupId=16959]Secular Sevens[/url] - 729 [url=http://www.bungie.net/en/Groups/Detail?groupId=18909]Sociopaths United[/url] - 723 [url=http://www.bungie.net/en/Groups/Detail?groupId=39813]Art and Stuff[/url] - 692[/quote][spoiler]select concat( '[url=http://www.bungie.net/en/Groups/Detail?groupId=', g.groupId, ']', g.name, '[/url] - ', format(g.followerCount, 0) ) from bungie.groups g order by g.followerCount desc limit 0, 20;[/spoiler]
    Reply Start Related Topic
    Edit Preview Cancel
    5 Replies
    • 1
      Cam

      Cam

      1/13/2014 2:45:56 PM Permalink
      [quote]Top 20 doppelgangers (identical display names): Camnator - 33[/quote] I see my power of cloaking is quite advanced.
    • 0
      MastaSin
    • 5
      dazarobbo

      dazarobbo

      1/13/2014 10:18:44 AM Permalink
      Top 20 groups with the most members: [quote][url=http://www.bungie.net/en/Groups/Detail?groupId=3656]The Spartan I Project[/url] - 10,382 [url=http://www.bungie.net/en/Groups/Detail?groupId=8263]Coup D Bungie[/url] - 6,566 [url=http://www.bungie.net/en/Groups/Detail?groupId=2442]The Bungie Podcast[/url] - 4,467 [url=http://www.bungie.net/en/Groups/Detail?groupId=293]Mjolnir Battle Tactics[/url] - 3,563 [url=http://www.bungie.net/en/Groups/Detail?groupId=2302]Owners of the Katana[/url] - 3,272 [url=http://www.bungie.net/en/Groups/Detail?groupId=16555]Destiny[/url] - 2,800 [url=http://www.bungie.net/en/Groups/Detail?groupId=2812]Facility B5D[/url] - 1,704 [url=http://www.bungie.net/en/Groups/Detail?groupId=12337]Sapphire[/url] - 1,342 [url=http://www.bungie.net/en/Groups/Detail?groupId=13461]Minecrafters[/url] - 1,021 [url=http://www.bungie.net/en/Groups/Detail?groupId=602]KOTOR[/url] - 987 [url=http://www.bungie.net/en/Groups/Detail?groupId=3338]HLG[/url] - 926 [url=http://www.bungie.net/en/Groups/Detail?groupId=10888]Ask Your Ninja[/url] - 850 [url=http://www.bungie.net/en/Groups/Detail?groupId=18533]TFS The Floods Sanctuary[/url] - 699 [url=http://www.bungie.net/en/Groups/Detail?groupId=3493]Mythic Members[/url] - 661 [url=http://www.bungie.net/en/Groups/Detail?groupId=19291]The Bird People[/url] - 657 [url=http://www.bungie.net/en/Groups/Detail?groupId=19014]Halo Forum[/url] - 650 [url=http://www.bungie.net/en/Groups/Detail?groupId=16959]Secular Sevens[/url] - 649 [url=http://www.bungie.net/en/Groups/Detail?groupId=8896]UNSC Leviathan[/url] - 603 [url=http://www.bungie.net/en/Groups/Detail?groupId=15287]The Community Carnage[/url] - 603 [url=http://www.bungie.net/en/Groups/Detail?groupId=17487]Cafe[/url] - 581[/quote][spoiler]select concat( '[url=http://www.bungie.net/en/Groups/Detail?groupId=', g.groupId, ']', g.name, '[/url] - ', format(g.memberCount, 0) ) from bungie.groups g order by g.memberCount desc limit 0, 20;[/spoiler] Average number of members a group has: 43[spoiler]select format(floor(avg(g.memberCount)), 0) from bungie.groups g;[/spoiler] Average number of followers as group has: 51[spoiler]select format(floor(avg(g.followerCount)), 0) from bungie.groups g;[/spoiler] Number of groups with pending members: 123[spoiler]select format(count(*), 0) from bungie.groups g where g.pendingMemberCount > 0;[/spoiler] Top 20 groups with most pending members: [quote][url=http://www.bungie.net/en/Groups/Detail?groupId=3656]The Spartan I Project[/url] - 841 [url=http://www.bungie.net/en/Groups/Detail?groupId=18405]Welcoming Committee[/url] - 69 [url=http://www.bungie.net/en/Groups/Detail?groupId=2302]Owners of the Katana[/url] - 65 [url=http://www.bungie.net/en/Groups/Detail?groupId=39873]The 117th Regiment[/url] - 17 [url=http://www.bungie.net/en/Groups/Detail?groupId=12]Mob of Angry Peasants[/url] - 11 [url=http://www.bungie.net/en/Groups/Detail?groupId=12839]The WorkPLace[/url] - 11 [url=http://www.bungie.net/en/Groups/Detail?groupId=39813]Art and Stuff[/url] - 9 [url=http://www.bungie.net/en/Groups/Detail?groupId=12088]The Anime Community[/url] - 9 [url=http://www.bungie.net/en/Groups/Detail?groupId=12337]Sapphire[/url] - 9 [url=http://www.bungie.net/en/Groups/Detail?groupId=39834]Destiny on Wii U[/url] - 8 [url=http://www.bungie.net/en/Groups/Detail?groupId=62257]Destiny beta[/url] - 7 [url=http://www.bungie.net/en/Groups/Detail?groupId=61944]The Lone Wolves Pack[/url] - 6 [url=http://www.bungie.net/en/Groups/Detail?groupId=40360]Trinity[/url] - 5 [url=http://www.bungie.net/en/Groups/Detail?groupId=40465]Rogue Ops[/url] - 4 [url=http://www.bungie.net/en/Groups/Detail?groupId=39601]The Guardians of Destiny[/url] - 4 [url=http://www.bungie.net/en/Groups/Detail?groupId=41706]Lurulu[/url] - 4 [url=http://www.bungie.net/en/Groups/Detail?groupId=41199]AlienNerds[/url] - 3 [url=http://www.bungie.net/en/Groups/Detail?groupId=39785]KSI National[/url] - 3 [url=http://www.bungie.net/en/Groups/Detail?groupId=41360]Destiny Gamers in PS4[/url] - 3 [url=http://www.bungie.net/en/Groups/Detail?groupId=41418]Huntresses[/url] - 3[/quote][spoiler]select concat( '[url=http://www.bungie.net/en/Groups/Detail?groupId=', g.groupId, ']', g.name, '[/url] - ', format(g.pendingMemberCount, 0) ) from bungie.groups g order by g.pendingMemberCount desc limit 0, 20;[/spoiler] Number of groups where the current founder is not the user who created it: 159[spoiler]select format(count(*), 0) from bungie.groups g where g.membershipIdCreated != g.founderMembershipId;[/spoiler] Number of groups that were deleted by someone other than the founder or creator: 131[spoiler]select format(count(*), 0) from bungie.groups g where g.deletedByMembershipId != g.founderMembershipId and g.deletedByMembershipId != g.membershipIdCreated;[/spoiler] Number of users whose display name is the same as their unique name: 6,834[spoiler]select format(count(*), 0) from bungie.users u where u.displayName = u.uniqueName;[/spoiler] Number of users whose unique name is a number: 11,604[spoiler]select format(count(*), 0) from bungie.users u where u.uniqueName regexp '[0-9]+';[/spoiler] Top 20 doppelgangers (identical display names): [quote]Camnator - 33 alex - 15 FOX NEWS - 15 lll - 14 SecondClass - 13 NRA Member - 13 Nick - 11 Chris - 9 Matt - 9 Master Chief - 8 Ryan - 7 Jacob - 7 Jake - 7 Ghost - 6 Sam - 6 Ethan - 6 Epsilon Eridani - 6 Tony - 6 Tyler - 6 Le Dustin - 6[/quote][spoiler]select concat( u.displayName, ' - ', count(*) ) from bungie.users u group by u.displayName having count(*) > 1 order by count(*) desc limit 0, 20;[/spoiler] Average number of followers a user has: 3[spoiler]select format(floor(avg(u.followerCount)), 0) from users u;[/spoiler] Top 20 most active days for account creation: [quote]2013-02-18 - 127 2013-02-19 - 108 2013-06-11 - 87 2013-06-13 - 87 2013-06-12 - 80 2013-10-02 - 78 2013-02-20 - 70 2013-06-14 - 69 2013-02-17 - 61 2013-06-17 - 60 2013-02-23 - 60 2013-06-22 - 57 2013-03-14 - 56 2013-07-08 - 56 2013-10-05 - 55 2013-10-06 - 55 2013-02-21 - 54 2013-10-01 - 54 2013-05-26 - 52 2013-05-27 - 50[/quote][spoiler]select concat( date(u.firstAccess), ' - ', format(count(*), 0) ) from bungie.users u group by date(u.firstAccess) order by count(*) desc limit 0, 20;[/spoiler] Top 20 most active days for account updates: [quote]2013-07-03 - 272 2013-07-04 - 205 2013-01-08 - 197 2013-02-18 - 169 2013-10-02 - 143 2013-07-07 - 143 2013-01-25 - 140 2013-02-19 - 134 2013-10-06 - 131 2013-01-11 - 126 2013-02-17 - 126 2013-07-05 - 124 2013-10-05 - 117 2013-10-04 - 105 2013-10-07 - 104 2013-07-06 - 101 2013-07-08 - 101 2013-10-03 - 98 2013-10-01 - 94 2013-06-11 - 90[/quote][spoiler]select concat( date(u.lastUpdate), ' - ', format(count(*), 0) ) from bungie.users u group by date(u.lastUpdate) order by count(*) desc limit 0, 20;[/spoiler] Top 20 most followed users:[quote][url=http://www.bungie.net/en/View/Profile/index?mid=8011]DeeJ[/url] - 2,512 [url=http://www.bungie.net/en/View/Profile/index?mid=3844298]Alpha Lupi[/url] - 1,055 [url=http://www.bungie.net/en/View/Profile/index?mid=6653]Halcylon[/url] - 506 [url=http://www.bungie.net/en/View/Profile/index?mid=282]stoshington[/url] - 494 [url=http://www.bungie.net/en/View/Profile/index?mid=1]Achronos[/url] - 467 [url=http://www.bungie.net/en/View/Profile/index?mid=1189677]Recon Number 54[/url] - 395 [url=http://www.bungie.net/en/View/Profile/index?mid=111662]Hylebos[/url] - 373 [url=http://www.bungie.net/en/View/Profile/index?mid=14130]burritosenior[/url] - 337 [url=http://www.bungie.net/en/View/Profile/index?mid=17925]FoMan123[/url] - 275 [url=http://www.bungie.net/en/View/Profile/index?mid=4878]Spawn[/url] - 266 [url=http://www.bungie.net/en/View/Profile/index?mid=7777]True Underdog[/url] - 258 [url=http://www.bungie.net/en/View/Profile/index?mid=666219]A 3 Legged Goat[/url] - 243 [url=http://www.bungie.net/en/View/Profile/index?mid=3360554]Vien Quitonm[/url] - 242 [url=http://www.bungie.net/en/View/Profile/index?mid=3391609]CamCamm[/url] - 236 [url=http://www.bungie.net/en/View/Profile/index?mid=888158]Destiny Updates[/url] - 202 [url=http://www.bungie.net/en/View/Profile/index?mid=23356]MyNameIsCharlie[/url] - 202 [url=http://www.bungie.net/en/View/Profile/index?mid=1774]Duardo[/url] - 200 [url=http://www.bungie.net/en/View/Profile/index?mid=17956]Cortana Five[/url] - 195 [url=http://www.bungie.net/en/View/Profile/index?mid=744314]Gojira[/url] - 188 [url=http://www.bungie.net/en/View/Profile/index?mid=423038]TopWargamer[/url] - 168[/quote][spoiler]select concat( '[url=http://www.bungie.net/en/View/Profile/index?mid=', u.membershipId, ']', u.displayName, '[/url] - ', format(u.followerCount, 0) ) from users u order by u.followerCount desc limit 0, 20;[/spoiler]
      Reply Start Related Topic
      Edit Preview Cancel
      12 Replies
      • 1
        dazarobbo

        dazarobbo

        1/15/2014 7:28:36 AM Permalink
        I wanted to have a look at the most frequently used words in topic subject lines, so here's the top 200 of them. Note: this takes into account things like upper/lower case, HTML encoding, and apostrophes (ie. "I'm" becomes "im"). If you would like to check the way I've done this, open the spoiler below. [quote]"the" - x23,320 "you" - x16,504 "a" - x15,736 "to" - x14,283 "i" - x12,048 "is" - x11,636 "of" - x10,599 "in" - x9,301 "destiny" - x8,829 "what" - x8,133 "do" - x7,366 "for" - x7,258 "on" - x6,562 "and" - x6,343 "your" - x5,676 "this" - x5,352 "are" - x5,162 "my" - x5,146 "be" - x4,831 "halo" - x4,726 "how" - x4,718 "have" - x4,572 "so" - x3,856 "will" - x3,774 "why" - x3,554 "with" - x3,511 "or" - x3,433 "it" - x3,348 "one" - x3,317 "game" - x3,267 "xbox" - x3,246 "me" - x3,187 "new" - x3,028 "if" - x2,832 "like" - x2,658 "bungie" - x2,595 "that" - x2,509 "about" - x2,495 "should" - x2,445 "would" - x2,403 "im" - x2,397 "just" - x2,394 "who" - x2,331 "can" - x2,197 "we" - x2,083 "vs" - x2,048 "all" - x2,030 "get" - x2,024 "think" - x2,019 "thread" - x1,992 "people" - x1,988 "3" - x1,964 "anyone" - x1,939 "an" - x1,935 "games" - x1,881 "4" - x1,853 "not" - x1,805 "at" - x1,777 "help" - x1,719 "flood" - x1,686 "from" - x1,685 "2" - x1,683 "best" - x1,679 "ps4" - x1,676 "-" - x1,670 "was" - x1,638 "out" - x1,628 "favorite" - x1,624 "does" - x1,606 "has" - x1,581 "now" - x1,567 "good" - x1,556 "up" - x1,548 "beta" - x1,524 "here" - x1,490 "need" - x1,479 "ever" - x1,479 "play" - x1,453 "want" - x1,430 "when" - x1,416 "there" - x1,409 "any" - x1,342 "whats" - x1,331 "more" - x1,325 "question" - x1,324 "time" - x1,274 "post" - x1,240 "no" - x1,240 "some" - x1,236 "am" - x1,232 "which" - x1,232 "guys" - x1,232 "going" - x1,189 "video" - x1,163 "did" - x1,161 "make" - x1,154 "as" - x1,151 "know" - x1,144 "dont" - x1,115 "most" - x1,096 "its" - x1,077 "day" - x1,066 "pc" - x1,042 "first" - x957 "world" - x905 "us" - x900 "got" - x879 "by" - x840 "5" - x840 "see" - x824 "been" - x820 "next" - x818 "man" - x815 "gaming" - x796 "back" - x776 "could" - x764 "today" - x764 "last" - x763 "old" - x760 "reach" - x757 "better" - x757 "class" - x737 "go" - x736 "bad" - x727 "where" - x726 "only" - x720 "live" - x719 "console" - x716 "online" - x715 "music" - x709 "many" - x707 "life" - x707 "getting" - x691 "really" - x688 "than" - x683 "something" - x683 "gta" - x679 "thing" - x671 "dead" - x664 "forum" - x664 "else" - x652 "cant" - x650 "name" - x646 "lets" - x645 "someone" - x644 "movie" - x641 "had" - x637 "war" - x637 "they" - x635 "still" - x629 "looking" - x626 "made" - x618 "1" - x614 "love" - x609 "hate" - x601 "please" - x600 "trailer" - x596 "into" - x593 "but" - x590 "group" - x582 "story" - x582 "much" - x580 "release" - x565 "black" - x564 "over" - x564 "right" - x562 "being" - x562 "free" - x559 "360" - x558 "were" - x555 "space" - x552 "gun" - x552 "community" - x551 "join" - x550 "news" - x550 "year" - x548 "girl" - x548 "character" - x547 "look" - x546 "use" - x534 "come" - x534 "wars" - x533 "our" - x533 "everyone" - x521 "other" - x521 "u" - x518 "e3" - x516 "hey" - x516 "things" - x515 "song" - x511 "real" - x509 "gameplay" - x508 "anything" - x502 "cod" - x492 "off" - x491 "buy" - x488 "feel" - x486 "found" - x484 "watch" - x482 "ask" - x481[/quote][spoiler][b]SQL[/b] select p.subject from bungie.alltopics p into outfile 'H:/Websites/bungie/public_html/subject_lines.txt'; [b]PHP[/b] <?php $bin = array(); $f = fopen('subject_lines.txt', 'r'); while($f && ($line = fgets($f))){ $line = strtolower(html_entity_decode($line, ENT_QUOTES)); $line = preg_replace('/[^a-zA-Z0-9- ]/i', '', $line); foreach(explode(' ', trim($line)) as $word){ if(strlen($word) === 0){ continue; } if(!isset($bin[$word])){ $bin[$word] = 1; } else{ ++$bin[$word]; } } } fclose($f); arsort($bin, SORT_NUMERIC); $bin = array_slice($bin, 0, 200, true); foreach($bin as $word => $freq){ echo sprintf('"%s" - x%s', $word, number_format($freq)) . PHP_EOL; } ?>[/spoiler]
        Reply Start Related Topic
        Edit Preview Cancel
      • 0
        Crimson Howl
      • 0
        Violet

        Violet

        Edited: 1/16/2014 2:51:41 AM Permalink
        Well, Daz, you have just brightened up my day. I thank thee.[quote]Most rated topic: [url=http://www.bungie.net/en/Forum/Post?id=5298298]Am I the only one who actually likes the new layout?[/url] - 166[/quote] [spoiler]Something tells me this isn't quite right. Maybe it's just me, but I'm fairly certain that my thread only has 45 replies![quote][url=http://www.bungie.net/en/Groups/Post?id=5298298]Am I the only one who actually likes the new layout?[/url] - 4,377[/quote][/spoiler]
        Reply Start Related Topic
        Edit Preview Cancel
        2 Replies
        • 0
          HipiO7

          HipiO7

          1/15/2014 7:22:13 PM Permalink
          Impressive Das. Sad how more than half those groups with the most members are pretty much dead now. Anyways, amazing job on the stats. Very well done.
          1 Reply
          • 0
            A Fly Lady
          • 0
            Laser

            Laser

            1/14/2014 6:08:39 AM Permalink
            Can you do one for how many people use the dark theme compared to those who use the white one?
            2 Replies
            • 0
              KingInTheNorth
            • 0
              Wicked Navajo

              Wicked Navajo

              1/14/2014 3:24:48 AM Permalink
              That's pretty awesome. Could you maybe also post the top 20 people who posted the most replies, in addition to topics?
              2 Replies
              • 0
                Bricypoo
              • 0
                EAGLES5
                1 Reply
                • 0
                  Hylebos

                  Hylebos

                  1/13/2014 6:50:04 PM Permalink
                  I'm taking Webscripting this quarter which includes PHP, so perhaps I'll be able to do stuff like this myself soon :)
                  1 Reply
                  • 0
                    Spawn
                    10 Replies
                    • 0
                      Frosty
                      2 Replies
                      • 0
                        Hatima
                      • 1
                        Jailbot
                      • 1
                        DE4THINC4RN4TE
                      • 1
                        Cobravert

                        Cobravert

                        1/13/2014 3:46:00 PM Permalink
                        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.
                        Reply Start Related Topic
                        Edit Preview Cancel
                      • 1
                        UphillMercury

                        UphillMercury

                        Edited: 1/13/2014 5:06:47 PM Permalink
                        This is awesome. I love seeing stats like this, and it's really cool to get an insight into the community like this. Thanks for sharing.
                      • 0
                        Joy To Kamots
                      • 0
                        Oneironaut

                        Oneironaut

                        1/13/2014 7:03:00 PM Permalink
                        The "Top 20 people who post too many topics" part is my favorite. I see Charlie has been beaten this year, lol. Also surprised Shadows didn't make the list...
                      • 0
                        Progo Claus
                      preload icon
                      preload icon
                      preload icon
                      You are not allowed to view this content.