So, there is a has_child setting, but there's no has_parent boolean? Why's that?
Posted under General
So, there is a has_child setting, but there's no has_parent boolean? Why's that?
There's has_children, has_active_children, and has_visible_children on the one side, and parent_id on the other.
parent_id == null
...is equivalent to...
has_parent == false
... and vice versa...
parent_id != null
...is equivalent to...
has_parent == true
BrokenEagle98 said:
Strange, then why don't the table details show an option for has_parent?
Edit: I see, you edited it, nevermind.
Edit2: Everytime I use parent_id as a requirement, query returns 0 results :(. This is wasting too much time when I can do it manually on DB without querying it.
Updated
SELECT id,parent_id FROM [danbooru-data:danbooru.posts] WHERE parent_id != 0 LIMIT 1000
Apparently when it was imported, it set the parent_id from null to 0. See the above example which worked for me.
lkjh098 said:
It looks like the answer is that tags do make a difference. comic doesn't show up in the top 100 at all, which probably means a very low average score. High-scoring tags on safe images include ass, small_breasts, bikini, collarbone, and panties. Low-scoring tags on safe images include monochrome, 1boy, school_uniform, glasses and weapon. Only two copyrights showed up: kantai_collection is unusually high-scoring, but touhou posts have average scores.
Great analysis. The tags with the highest average scores are mostly just the ones indicating a post's level of sexiness. It's interesting that for certain tags - breasts, cleavage, underwear - average scores barely differ across ratings; rating:s cleavage is almost as popular as rating:e cleavage. So it goes to show that score is driven by sex appeal, even for rating:s.
It's also interesting to compare average scores across classes of tags. Among hair colors, silver hair is the most popular and green hair is the least. Among breast sizes, small breasts is Danbooru's favorite.
Let's look specifically at the top copyrights. Do Touhou and Kantai Collection really have higher scores than average?
Spreadsheet: https://docs.google.com/spreadsheets/d/1N01syQ1ulSGdNnA1Iv-TIn5ktdVgukl5jDxzSUlxICA/edit?usp=sharing
BigQuery: https://bigquery.cloud.google.com:443/savedquery/657582419813:43ad5d035f6040d6a314e387125dd36b
0 | tag | posts | total_score | average_score | average_score_e | average_score_q | average_score_s | rating_e_pct | rating_q_pct | rating_s_pct |
---|---|---|---|---|---|---|---|---|---|---|
1 | kantai_collection | 48520 | 468758 | 9.7 | 13.4 | 15.0 | 8.7 | 4.4 | 12.5 | 83.0 |
2 | touhou | 38400 | 362157 | 9.4 | 12.1 | 14.0 | 8.8 | 4.0 | 9.9 | 86.1 |
3 | original | 32402 | 298834 | 9.2 | 12.7 | 12.5 | 7.9 | 11.2 | 17.0 | 71.8 |
4 | idolmaster | 13687 | 117568 | 8.6 | 14.2 | 14.8 | 7.2 | 7.1 | 11.6 | 81.3 |
5 | idolmaster_cinderella_girls | 10132 | 89731 | 8.9 | 14.8 | 14.8 | 7.5 | 6.4 | 11.7 | 81.9 |
6 | fate_(series) | 6831 | 59757 | 8.7 | 15.9 | 13.4 | 7.8 | 4.5 | 10.9 | 84.6 |
7 | girls_und_panzer | 6389 | 59291 | 9.3 | 13.0 | 13.8 | 8.4 | 4.9 | 12.9 | 82.2 |
8 | granblue_fantasy | 5551 | 60509 | 10.9 | 13.6 | 13.4 | 9.6 | 14.9 | 18.7 | 66.4 |
9 | pokemon | 5121 | 38208 | 7.5 | 9.4 | 12.1 | 6.7 | 10.8 | 9.1 | 80.1 |
10 | love_live! | 5055 | 44295 | 8.8 | 15.7 | 14.1 | 7.9 | 3.5 | 8.9 | 87.6 |
11 | fate/grand_order | 4862 | 39507 | 8.1 | 12.6 | 11.5 | 7.6 | 2.9 | 10.4 | 86.7 |
12 | overwatch | 4394 | 50633 | 11.5 | 14.4 | 16.2 | 10.6 | 7.7 | 10.9 | 81.4 |
13 | vocaloid | 3922 | 32436 | 8.3 | 7.4 | 12.3 | 7.9 | 7.9 | 9.0 | 83.1 |
14 | love_live!_school_idol_project | 3618 | 29831 | 8.2 | 15.0 | 13.7 | 7.4 | 2.8 | 9.6 | 87.6 |
15 | touken_ranbu | 2866 | 7085 | 2.5 | 4.4 | 3.8 | 2.4 | 2.0 | 1.7 | 96.4 |
16 | jojo_no_kimyou_na_bouken | 2666 | 3443 | 1.3 | 3.2 | 4.2 | 1.3 | 0.5 | 0.9 | 98.6 |
17 | pokemon_(game) | 2534 | 19096 | 7.5 | 9.9 | 11.9 | 6.5 | 12.7 | 10.8 | 76.5 |
18 | re:zero_kara_hajimeru_isekai_seikatsu | 2520 | 34400 | 13.7 | 17.8 | 19.0 | 12.5 | 6.6 | 12.6 | 80.8 |
19 | osomatsu-san | 1924 | 3784 | 2.0 | 3.0 | 4.0 | 1.9 | 0.9 | 2.1 | 97.0 |
20 | fire_emblem | 1914 | 12127 | 6.3 | 18.4 | 10.5 | 5.4 | 4.7 | 5.5 | 89.8 |
21 | final_fantasy | 1805 | 12457 | 6.9 | 15.8 | 13.6 | 5.2 | 7.8 | 10.4 | 81.8 |
22 | precure | 1799 | 8102 | 4.5 | 8.0 | 8.0 | 3.4 | 5.8 | 17.6 | 76.5 |
23 | osomatsu-kun | 1748 | 3028 | 1.7 | 2.8 | 3.8 | 1.7 | 0.7 | 2.1 | 97.3 |
24 | love_live!_sunshine!! | 1486 | 14764 | 9.9 | 16.7 | 15.6 | 9.1 | 4.9 | 7.0 | 88.1 |
25 | mahou_shoujo_madoka_magica | 1446 | 10435 | 7.2 | 10.4 | 10.8 | 6.6 | 4.1 | 10.2 | 85.7 |
26 | league_of_legends | 1322 | 13691 | 10.4 | 13.7 | 15.6 | 8.7 | 12.4 | 15.2 | 72.4 |
27 | fire_emblem_if | 1316 | 8226 | 6.3 | 18.5 | 10.6 | 5.6 | 3.4 | 4.6 | 92.0 |
28 | neptune_(series) | 1311 | 8371 | 6.4 | 7.8 | 9.4 | 5.8 | 11.4 | 10.9 | 77.7 |
29 | fate/stay_night | 1254 | 10165 | 8.1 | 11.1 | 17.2 | 7.1 | 4.5 | 7.9 | 87.6 |
30 | high_school_dxd | 1213 | 4955 | 4.1 | 16.7 | 7.3 | 3.7 | 1.0 | 7.2 | 91.8 |
31 | pokemon_sm | 1156 | 8035 | 7.0 | 10.2 | 11.9 | 6.1 | 10.5 | 7.7 | 81.8 |
32 | zhan_jian_shao_nyu | 1154 | 9426 | 8.2 | 13.2 | 10.8 | 7.2 | 4.1 | 20.7 | 75.2 |
33 | splatoon | 1106 | 7009 | 6.3 | 11.6 | 12.0 | 5.7 | 4.9 | 5.5 | 89.6 |
34 | gochuumon_wa_usagi_desu_ka? | 1061 | 11700 | 11.0 | 16.2 | 16.1 | 9.3 | 4.9 | 20.4 | 74.7 |
35 | idolmaster_million_live! | 1042 | 9081 | 8.7 | 12.0 | 13.7 | 7.0 | 17.3 | 13.0 | 69.8 |
36 | kono_subarashii_sekai_ni_shukufuku_wo! | 1041 | 15957 | 15.3 | 18.9 | 18.4 | 12.7 | 15.9 | 29.4 | 54.8 |
37 | undertale | 1023 | 4510 | 4.4 | 8.1 | 7.3 | 4.3 | 1.7 | 2.0 | 96.4 |
38 | idolmaster_cinderella_girls_starlight_stage | 1009 | 6377 | 6.3 | 8.9 | 12.4 | 6.1 | 2.1 | 3.2 | 94.7 |
39 | bishoujo_senshi_sailor_moon | 1002 | 5244 | 5.2 | 9.1 | 9.5 | 5.0 | 2.1 | 3.9 | 94.0 |
40 | danganronpa | 993 | 3985 | 4.0 | 8.8 | 7.4 | 3.8 | 0.8 | 4.3 | 94.9 |
41 | flower_knight_girl | 980 | 6831 | 7.0 | 7.5 | 11.5 | 5.8 | 51.5 | 4.5 | 44.0 |
42 | pokemon_go | 976 | 10076 | 10.3 | 14.3 | 15.3 | 9.6 | 5.5 | 8.8 | 85.7 |
43 | street_fighter | 966 | 6553 | 6.8 | 8.1 | 8.7 | 5.8 | 14.5 | 22.0 | 63.5 |
44 | idolmaster_side-m | 950 | 1289 | 1.4 | 5.7 | 4.8 | 1.3 | 0.9 | 1.4 | 97.7 |
45 | gundam | 928 | 4860 | 5.2 | 10.2 | 8.9 | 4.2 | 5.3 | 14.9 | 79.8 |
46 | rwby | 898 | 8902 | 9.9 | 22.8 | 13.7 | 8.6 | 8.1 | 3.1 | 88.8 |
47 | fate/extra | 881 | 8576 | 9.7 | 14.8 | 15.4 | 8.9 | 2.8 | 9.8 | 87.4 |
48 | voiceroid | 776 | 6251 | 8.1 | 5.3 | 11.0 | 8.2 | 22.7 | 18.6 | 58.8 |
49 | phantasy_star | 761 | 6720 | 8.8 | 10.6 | 11.3 | 8.0 | 7.9 | 18.5 | 73.6 |
50 | phantasy_star_online_2 | 749 | 6674 | 8.9 | 10.7 | 11.4 | 8.1 | 7.7 | 18.7 | 73.6 |
51 | koutetsujou_no_kabaneri | 711 | 8837 | 12.4 | 21.4 | 16.0 | 11.0 | 4.4 | 20.1 | 75.5 |
52 | tales_of_(series) | 694 | 2834 | 4.1 | 4.4 | 5.5 | 3.8 | 20.9 | 8.9 | 70.2 |
53 | world_witches_series | 690 | 7669 | 11.1 | 13.1 | 14.4 | 9.3 | 11.0 | 26.8 | 62.2 |
54 | persona | 681 | 4003 | 5.9 | 14.1 | 12.0 | 4.8 | 6.3 | 6.8 | 86.9 |
55 | mahou_girls_precure! | 679 | 2702 | 4.0 | 9.0 | 7.2 | 3.2 | 5.4 | 11.8 | 82.8 |
56 | musaigen_no_phantom_world | 670 | 8391 | 12.5 | 14.4 | 15.4 | 9.9 | 23.4 | 28.2 | 48.4 |
57 | dagashi_kashi | 654 | 10601 | 16.2 | 19.4 | 18.6 | 14.3 | 15.1 | 27.2 | 57.6 |
58 | sword_art_online | 652 | 4758 | 7.3 | 17.2 | 14.4 | 6.1 | 5.8 | 6.3 | 87.9 |
59 | yuu-gi-ou | 648 | 4115 | 6.4 | 12.8 | 11.3 | 4.7 | 10.6 | 11.6 | 77.8 |
60 | neon_genesis_evangelion | 646 | 5184 | 8.0 | 15.2 | 10.3 | 7.4 | 4.6 | 10.1 | 85.3 |
61 | strike_witches | 628 | 7061 | 11.2 | 13.1 | 14.3 | 9.5 | 12.1 | 26.9 | 61.0 |
62 | monogatari_(series) | 609 | 7975 | 13.1 | 22.1 | 20.1 | 10.9 | 6.1 | 16.6 | 77.3 |
63 | kill_la_kill | 580 | 3977 | 6.9 | 13.6 | 13.5 | 5.5 | 4.3 | 12.6 | 83.1 |
64 | senran_kagura_(series) | 575 | 5813 | 10.1 | 11.7 | 11.9 | 9.0 | 8.2 | 31.8 | 60.0 |
65 | blazblue | 569 | 4139 | 7.3 | 4.7 | 12.6 | 6.6 | 19.0 | 17.4 | 63.6 |
66 | lawson | 568 | 5391 | 9.5 | 14.4 | 15.1 | 8.2 | 6.0 | 13.7 | 80.3 |
67 | boku_no_hero_academia | 566 | 3735 | 6.6 | 13.7 | 12.3 | 4.9 | 7.8 | 13.4 | 78.8 |
68 | puzzle_&_dragons | 555 | 4452 | 8.0 | 12.4 | 13.0 | 6.4 | 5.6 | 19.5 | 75.0 |
69 | guilty_gear | 553 | 4707 | 8.5 | 10.7 | 10.9 | 7.8 | 5.8 | 17.5 | 76.7 |
70 | one-punch_man | 545 | 4855 | 8.9 | 12.9 | 15.3 | 6.8 | 8.8 | 18.3 | 72.8 |
71 | fate/apocrypha | 538 | 4196 | 7.8 | 12.5 | 13.8 | 7.0 | 2.0 | 10.0 | 87.9 |
72 | naruto | 529 | 2427 | 4.6 | 6.5 | 12.3 | 3.6 | 14.6 | 6.6 | 78.8 |
73 | new_horizon | 510 | 7188 | 14.1 | 20.3 | 18.3 | 11.9 | 11.2 | 19.8 | 69.0 |
74 | the_king_of_fighters | 508 | 2572 | 5.1 | 7.5 | 7.5 | 4.3 | 7.5 | 16.7 | 75.8 |
75 | dragon_ball | 502 | 1956 | 3.9 | 3.6 | 9.6 | 3.4 | 6.0 | 7.6 | 86.5 |
76 | monster_musume_no_iru_nichijou | 498 | 3980 | 8.0 | 11.8 | 10.0 | 6.9 | 8.2 | 21.5 | 70.3 |
77 | the_legend_of_zelda | 488 | 3503 | 7.2 | 11.3 | 11.7 | 5.8 | 12.1 | 11.7 | 76.2 |
78 | to_aru_majutsu_no_index | 455 | 4419 | 9.7 | 17.3 | 16.7 | 8.6 | 5.3 | 7.7 | 87.0 |
79 | wild_arms | 445 | 370 | 0.8 | 7.2 | 6.8 | 0.6 | 2.2 | 1.3 | 96.4 |
80 | street_fighter_v | 438 | 3302 | 7.5 | 9.2 | 9.4 | 6.4 | 14.2 | 23.5 | 62.3 |
81 | final_fantasy_xiv | 435 | 6590 | 15.1 | 20.3 | 18.5 | 13.4 | 15.2 | 13.6 | 71.3 |
82 | to_love-ru | 411 | 5578 | 13.6 | 10.4 | 18.3 | 10.6 | 18.2 | 39.2 | 42.6 |
83 | final_fantasy_tactics | 403 | 626 | 1.6 | 7.7 | 4.9 | 1.3 | 2.5 | 2.7 | 94.8 |
84 | fate/kaleid_liner_prisma_illya | 389 | 6442 | 16.6 | 25.3 | 20.7 | 11.5 | 21.1 | 23.1 | 55.8 |
85 | sennen_sensou_aigis | 386 | 3195 | 8.3 | 11.6 | 13.0 | 6.2 | 11.1 | 22.3 | 66.6 |
86 | guilty_gear_xrd | 382 | 3507 | 9.2 | 11.6 | 11.9 | 8.4 | 5.2 | 18.3 | 76.4 |
87 | aikatsu! | 380 | 2469 | 6.5 | 13.5 | 13.7 | 5.7 | 2.1 | 8.4 | 89.5 |
88 | touhou_(pc-98) | 375 | 2386 | 6.4 | 7.3 | 7.9 | 6.3 | 0.8 | 2.9 | 96.3 |
89 | pokemon_(anime) | 372 | 2542 | 6.8 | 12.6 | 10.5 | 5.1 | 13.2 | 14.0 | 72.8 |
90 | one_piece | 370 | 1575 | 4.3 | 8.5 | 6.7 | 3.5 | 3.5 | 18.6 | 77.8 |
91 | marvel | 359 | 1833 | 5.1 | 5.8 | 9.4 | 4.8 | 1.7 | 5.3 | 93.0 |
92 | shingeki_no_bahamut | 359 | 3120 | 8.7 | 11.8 | 12.6 | 8.1 | 4.7 | 8.9 | 86.4 |
93 | dragon_ball_z | 352 | 1203 | 3.4 | 1.5 | 5.9 | 3.4 | 6.3 | 4.8 | 88.9 |
94 | elsword | 345 | 2327 | 6.7 | 8.7 | 9.7 | 6.0 | 7.8 | 15.4 | 76.8 |
95 | kumamiko | 344 | 4177 | 12.1 | 19.5 | 15.9 | 10.3 | 6.7 | 21.5 | 71.8 |
96 | yuru_yuri | 341 | 2545 | 7.5 | 9.4 | 9.9 | 7.3 | 1.5 | 5.0 | 93.5 |
97 | senran_kagura | 339 | 3146 | 9.3 | 10.4 | 11.4 | 8.2 | 8.3 | 27.1 | 64.6 |
98 | k-on! | 337 | 2374 | 7.0 | 8.1 | 8.3 | 6.7 | 13.1 | 9.5 | 77.4 |
99 | youkai_watch | 333 | 1170 | 3.5 | 8.3 | 6.6 | 3.1 | 5.7 | 4.2 | 90.1 |
100 | transformers | 332 | 657 | 2.0 | 1.0 | 7.3 | 1.9 | 0.3 | 1.2 | 98.5 |
SELECT tags.name AS tag, COUNT(p.id) AS posts, SUM(score) AS total_score, ROUND(AVG(score), 1) AS average_score, ROUND(AVG(IF(rating = "e", score, NULL)), 1) as average_score_e, ROUND(AVG(IF(rating = "q", score, NULL)), 1) as average_score_q, ROUND(AVG(IF(rating = "s", score, NULL)), 1) as average_score_s, ROUND(AVG(rating = "e") * 100, 1) as rating_e_pct, ROUND(AVG(rating = "q") * 100, 1) as rating_q_pct, ROUND(AVG(rating = "s") * 100, 1) as rating_s_pct FROM FLATTEN([danbooru-data:danbooru.posts], tags) AS p JOIN [turing-zone-143603:danbooru_latest.tags] AS t ON p.tags.name = t.name WHERE TRUE AND t.category = 3 AND YEAR(p.created_at) = 2016 GROUP BY tag ORDER BY posts DESC LIMIT 100
Answer: Yes and no. They're the most uploaded and they do have higher averages than many copyrights. But original actually isn't far behind. idolmaster_cinderella_girls, fate_(series), and love_live! beat them in the rating:e category. overwatch, re:zero_kara_hajimeru_isekai_seikatsu and kono_subarashii_sekai_ni_shukufuku_wo! beat them across the board. Although these things do have far fewer uploads, so perhaps their scores are less watered down in comparison to the tens of thousands of touhou / kantai collection posts.
And perhaps unsurprisingly: Male focus copyrights like jojo_no_kimyou_na_bouken and touken_ranbu are incredibly unpopular here in terms of scores, despite having respectable numbers of uploads.
Updated
evazion said:
Yeah, part of the problem with comparing kancolle and touhou tags to others is gonna also be that, due to their popularity, very mediocre images which might otherwise fall through may be approved on the strength of the fandom, and in much larger quantities, as well as the large number of comics attributed to those two copyrights, which naturally fall low on the score table.
Here's a table showing how the top 100 copyrights have grown over the years:
BigQuery: https://bigquery.cloud.google.com:443/savedquery/657582419813:17937b92402b40578136eae1eb018df0
Spreadsheet: https://docs.google.com/spreadsheets/d/1eem8FbABaRyEa5xMy6R9BHzcfe5vT0N4u9JEo5BC26U/edit?usp=sharing
0 | tag | uploads | uploads_2005 | uploads_2006 | uploads_2007 | uploads_2008 | uploads_2009 | uploads_2010 | uploads_2011 | uploads_2012 | uploads_2013 | uploads_2014 | uploads_2015 | uploads_2016 |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | touhou | 521213 | 1174 | 2869 | 6956 | 35056 | 56872 | 71151 | 72635 | 71191 | 54681 | 54976 | 55252 | 38400 |
2 | original | 266082 | 1901 | 5341 | 7968 | 15659 | 17752 | 21278 | 24428 | 31236 | 31472 | 36407 | 40238 | 32402 |
3 | kantai_collection | 197497 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 15549 | 63020 | 70408 | 48520 |
4 | vocaloid | 75785 | 0 | 0 | 1408 | 4984 | 12175 | 12600 | 10276 | 11813 | 8189 | 5565 | 4853 | 3922 |
5 | idolmaster | 70107 | 83 | 212 | 866 | 2594 | 2855 | 3228 | 4123 | 9161 | 8727 | 9458 | 15113 | 13687 |
6 | mahou_shoujo_madoka_magica | 48065 | 0 | 0 | 0 | 0 | 0 | 9 | 22402 | 8551 | 5535 | 6984 | 3138 | 1446 |
7 | fate_(series) | 44499 | 814 | 2136 | 1513 | 1702 | 1224 | 1145 | 4505 | 10471 | 4606 | 3797 | 5755 | 6831 |
8 | pokemon | 36397 | 9 | 119 | 143 | 1552 | 2730 | 5647 | 3578 | 6618 | 3709 | 4035 | 3136 | 5121 |
9 | idolmaster_cinderella_girls | 35867 | 0 | 0 | 0 | 0 | 0 | 1 | 35 | 4886 | 5147 | 4142 | 11524 | 10132 |
10 | precure | 29604 | 73 | 309 | 219 | 388 | 891 | 2118 | 3237 | 6730 | 6475 | 4358 | 3007 | 1799 |
11 | jojo_no_kimyou_na_bouken | 26577 | 5 | 28 | 64 | 240 | 375 | 400 | 277 | 783 | 10316 | 5503 | 5920 | 2666 |
12 | k-on! | 22991 | 0 | 0 | 0 | 5 | 5212 | 7470 | 4444 | 3138 | 1231 | 687 | 467 | 337 |
13 | fate/stay_night | 21141 | 786 | 2125 | 1441 | 1666 | 1105 | 1003 | 1972 | 2907 | 1497 | 2360 | 3025 | 1254 |
14 | love_live! | 20751 | 0 | 0 | 0 | 0 | 0 | 6 | 44 | 46 | 1656 | 6797 | 7147 | 5055 |
15 | pokemon_(game) | 20690 | 2 | 27 | 27 | 558 | 984 | 3428 | 2231 | 4332 | 2407 | 2613 | 1547 | 2534 |
16 | lyrical_nanoha | 20264 | 130 | 1246 | 3287 | 5209 | 2348 | 2091 | 1223 | 2035 | 1087 | 665 | 679 | 264 |
17 | final_fantasy | 19684 | 82 | 915 | 876 | 4085 | 2864 | 2249 | 1806 | 1449 | 953 | 1073 | 1527 | 1805 |
18 | suzumiya_haruhi_no_yuuutsu | 19466 | 3 | 4069 | 2770 | 4077 | 2698 | 2210 | 1304 | 716 | 465 | 433 | 477 | 244 |
19 | love_live!_school_idol_project | 19167 | 0 | 0 | 0 | 0 | 0 | 6 | 44 | 46 | 1654 | 6789 | 7010 | 3618 |
20 | gundam | 18496 | 277 | 644 | 904 | 3537 | 2364 | 1428 | 1230 | 1385 | 1306 | 2915 | 1578 | 928 |
21 | world_witches_series | 16740 | 1 | 15 | 5 | 1772 | 1546 | 2556 | 2371 | 2039 | 2131 | 1734 | 1880 | 690 |
22 | strike_witches | 15941 | 1 | 15 | 5 | 1771 | 1534 | 2404 | 2226 | 1956 | 2039 | 1654 | 1708 | 628 |
23 | persona | 15073 | 0 | 212 | 323 | 1496 | 3146 | 1906 | 1680 | 3105 | 991 | 984 | 549 | 681 |
24 | neon_genesis_evangelion | 15035 | 94 | 702 | 737 | 1742 | 2479 | 1974 | 1109 | 1955 | 1818 | 922 | 857 | 646 |
25 | to_aru_majutsu_no_index | 14233 | 2 | 10 | 10 | 372 | 1257 | 3441 | 3365 | 1162 | 2572 | 945 | 642 | 455 |
26 | fate/zero | 13061 | 0 | 5 | 75 | 33 | 95 | 59 | 2949 | 7252 | 1553 | 526 | 367 | 147 |
27 | girls_und_panzer | 10545 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 517 | 1815 | 779 | 1045 | 6389 |
28 | lucky_star | 10478 | 1 | 3 | 2972 | 3310 | 1564 | 1119 | 447 | 424 | 211 | 221 | 150 | 56 |
29 | mahou_shoujo_lyrical_nanoha | 10263 | 130 | 1169 | 1113 | 2398 | 1015 | 1186 | 671 | 1194 | 648 | 331 | 273 | 135 |
30 | kill_la_kill | 10219 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2305 | 6147 | 1187 | 580 |
31 | street_fighter | 10211 | 15 | 212 | 140 | 892 | 1045 | 1342 | 840 | 998 | 810 | 1575 | 1376 | 966 |
32 | tales_of_(series) | 10055 | 11 | 115 | 96 | 722 | 1595 | 1199 | 2442 | 1343 | 628 | 416 | 794 | 694 |
33 | inazuma_eleven_(series) | 9987 | 0 | 0 | 0 | 4 | 62 | 774 | 1927 | 2196 | 2494 | 1439 | 848 | 243 |
34 | mahou_shoujo_lyrical_nanoha_strikers | 9561 | 0 | 102 | 2301 | 3070 | 1440 | 911 | 410 | 574 | 263 | 223 | 188 | 79 |
35 | granblue_fantasy | 9479 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | 49 | 3878 | 5551 |
36 | code_geass | 9355 | 0 | 250 | 1123 | 3945 | 1695 | 634 | 211 | 316 | 190 | 317 | 368 | 306 |
37 | touken_ranbu | 9201 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 6335 | 2866 |
38 | umineko_no_naku_koro_ni | 8853 | 0 | 0 | 59 | 633 | 2934 | 3090 | 671 | 279 | 407 | 341 | 249 | 190 |
39 | fire_emblem | 8794 | 2 | 65 | 152 | 734 | 428 | 587 | 281 | 527 | 781 | 951 | 2372 | 1914 |
40 | rozen_maiden | 8762 | 651 | 2125 | 761 | 1271 | 1744 | 611 | 360 | 292 | 460 | 182 | 188 | 117 |
41 | persona_4 | 8740 | 0 | 0 | 0 | 945 | 2122 | 736 | 1057 | 2277 | 450 | 592 | 295 | 266 |
42 | tengen_toppa_gurren_lagann | 8516 | 0 | 1 | 1212 | 1935 | 1504 | 1212 | 716 | 575 | 547 | 397 | 225 | 192 |
43 | monogatari_(series) | 8414 | 0 | 0 | 1 | 15 | 992 | 820 | 616 | 2271 | 1454 | 849 | 787 | 609 |
44 | blazblue | 8268 | 0 | 0 | 0 | 95 | 851 | 1523 | 1007 | 948 | 1810 | 826 | 639 | 569 |
45 | smile_precure! | 8050 | 0 | 0 | 1 | 0 | 0 | 1 | 45 | 5381 | 1606 | 590 | 286 | 140 |
46 | naruto | 7993 | 31 | 1095 | 304 | 794 | 681 | 1369 | 442 | 420 | 341 | 1084 | 903 | 529 |
47 | to_aru_kagaku_no_railgun | 7760 | 0 | 0 | 2 | 21 | 639 | 2033 | 1061 | 603 | 1950 | 693 | 457 | 301 |
48 | bishoujo_senshi_sailor_moon | 7388 | 10 | 168 | 167 | 385 | 308 | 396 | 634 | 805 | 804 | 1619 | 1090 | 1002 |
49 | higurashi_no_naku_koro_ni | 7318 | 125 | 1134 | 689 | 2232 | 879 | 560 | 362 | 275 | 330 | 142 | 319 | 271 |
50 | league_of_legends | 7031 | 0 | 0 | 0 | 0 | 0 | 29 | 162 | 916 | 1590 | 1425 | 1587 | 1322 |
51 | tiger_&_bunny | 7000 | 0 | 0 | 0 | 0 | 0 | 0 | 4794 | 1821 | 229 | 92 | 50 | 14 |
52 | shingeki_no_kyojin | 6619 | 0 | 0 | 0 | 0 | 0 | 2 | 18 | 74 | 5074 | 807 | 460 | 184 |
53 | inazuma_eleven_go | 6402 | 0 | 0 | 0 | 0 | 0 | 0 | 903 | 1775 | 2044 | 1021 | 469 | 190 |
54 | the_legend_of_zelda | 6322 | 1 | 54 | 83 | 404 | 459 | 641 | 877 | 645 | 725 | 1177 | 768 | 488 |
55 | fate/grand_order | 6259 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 2 | 2 | 9 | 1384 | 4862 |
56 | ore_no_imouto_ga_konna_ni_kawaii_wake_ga_nai | 6219 | 0 | 0 | 0 | 2 | 17 | 2159 | 1517 | 653 | 1086 | 359 | 263 | 163 |
57 | sword_art_online | 6177 | 0 | 0 | 0 | 0 | 1 | 10 | 10 | 2251 | 1079 | 1417 | 757 | 652 |
58 | saki | 5991 | 0 | 0 | 4 | 38 | 1358 | 608 | 284 | 1420 | 952 | 788 | 394 | 145 |
59 | dragon_quest | 5979 | 34 | 134 | 166 | 1205 | 948 | 828 | 494 | 558 | 535 | 393 | 366 | 318 |
60 | to_heart_2 | 5908 | 943 | 2091 | 568 | 909 | 534 | 311 | 155 | 130 | 92 | 75 | 59 | 41 |
61 | macross | 5873 | 5 | 37 | 88 | 2244 | 1188 | 778 | 625 | 266 | 150 | 93 | 98 | 301 |
62 | pokemon_bw | 5706 | 0 | 0 | 0 | 0 | 1 | 1729 | 1232 | 1348 | 540 | 340 | 233 | 283 |
63 | persona_3 | 5662 | 0 | 197 | 320 | 524 | 1009 | 1169 | 563 | 764 | 469 | 353 | 139 | 155 |
64 | touhou_(pc-98) | 5617 | 8 | 38 | 47 | 215 | 437 | 768 | 1050 | 790 | 698 | 625 | 566 | 375 |
65 | tsukihime | 5512 | 911 | 710 | 559 | 649 | 416 | 381 | 295 | 552 | 210 | 377 | 327 | 125 |
66 | idolmaster_million_live! | 5419 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 838 | 2272 | 1267 | 1042 |
67 | yuu-gi-ou | 5407 | 2 | 52 | 42 | 354 | 461 | 456 | 476 | 550 | 474 | 747 | 1145 | 648 |
68 | the_king_of_fighters | 5245 | 7 | 241 | 150 | 766 | 670 | 601 | 484 | 479 | 561 | 375 | 403 | 508 |
69 | one_piece | 5201 | 7 | 129 | 181 | 580 | 869 | 930 | 562 | 553 | 401 | 337 | 282 | 370 |
70 | mahou_shoujo_lyrical_nanoha_a's | 5140 | 68 | 774 | 592 | 1041 | 434 | 515 | 219 | 715 | 445 | 203 | 94 | 40 |
71 | rebuild_of_evangelion | 5073 | 1 | 0 | 1 | 23 | 986 | 840 | 364 | 1068 | 896 | 409 | 279 | 206 |
72 | danganronpa | 5045 | 0 | 0 | 0 | 0 | 0 | 24 | 103 | 309 | 1949 | 1152 | 515 | 993 |
73 | guilty_gear | 4982 | 37 | 722 | 167 | 339 | 508 | 345 | 219 | 176 | 434 | 644 | 838 | 553 |
74 | bakemonogatari | 4937 | 0 | 0 | 0 | 15 | 990 | 818 | 605 | 1072 | 556 | 356 | 277 | 248 |
75 | macross_frontier | 4835 | 0 | 0 | 4 | 2182 | 1069 | 625 | 538 | 195 | 84 | 51 | 52 | 35 |
76 | vampire_(game) | 4790 | 19 | 163 | 197 | 596 | 718 | 684 | 459 | 509 | 462 | 321 | 383 | 279 |
77 | little_busters! | 4782 | 6 | 5 | 240 | 620 | 519 | 591 | 339 | 810 | 795 | 481 | 277 | 99 |
78 | clannad | 4780 | 40 | 84 | 425 | 1719 | 1260 | 450 | 162 | 146 | 241 | 117 | 94 | 42 |
79 | gundam_00 | 4758 | 0 | 0 | 411 | 2237 | 1137 | 375 | 226 | 167 | 74 | 62 | 42 | 27 |
80 | ragnarok_online | 4687 | 149 | 469 | 634 | 1242 | 645 | 331 | 372 | 384 | 145 | 137 | 107 | 72 |
81 | axis_powers_hetalia | 4629 | 0 | 0 | 1 | 28 | 953 | 1274 | 933 | 582 | 249 | 234 | 298 | 77 |
82 | overwatch | 4573 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 53 | 126 | 4394 |
83 | yuru_yuri | 4505 | 0 | 0 | 0 | 0 | 3 | 4 | 837 | 1877 | 627 | 328 | 488 | 341 |
84 | heartcatch_precure! | 4417 | 0 | 0 | 0 | 0 | 2 | 1650 | 1115 | 432 | 591 | 379 | 174 | 74 |
85 | black_rock_shooter | 4363 | 0 | 0 | 1 | 574 | 805 | 1070 | 555 | 743 | 134 | 115 | 213 | 153 |
86 | dokidoki!_precure | 4265 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 31 | 3181 | 625 | 229 | 199 |
87 | angel_beats! | 4247 | 0 | 0 | 0 | 0 | 8 | 2396 | 606 | 245 | 156 | 286 | 426 | 124 |
88 | pixiv_fantasia | 4223 | 0 | 0 | 1 | 126 | 390 | 337 | 910 | 579 | 469 | 554 | 681 | 176 |
89 | bleach | 4156 | 33 | 480 | 312 | 954 | 761 | 460 | 293 | 183 | 133 | 159 | 180 | 208 |
90 | kamen_rider | 4131 | 4 | 22 | 18 | 104 | 873 | 655 | 604 | 592 | 368 | 310 | 391 | 190 |
91 | queen's_blade | 4055 | 4 | 53 | 381 | 542 | 893 | 571 | 521 | 404 | 173 | 246 | 172 | 95 |
92 | inazuma_eleven | 4038 | 0 | 0 | 0 | 4 | 62 | 773 | 1233 | 551 | 504 | 453 | 410 | 48 |
93 | final_fantasy_vii | 4027 | 22 | 296 | 181 | 601 | 641 | 506 | 431 | 276 | 153 | 178 | 503 | 239 |
94 | to_love-ru | 4018 | 0 | 27 | 67 | 690 | 425 | 332 | 434 | 530 | 336 | 297 | 469 | 411 |
95 | fate/extra | 3991 | 0 | 0 | 0 | 0 | 34 | 118 | 217 | 547 | 1068 | 447 | 679 | 881 |
96 | mahou_shoujo_madoka_magica_movie | 3964 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 122 | 599 | 2266 | 740 | 237 |
97 | puzzle_&_dragons | 3847 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 4 | 494 | 1498 | 1296 | 555 |
98 | dragon_ball | 3842 | 9 | 144 | 43 | 254 | 423 | 439 | 326 | 264 | 295 | 471 | 672 | 502 |
99 | neptune_(series) | 3800 | 0 | 0 | 0 | 0 | 0 | 70 | 90 | 352 | 482 | 250 | 1245 | 1311 |
100 | splatoon | 3797 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 95 | 2596 | 1106 |
SELECT tags.name AS tag, COUNT(p.id) AS uploads, SUM(YEAR(p.created_at) = 2005) AS uploads_2005, SUM(YEAR(p.created_at) = 2006) AS uploads_2006, SUM(YEAR(p.created_at) = 2007) AS uploads_2007, SUM(YEAR(p.created_at) = 2008) AS uploads_2008, SUM(YEAR(p.created_at) = 2009) AS uploads_2009, SUM(YEAR(p.created_at) = 2010) AS uploads_2010, SUM(YEAR(p.created_at) = 2011) AS uploads_2011, SUM(YEAR(p.created_at) = 2012) AS uploads_2012, SUM(YEAR(p.created_at) = 2013) AS uploads_2013, SUM(YEAR(p.created_at) = 2014) AS uploads_2014, SUM(YEAR(p.created_at) = 2015) AS uploads_2015, SUM(YEAR(p.created_at) = 2016) AS uploads_2016, FROM FLATTEN([danbooru-data:danbooru.posts], tags) AS p JOIN [turing-zone-143603:danbooru_latest.tags] AS t ON p.tags.name = t.name WHERE t.category = 3 GROUP BY tag ORDER BY uploads DESC LIMIT 100
Basically: touhou peaked at 71000-72000 uploads per year in 2010-2012, until kantai_collection overtook it in 2014.
Updated
Alright, so comics: let's quantify exactly how much they affect scores.
BigQuery: https://bigquery.cloud.google.com:443/savedquery/657582419813:7e4e1ec7d09a45729d53b6a7dc51a8ce
Spreadsheet: https://docs.google.com/spreadsheets/d/1ib4PG3ScVN6XAJYKUjFHUTOkvTUbniv4ZP8sgTvgioU/edit?usp=sharing
0 | tag | posts | comics | comic_pct | comics_avg_score | average_score | avg_score_eq | avg_score_s | rating_s_pct |
---|---|---|---|---|---|---|---|---|---|
1 | kantai_collection | 48652 | 8795 | 18.1 | 3.3 | 9.7 | 14.6 | 8.7 | 83.0 |
2 | touhou | 38507 | 4137 | 10.7 | 3.1 | 9.4 | 13.5 | 8.8 | 86.1 |
3 | original | 32516 | 3061 | 9.4 | 1.5 | 9.2 | 12.6 | 7.9 | 71.9 |
4 | idolmaster | 13731 | 1078 | 7.9 | 2.8 | 8.6 | 14.6 | 7.2 | 81.2 |
5 | idolmaster_cinderella_girls | 10168 | 972 | 9.6 | 2.9 | 8.9 | 14.8 | 7.6 | 81.8 |
6 | fate_(series) | 6864 | 293 | 4.3 | 2.3 | 8.8 | 14.2 | 7.8 | 84.6 |
7 | girls_und_panzer | 6407 | 696 | 10.9 | 3.3 | 9.3 | 13.6 | 8.4 | 82.2 |
8 | granblue_fantasy | 5563 | 92 | 1.7 | 4.5 | 10.9 | 13.5 | 9.6 | 66.4 |
9 | pokemon | 5136 | 335 | 6.5 | 4.7 | 7.5 | 10.7 | 6.7 | 80.1 |
10 | love_live! | 5090 | 620 | 12.2 | 1.7 | 8.8 | 14.7 | 7.9 | 87.6 |
11 | fate/grand_order | 4890 | 253 | 5.2 | 2.4 | 8.2 | 11.9 | 7.6 | 86.7 |
12 | overwatch | 4408 | 182 | 4.1 | 5.1 | 11.5 | 15.4 | 10.6 | 81.3 |
13 | vocaloid | 3937 | 36 | 0.9 | 3.4 | 8.3 | 10.1 | 7.9 | 83.2 |
14 | love_live!_school_idol_project | 3641 | 470 | 12.9 | 1.4 | 8.3 | 14.1 | 7.4 | 87.6 |
15 | touken_ranbu | 2873 | 166 | 5.8 | 0.7 | 2.5 | 4.1 | 2.4 | 96.4 |
16 | jojo_no_kimyou_na_bouken | 2678 | 405 | 15.1 | 0.7 | 1.3 | 3.7 | 1.3 | 98.6 |
17 | pokemon_(game) | 2542 | 100 | 3.9 | 6.4 | 7.6 | 10.9 | 6.5 | 76.5 |
18 | re:zero_kara_hajimeru_isekai_seikatsu | 2533 | 26 | 1.0 | 11.7 | 13.7 | 18.6 | 12.5 | 80.8 |
19 | osomatsu-san | 1926 | 194 | 10.1 | 0.9 | 2.0 | 3.7 | 1.9 | 97.0 |
20 | fire_emblem | 1917 | 72 | 3.8 | 2.6 | 6.3 | 14.2 | 5.4 | 89.8 |
21 | final_fantasy | 1810 | 26 | 1.4 | 3.7 | 6.9 | 14.6 | 5.2 | 81.8 |
22 | precure | 1801 | 14 | 0.8 | 3.6 | 4.5 | 8.0 | 3.4 | 76.6 |
23 | osomatsu-kun | 1750 | 183 | 10.5 | 0.7 | 1.7 | 3.6 | 1.7 | 97.3 |
24 | love_live!_sunshine!! | 1497 | 161 | 10.8 | 2.7 | 10.0 | 16.2 | 9.2 | 88.0 |
25 | mahou_shoujo_madoka_magica | 1448 | 155 | 10.7 | 2.1 | 7.2 | 10.7 | 6.7 | 85.7 |
26 | league_of_legends | 1323 | 70 | 5.3 | 2.9 | 10.4 | 14.8 | 8.7 | 72.4 |
27 | fire_emblem_if | 1318 | 55 | 4.2 | 2.9 | 6.3 | 14.0 | 5.6 | 92.0 |
28 | neptune_(series) | 1313 | 17 | 1.3 | 6.3 | 6.4 | 8.6 | 5.8 | 77.8 |
29 | fate/stay_night | 1258 | 51 | 4.1 | 1.3 | 8.1 | 15.0 | 7.1 | 87.6 |
30 | high_school_dxd | 1214 | 1 | 0.1 | 0.0 | 4.1 | 8.4 | 3.7 | 91.7 |
31 | pokemon_sm | 1159 | 32 | 2.8 | 7.1 | 7.0 | 11.0 | 6.1 | 81.9 |
32 | zhan_jian_shao_nyu | 1154 | 45 | 3.9 | 2.6 | 8.2 | 11.2 | 7.2 | 75.2 |
33 | splatoon | 1115 | 235 | 21.1 | 2.8 | 6.3 | 11.8 | 5.7 | 89.7 |
34 | gochuumon_wa_usagi_desu_ka? | 1064 | 16 | 1.5 | 6.3 | 11.0 | 16.1 | 9.3 | 74.6 |
35 | kono_subarashii_sekai_ni_shukufuku_wo! | 1045 | 26 | 2.5 | 8.9 | 15.3 | 18.6 | 12.6 | 54.7 |
36 | idolmaster_million_live! | 1045 | 28 | 2.7 | 3.2 | 8.8 | 12.9 | 7.0 | 69.7 |
37 | danganronpa | 1026 | 22 | 2.1 | 1.3 | 4.1 | 7.7 | 3.9 | 94.9 |
38 | undertale | 1024 | 212 | 20.7 | 3.0 | 4.4 | 7.6 | 4.3 | 96.4 |
39 | idolmaster_cinderella_girls_starlight_stage | 1018 | 89 | 8.7 | 1.5 | 6.4 | 11.1 | 6.1 | 94.7 |
40 | bishoujo_senshi_sailor_moon | 1002 | 12 | 1.2 | 4.5 | 5.2 | 9.4 | 5.0 | 94.0 |
41 | flower_knight_girl | 980 | 22 | 2.2 | 2.7 | 7.0 | 7.9 | 5.9 | 44.0 |
42 | pokemon_go | 977 | 146 | 14.9 | 4.5 | 10.3 | 14.9 | 9.6 | 85.7 |
43 | street_fighter | 968 | 12 | 1.2 | 5.4 | 6.8 | 8.4 | 5.8 | 63.4 |
44 | idolmaster_side-m | 950 | 39 | 4.1 | 0.2 | 1.4 | 5.2 | 1.3 | 97.7 |
45 | gundam | 930 | 79 | 8.5 | 2.4 | 5.2 | 9.3 | 4.2 | 79.8 |
46 | rwby | 899 | 32 | 3.6 | 6.1 | 9.9 | 20.3 | 8.6 | 88.8 |
47 | fate/extra | 887 | 23 | 2.6 | 1.9 | 9.7 | 15.2 | 8.9 | 87.4 |
48 | voiceroid | 778 | 3 | 0.4 | 12.7 | 8.1 | 7.9 | 8.2 | 58.9 |
49 | phantasy_star | 762 | 9 | 1.2 | 5.3 | 8.8 | 11.1 | 8.0 | 73.5 |
50 | phantasy_star_online_2 | 750 | 9 | 1.2 | 5.3 | 8.9 | 11.2 | 8.1 | 73.5 |
51 | koutetsujou_no_kabaneri | 712 | 2 | 0.3 | 10.0 | 12.4 | 17.0 | 11.0 | 75.6 |
52 | persona | 700 | 103 | 14.7 | 0.7 | 5.9 | 12.9 | 4.9 | 87.0 |
53 | world_witches_series | 694 | 51 | 7.3 | 2.2 | 11.2 | 14.2 | 9.4 | 62.1 |
54 | tales_of_(series) | 694 | 126 | 18.2 | 1.2 | 4.1 | 4.8 | 3.8 | 70.2 |
55 | mahou_girls_precure! | 680 | 4 | 0.6 | 6.0 | 4.0 | 7.8 | 3.2 | 82.8 |
56 | musaigen_no_phantom_world | 671 | 6 | 0.9 | 8.5 | 12.5 | 15.0 | 9.9 | 48.3 |
57 | dagashi_kashi | 656 | 11 | 1.7 | 13.8 | 16.2 | 18.9 | 14.3 | 57.6 |
58 | sword_art_online | 652 | 4 | 0.6 | 10.5 | 7.3 | 15.8 | 6.1 | 87.9 |
59 | yuu-gi-ou | 651 | 14 | 2.2 | 1.6 | 6.4 | 12.0 | 4.8 | 77.7 |
60 | neon_genesis_evangelion | 646 | 29 | 4.5 | 0.9 | 8.1 | 11.9 | 7.4 | 85.3 |
61 | strike_witches | 631 | 50 | 7.9 | 2.2 | 11.3 | 14.0 | 9.5 | 61.0 |
62 | monogatari_(series) | 611 | 6 | 1.0 | 4.8 | 13.1 | 20.6 | 10.9 | 77.1 |
63 | kill_la_kill | 582 | 60 | 10.3 | 0.6 | 6.9 | 13.5 | 5.5 | 83.2 |
64 | senran_kagura_(series) | 575 | 1 | 0.2 | 17.0 | 10.2 | 11.9 | 9.0 | 60.0 |
65 | blazblue | 572 | 7.3 | 8.5 | 6.6 | 63.6 | |||
66 | lawson | 568 | 87 | 15.3 | 5.2 | 9.5 | 14.9 | 8.2 | 80.3 |
67 | boku_no_hero_academia | 566 | 42 | 7.4 | 2.1 | 6.6 | 12.8 | 4.9 | 78.8 |
68 | puzzle_&_dragons | 556 | 2 | 0.4 | 2.5 | 8.1 | 12.9 | 6.4 | 75.0 |
69 | guilty_gear | 556 | 1 | 0.2 | 17.0 | 8.5 | 10.9 | 7.8 | 76.8 |
70 | one-punch_man | 548 | 58 | 10.6 | 1.2 | 8.9 | 14.7 | 6.8 | 73.0 |
71 | fate/apocrypha | 540 | 30 | 5.6 | 1.6 | 7.8 | 13.9 | 7.0 | 87.8 |
72 | naruto | 531 | 53 | 10.0 | 0.4 | 4.6 | 8.3 | 3.6 | 78.9 |
73 | new_horizon | 510 | 31 | 6.1 | 9.4 | 14.1 | 19.0 | 11.9 | 69.0 |
74 | the_king_of_fighters | 509 | 4 | 0.8 | 6.0 | 5.1 | 7.6 | 4.3 | 75.8 |
75 | dragon_ball | 502 | 119 | 23.7 | 1.5 | 3.9 | 6.9 | 3.4 | 86.5 |
76 | monster_musume_no_iru_nichijou | 498 | 66 | 13.3 | 2.6 | 8.0 | 10.5 | 6.9 | 70.3 |
77 | the_legend_of_zelda | 495 | 19 | 3.8 | 5.3 | 7.1 | 11.4 | 5.8 | 76.0 |
78 | to_aru_majutsu_no_index | 457 | 10 | 2.2 | 0.8 | 9.7 | 16.8 | 8.6 | 86.9 |
79 | wild_arms | 445 | 0.8 | 7.1 | 0.6 | 96.4 | |||
80 | street_fighter_v | 438 | 1 | 0.2 | 15.0 | 7.6 | 9.4 | 6.5 | 62.3 |
81 | final_fantasy_xiv | 436 | 5 | 1.1 | 1.2 | 15.2 | 19.6 | 13.4 | 71.3 |
82 | to_love-ru | 412 | 12 | 2.9 | 15.3 | 13.6 | 15.8 | 10.6 | 42.7 |
83 | final_fantasy_tactics | 403 | 1 | 0.2 | 1.0 | 1.6 | 6.2 | 1.3 | 94.8 |
84 | fate/kaleid_liner_prisma_illya | 395 | 18 | 4.6 | 6.6 | 16.6 | 22.8 | 11.7 | 55.4 |
85 | sennen_sensou_aigis | 386 | 2 | 0.5 | 7.0 | 8.3 | 12.5 | 6.2 | 66.6 |
86 | guilty_gear_xrd | 385 | 9.2 | 11.8 | 8.4 | 76.6 | |||
87 | aikatsu! | 381 | 6 | 1.6 | 3.5 | 6.5 | 13.7 | 5.7 | 89.5 |
88 | touhou_(pc-98) | 379 | 18 | 4.7 | 2.9 | 6.4 | 7.8 | 6.3 | 96.3 |
89 | pokemon_(anime) | 372 | 19 | 5.1 | 4.1 | 6.9 | 11.5 | 5.1 | 72.8 |
90 | one_piece | 370 | 30 | 8.1 | 0.6 | 4.3 | 7.0 | 3.5 | 77.8 |
91 | marvel | 360 | 15 | 4.2 | 4.6 | 5.1 | 8.6 | 4.8 | 93.1 |
92 | shingeki_no_bahamut | 359 | 1 | 0.3 | 4.0 | 8.8 | 12.3 | 8.2 | 86.4 |
93 | dragon_ball_z | 352 | 101 | 28.7 | 1.3 | 3.4 | 3.5 | 3.4 | 88.9 |
94 | elsword | 348 | 43 | 12.4 | 0.4 | 6.7 | 9.2 | 5.9 | 76.4 |
95 | kumamiko | 344 | 2 | 0.6 | 1.0 | 12.1 | 16.8 | 10.3 | 71.8 |
96 | yuru_yuri | 341 | 8 | 2.3 | 5.3 | 7.5 | 9.8 | 7.3 | 93.5 |
97 | senran_kagura | 339 | 9.4 | 11.3 | 8.3 | 64.6 | |||
98 | super_danganronpa_2 | 339 | 4 | 1.2 | 1.8 | 4.1 | 5.8 | 4.0 | 93.8 |
99 | k-on! | 337 | 77 | 22.8 | 1.2 | 7.1 | 8.2 | 6.8 | 77.4 |
100 | nitroplus | 334 | 6.7 | 8.9 | 5.7 | 68.3 |
SELECT tags.name AS tag, COUNT(p.id) AS posts, FIRST(comics.posts) AS comics, ROUND(FIRST(comics.posts) / COUNT(p.id) * 100, 1) AS comic_pct, FIRST(comics.average_score) AS comics_avg_score, ROUND(AVG(score), 1) AS average_score, ROUND(AVG(IF(rating = "e" OR rating = "q", score, NULL)), 1) as avg_score_eq, ROUND(AVG(IF(rating = "s", score, NULL)), 1) as avg_score_s, ROUND(AVG(rating = "s") * 100, 1) as rating_s_pct, -- FIRST(comics.rating_s_pct) AS comics_rating_s_pct, FROM FLATTEN([danbooru-data:danbooru.posts], tags) AS p JOIN [turing-zone-143603:danbooru_latest.tags] AS t ON p.tags.name = t.name LEFT JOIN ( SELECT p.tags.name AS tag, COUNT(p.id) AS posts, ROUND(AVG(score), 1) AS average_score, ROUND(AVG(rating = "s") * 100, 1) as rating_s_pct FROM FLATTEN([danbooru-data:danbooru.posts], tags) AS p JOIN [turing-zone-143603:danbooru_latest.tags] AS t ON p.tags.name = t.name WHERE t.category = 3 AND YEAR(p.created_at) = 2016 AND p.id IN (SELECT id FROM [danbooru-data:danbooru.posts] WHERE tags.name = "comic") GROUP BY tag ) AS comics ON comics.tag = p.tags.name WHERE TRUE AND t.category = 3 AND YEAR(p.created_at) = 2016 GROUP BY tag ORDER BY posts DESC LIMIT 100
The above table shows what percentage of a copyright was tagged comic, and what the average score is for those comic posts. tl;dr: kantai_collection was 18% comics this year versus 10.7% for touhou, and both had average scores of around 3. That does bring down their rating:s scores. But girls_und_panzer and love_live! were 11%-12% comics too, so touhou doesn't actually have an unusually high number of comics. Just kantai collection does.
What if we exclude comic posts and then compare scores?
BigQuery: https://bigquery.cloud.google.com:443/savedquery/657582419813:8b2680dbc83d4b039ebdffc4c7aa208d
Spreadsheet: https://docs.google.com/spreadsheets/d/1IuyLslH86FH_SYN3irrsXUOv_OhBZyESlaN9PwA9etg/edit?usp=sharing
0 | tag | posts | average_score | avg_score_eq | avg_score_s | rating_s_pct |
---|---|---|---|---|---|---|
1 | kantai_collection | 39857 | 11.1 | 14.8 | 10.2 | 80.0 |
2 | touhou | 34370 | 10.2 | 13.7 | 9.6 | 84.8 |
3 | original | 29455 | 10.0 | 12.7 | 8.9 | 69.7 |
4 | idolmaster | 12653 | 9.1 | 14.8 | 7.7 | 80.6 |
5 | idolmaster_cinderella_girls | 9196 | 9.5 | 15.2 | 8.2 | 81.1 |
6 | fate_(series) | 6571 | 9.1 | 14.3 | 8.1 | 84.1 |
7 | girls_und_panzer | 5711 | 10.0 | 13.7 | 9.2 | 80.6 |
8 | granblue_fantasy | 5471 | 11.0 | 13.6 | 9.8 | 66.3 |
9 | pokemon | 4801 | 7.7 | 10.7 | 6.9 | 79.2 |
10 | fate/grand_order | 4637 | 8.5 | 12.0 | 7.9 | 86.2 |
11 | love_live! | 4470 | 9.8 | 15.0 | 8.9 | 86.2 |
12 | overwatch | 4226 | 11.8 | 15.5 | 10.9 | 80.6 |
13 | vocaloid | 3901 | 8.3 | 10.1 | 8.0 | 83.1 |
14 | love_live!_school_idol_project | 3171 | 9.3 | 14.4 | 8.4 | 86.1 |
15 | touken_ranbu | 2707 | 2.6 | 4.3 | 2.5 | 96.4 |
16 | re:zero_kara_hajimeru_isekai_seikatsu | 2507 | 13.7 | 18.7 | 12.5 | 81.0 |
17 | pokemon_(game) | 2442 | 7.6 | 10.8 | 6.6 | 76.0 |
18 | jojo_no_kimyou_na_bouken | 2273 | 1.4 | 4.1 | 1.4 | 98.5 |
19 | fire_emblem | 1845 | 6.5 | 14.4 | 5.6 | 89.6 |
20 | precure | 1787 | 4.5 | 8.0 | 3.4 | 76.6 |
21 | final_fantasy | 1784 | 7.0 | 14.8 | 5.3 | 81.9 |
22 | osomatsu-san | 1732 | 2.1 | 3.9 | 2.0 | 96.9 |
23 | osomatsu-kun | 1567 | 1.9 | 3.7 | 1.8 | 97.1 |
24 | love_live!_sunshine!! | 1336 | 10.9 | 16.4 | 10.0 | 86.9 |
25 | neptune_(series) | 1296 | 6.4 | 8.6 | 5.7 | 77.5 |
26 | mahou_shoujo_madoka_magica | 1293 | 7.8 | 12.3 | 7.2 | 86.5 |
27 | fire_emblem_if | 1263 | 6.4 | 14.2 | 5.7 | 91.8 |
28 | league_of_legends | 1253 | 10.8 | 15.0 | 9.1 | 71.4 |
29 | high_school_dxd | 1213 | 4.1 | 8.4 | 3.7 | 91.7 |
30 | fate/stay_night | 1207 | 8.4 | 15.0 | 7.4 | 87.2 |
31 | pokemon_sm | 1127 | 7.0 | 10.9 | 6.1 | 81.8 |
32 | zhan_jian_shao_nyu | 1109 | 8.4 | 11.3 | 7.5 | 74.4 |
33 | gochuumon_wa_usagi_desu_ka? | 1048 | 11.1 | 16.5 | 9.3 | 75.2 |
34 | kono_subarashii_sekai_ni_shukufuku_wo! | 1019 | 15.5 | 18.6 | 12.9 | 54.3 |
35 | idolmaster_million_live! | 1017 | 8.9 | 12.9 | 7.2 | 69.4 |
36 | danganronpa | 1004 | 4.1 | 7.9 | 3.9 | 95.0 |
37 | bishoujo_senshi_sailor_moon | 990 | 5.2 | 9.4 | 5.0 | 93.9 |
38 | flower_knight_girl | 958 | 7.1 | 7.9 | 6.0 | 42.8 |
39 | street_fighter | 956 | 6.8 | 8.4 | 5.9 | 63.1 |
40 | idolmaster_cinderella_girls_starlight_stage | 929 | 6.8 | 11.1 | 6.6 | 94.2 |
41 | idolmaster_side-m | 911 | 1.4 | 5.2 | 1.3 | 97.6 |
42 | splatoon | 880 | 7.3 | 12.0 | 6.6 | 87.5 |
43 | rwby | 867 | 10.1 | 20.5 | 8.7 | 88.6 |
44 | fate/extra | 864 | 9.9 | 15.2 | 9.2 | 87.0 |
45 | gundam | 851 | 5.5 | 9.4 | 4.4 | 78.3 |
46 | pokemon_go | 831 | 11.3 | 15.1 | 10.6 | 83.6 |
47 | undertale | 812 | 4.8 | 8.6 | 4.6 | 96.1 |
48 | voiceroid | 775 | 8.1 | 7.9 | 8.2 | 58.8 |
49 | phantasy_star | 753 | 8.9 | 11.1 | 8.1 | 73.2 |
50 | phantasy_star_online_2 | 741 | 9.0 | 11.2 | 8.1 | 73.1 |
51 | koutetsujou_no_kabaneri | 710 | 12.4 | 17.0 | 11.0 | 75.6 |
52 | mahou_girls_precure! | 676 | 4.0 | 7.8 | 3.2 | 83.0 |
53 | musaigen_no_phantom_world | 665 | 12.6 | 15.0 | 10.0 | 48.3 |
54 | sword_art_online | 648 | 7.3 | 15.9 | 6.1 | 88.1 |
55 | dagashi_kashi | 645 | 16.3 | 19.0 | 14.3 | 58.3 |
56 | world_witches_series | 643 | 11.9 | 15.5 | 9.8 | 63.1 |
57 | yuu-gi-ou | 637 | 6.5 | 12.0 | 4.9 | 77.2 |
58 | neon_genesis_evangelion | 617 | 8.4 | 12.2 | 7.7 | 85.1 |
59 | monogatari_(series) | 605 | 13.2 | 20.7 | 11.0 | 77.0 |
60 | persona | 597 | 6.8 | 13.2 | 5.7 | 85.4 |
61 | strike_witches | 581 | 12.1 | 15.3 | 10.1 | 62.0 |
62 | senran_kagura_(series) | 574 | 10.2 | 11.9 | 9.0 | 60.1 |
63 | blazblue | 572 | 7.3 | 8.5 | 6.6 | 63.6 |
64 | tales_of_(series) | 568 | 4.7 | 6.9 | 4.1 | 77.8 |
65 | guilty_gear | 555 | 8.5 | 10.8 | 7.8 | 76.9 |
66 | puzzle_&_dragons | 554 | 8.1 | 12.9 | 6.5 | 74.9 |
67 | boku_no_hero_academia | 524 | 7.0 | 12.7 | 5.3 | 77.9 |
68 | kill_la_kill | 522 | 7.6 | 13.5 | 6.2 | 81.2 |
69 | fate/apocrypha | 510 | 8.2 | 14.3 | 7.3 | 87.5 |
70 | the_king_of_fighters | 505 | 5.1 | 7.5 | 4.3 | 75.8 |
71 | one-punch_man | 490 | 9.9 | 14.7 | 7.8 | 70.0 |
72 | lawson | 481 | 10.3 | 15.0 | 8.9 | 76.9 |
73 | new_horizon | 479 | 14.4 | 19.1 | 12.1 | 67.2 |
74 | naruto | 478 | 5.0 | 8.3 | 4.0 | 76.6 |
75 | the_legend_of_zelda | 476 | 7.2 | 11.4 | 5.8 | 75.2 |
76 | to_aru_majutsu_no_index | 447 | 9.9 | 17.3 | 8.8 | 87.0 |
77 | wild_arms | 445 | 0.8 | 7.1 | 0.6 | 96.4 |
78 | street_fighter_v | 437 | 7.5 | 9.4 | 6.4 | 62.2 |
79 | monster_musume_no_iru_nichijou | 432 | 8.8 | 10.5 | 7.9 | 66.0 |
80 | final_fantasy_xiv | 431 | 15.4 | 19.6 | 13.6 | 71.0 |
81 | final_fantasy_tactics | 402 | 1.6 | 6.2 | 1.3 | 94.8 |
82 | to_love-ru | 400 | 13.5 | 15.8 | 10.6 | 43.5 |
83 | guilty_gear_xrd | 385 | 9.2 | 11.8 | 8.4 | 76.6 |
84 | sennen_sensou_aigis | 384 | 8.3 | 12.5 | 6.2 | 66.7 |
85 | dragon_ball | 383 | 4.6 | 7.6 | 4.1 | 83.8 |
86 | fate/kaleid_liner_prisma_illya | 377 | 17.1 | 23.4 | 11.9 | 54.9 |
87 | aikatsu! | 375 | 6.5 | 13.7 | 5.7 | 89.3 |
88 | touhou_(pc-98) | 361 | 6.5 | 7.8 | 6.5 | 96.1 |
89 | shingeki_no_bahamut | 358 | 8.8 | 12.5 | 8.2 | 86.6 |
90 | pokemon_(anime) | 353 | 7.0 | 11.4 | 5.3 | 72.2 |
91 | marvel | 345 | 5.1 | 8.2 | 4.9 | 93.0 |
92 | kumamiko | 342 | 12.2 | 16.8 | 10.4 | 71.6 |
93 | one_piece | 340 | 4.6 | 7.0 | 3.8 | 75.9 |
94 | senran_kagura | 339 | 9.4 | 11.3 | 8.3 | 64.6 |
95 | super_danganronpa_2 | 335 | 4.1 | 6.1 | 4.0 | 94.3 |
96 | nitroplus | 334 | 6.7 | 8.9 | 5.7 | 68.3 |
97 | yuru_yuri | 333 | 7.5 | 9.9 | 7.4 | 94.0 |
98 | ensemble_stars! | 328 | 2.6 | 3.4 | 2.5 | 97.0 |
99 | ikkitousen | 326 | 4.7 | 9.3 | 4.5 | 94.8 |
100 | monster_girl_encyclopedia | 326 | 8.5 | 9.9 | 7.7 | 61.3 |
SELECT tags.name AS tag, COUNT(p.id) AS posts, ROUND(AVG(score), 1) AS average_score, ROUND(AVG(IF(rating = "e" OR rating = "q", score, NULL)), 1) as avg_score_eq, ROUND(AVG(IF(rating = "s", score, NULL)), 1) as avg_score_s, ROUND(AVG(rating = "s") * 100, 1) as rating_s_pct, FROM FLATTEN([danbooru-data:danbooru.posts], tags) AS p JOIN [turing-zone-143603:danbooru_latest.tags] AS t ON p.tags.name = t.name WHERE TRUE AND t.category = 3 AND YEAR(p.created_at) = 2016 AND p.id NOT IN (SELECT id FROM [danbooru-data:danbooru.posts] WHERE tags.name = "comic") GROUP BY tag ORDER BY posts DESC LIMIT 100
When comics are excluded then kantai_collection and touhou scores do rise, but there are still other copyrights that are even more popular in terms of scores.
Just popping in to say how awesome and informative this is, you guys.
Not to mention how useful it can be: revealing trends, approval biases, user habits, user activity graphs when considering promotions, etc. So many things.
At times like this I wish I knew more than the most basic knowledge required to appreciate a good spreadsheet..
For the curious a flattened post version table has finished syncing now and is available for query. You can contact me for access (I don't want to make it public because it has ip addresses).
I posted this in topic #13112 but I guess I should post it here too. This is a dump of nearly everything publicly available from the API:
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_versions.artist_commentary_versions
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_versions.artist_versions
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_versions.note_versions
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_versions.post_versions
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_versions.wiki_pages_versions
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.artist_commentaries
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.artists
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.bulk_update_requests
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.comments
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.forum_posts
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.forum_topics
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.notes
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.pools
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.post_appeals
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.post_flags
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.posts
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.tag_aliases
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.tag_implications
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.tags
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.uploads
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.user_feedbacks
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.users
https://bigquery.cloud.google.com/table/turing-zone-143603:danbooru_latest.wiki_pages
This should cover everything except bans and mod actions (not available in the API due to a bug), pool versions (skipped it out of laziness), and posts (OP already has that covered). I haven't automated this though so everything's about a week or two out of date at this point.
Crossposting forum #122158 here for reference:
BigQuery: https://bigquery.cloud.google.com/savedquery/657582419813:dbddc4af3565484c9773d076744061be
Spreadsheet: https://docs.google.com/spreadsheets/d/1UXnVGTQ6DUkMh5elzWaEf7luan3MxYXLGIHVb-EzJWw/edit?usp=sharing.
SELECT CONCAT("post #", STRING(id)), CONCAT("http://danbooru.me/posts/", STRING(id)) FROM [danbooru-data:danbooru.posts] WHERE id IN (SELECT id FROM [danbooru-data:danbooru.posts] WHERE REGEXP_MATCH(tags.name, r'.*_\(cosplay\)')) AND id NOT IN (SELECT id FROM [danbooru-data:danbooru.posts] WHERE tags.name = 'cosplay') AND TRUE
@Allynay, the posts table is still updating every 24 hours, but it seems to be getting only a random subset of the posts each time. Today it has 1,419,809 rows, about half what it should, and over the last few days it's ranged from 400,000 to 1,600,000. Do you know what's going on? (Thank you for all your work, regardless.)
norainu said:
@Allynay, the posts table is still updating every 24 hours, but it seems to be getting only a random subset of the posts each time. Today it has 1,419,809 rows, about half what it should, and over the last few days it's ranged from 400,000 to 1,600,000. Do you know what's going on? (Thank you for all your work, regardless.)
Thanks for letting me know, I'll have a look tonight and see what's going on. I suspect the script I'm running is crapping out early for some reason.
Okay, figured it out. For some reason there's a post that had a tag that was in the posts table but not in the tags table. I assumed that would never happened so the script was written to shit itself if it did, since it needed the tags table for the tag ID and category.
If a post is found with a tag that was missing from the tags table, it will now have a tag with ID 0, its original name and category -42.
For reference, the bad posts and tags were: 2351218, 1858377 and 2540508 with anila_(granblue_fantasy)), idunn_(p&d) and mika respectively.
The table should be kept up to date now. Sorry it took so long to respond.
Good find. I think it might be related to the *_(cosplay) auto-tagger. See issue #3307.
@Allynay is this resource still available? When I try to access the dump, I get a message
Unable to find table: danbooru-data:danbooru.posts
Thanks.
kevo said:
@Allynay is this resource still available? When I try to access the dump, I get a message
Thanks.
Yep, it's still there. Can you share the query you're trying to run? Maybe your query is the newer (not Legacy) SQL? If you're using the new SQL you need to use "`danbooru-data.danbooru.posts`" instead.
I'm new to BigQuery and had the same problem as kevo (red banner at the top of the page saying "Unable to find table: danbooru-data:danbooru.posts").
I resolved it by going to the Google APIs dashboard (https://console.developers.google.com/apis/dashboard) (you may need to create a dummy project to be able to access it) then clicking "Enable APIs and services" and enabling the BigQuery API.
I hope this helps anyone else who may have trouble :)