Danbooru

BigQuery dataset (queryable dump)

Posted under General

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

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?

Top 100 Copyrights of 2016

Spreadsheet: https://docs.google.com/spreadsheets/d/1N01syQ1ulSGdNnA1Iv-TIn5ktdVgukl5jDxzSUlxICA/edit?usp=sharing
BigQuery: https://bigquery.cloud.google.com:443/savedquery/657582419813:43ad5d035f6040d6a314e387125dd36b

0tagpoststotal_scoreaverage_scoreaverage_score_eaverage_score_qaverage_score_srating_e_pctrating_q_pctrating_s_pct
1kantai_collection485204687589.713.415.08.74.412.583.0
2touhou384003621579.412.114.08.84.09.986.1
3original324022988349.212.712.57.911.217.071.8
4idolmaster136871175688.614.214.87.27.111.681.3
5idolmaster_cinderella_girls10132897318.914.814.87.56.411.781.9
6fate_(series)6831597578.715.913.47.84.510.984.6
7girls_und_panzer6389592919.313.013.88.44.912.982.2
8granblue_fantasy55516050910.913.613.49.614.918.766.4
9pokemon5121382087.59.412.16.710.89.180.1
10love_live!5055442958.815.714.17.93.58.987.6
11fate/grand_order4862395078.112.611.57.62.910.486.7
12overwatch43945063311.514.416.210.67.710.981.4
13vocaloid3922324368.37.412.37.97.99.083.1
14love_live!_school_idol_project3618298318.215.013.77.42.89.687.6
15touken_ranbu286670852.54.43.82.42.01.796.4
16jojo_no_kimyou_na_bouken266634431.33.24.21.30.50.998.6
17pokemon_(game)2534190967.59.911.96.512.710.876.5
18re:zero_kara_hajimeru_isekai_seikatsu25203440013.717.819.012.56.612.680.8
19osomatsu-san192437842.03.04.01.90.92.197.0
20fire_emblem1914121276.318.410.55.44.75.589.8
21final_fantasy1805124576.915.813.65.27.810.481.8
22precure179981024.58.08.03.45.817.676.5
23osomatsu-kun174830281.72.83.81.70.72.197.3
24love_live!_sunshine!!1486147649.916.715.69.14.97.088.1
25mahou_shoujo_madoka_magica1446104357.210.410.86.64.110.285.7
26league_of_legends13221369110.413.715.68.712.415.272.4
27fire_emblem_if131682266.318.510.65.63.44.692.0
28neptune_(series)131183716.47.89.45.811.410.977.7
29fate/stay_night1254101658.111.117.27.14.57.987.6
30high_school_dxd121349554.116.77.33.71.07.291.8
31pokemon_sm115680357.010.211.96.110.57.781.8
32zhan_jian_shao_nyu115494268.213.210.87.24.120.775.2
33splatoon110670096.311.612.05.74.95.589.6
34gochuumon_wa_usagi_desu_ka?10611170011.016.216.19.34.920.474.7
35idolmaster_million_live!104290818.712.013.77.017.313.069.8
36kono_subarashii_sekai_ni_shukufuku_wo!10411595715.318.918.412.715.929.454.8
37undertale102345104.48.17.34.31.72.096.4
38idolmaster_cinderella_girls_starlight_stage100963776.38.912.46.12.13.294.7
39bishoujo_senshi_sailor_moon100252445.29.19.55.02.13.994.0
40danganronpa99339854.08.87.43.80.84.394.9
41flower_knight_girl98068317.07.511.55.851.54.544.0
42pokemon_go9761007610.314.315.39.65.58.885.7
43street_fighter96665536.88.18.75.814.522.063.5
44idolmaster_side-m95012891.45.74.81.30.91.497.7
45gundam92848605.210.28.94.25.314.979.8
46rwby89889029.922.813.78.68.13.188.8
47fate/extra88185769.714.815.48.92.89.887.4
48voiceroid77662518.15.311.08.222.718.658.8
49phantasy_star76167208.810.611.38.07.918.573.6
50phantasy_star_online_274966748.910.711.48.17.718.773.6
51koutetsujou_no_kabaneri711883712.421.416.011.04.420.175.5
52tales_of_(series)69428344.14.45.53.820.98.970.2
53world_witches_series690766911.113.114.49.311.026.862.2
54persona68140035.914.112.04.86.36.886.9
55mahou_girls_precure!67927024.09.07.23.25.411.882.8
56musaigen_no_phantom_world670839112.514.415.49.923.428.248.4
57dagashi_kashi6541060116.219.418.614.315.127.257.6
58sword_art_online65247587.317.214.46.15.86.387.9
59yuu-gi-ou64841156.412.811.34.710.611.677.8
60neon_genesis_evangelion64651848.015.210.37.44.610.185.3
61strike_witches628706111.213.114.39.512.126.961.0
62monogatari_(series)609797513.122.120.110.96.116.677.3
63kill_la_kill58039776.913.613.55.54.312.683.1
64senran_kagura_(series)575581310.111.711.99.08.231.860.0
65blazblue56941397.34.712.66.619.017.463.6
66lawson56853919.514.415.18.26.013.780.3
67boku_no_hero_academia56637356.613.712.34.97.813.478.8
68puzzle_&_dragons55544528.012.413.06.45.619.575.0
69guilty_gear55347078.510.710.97.85.817.576.7
70one-punch_man54548558.912.915.36.88.818.372.8
71fate/apocrypha53841967.812.513.87.02.010.087.9
72naruto52924274.66.512.33.614.66.678.8
73new_horizon510718814.120.318.311.911.219.869.0
74the_king_of_fighters50825725.17.57.54.37.516.775.8
75dragon_ball50219563.93.69.63.46.07.686.5
76monster_musume_no_iru_nichijou49839808.011.810.06.98.221.570.3
77the_legend_of_zelda48835037.211.311.75.812.111.776.2
78to_aru_majutsu_no_index45544199.717.316.78.65.37.787.0
79wild_arms4453700.87.26.80.62.21.396.4
80street_fighter_v43833027.59.29.46.414.223.562.3
81final_fantasy_xiv435659015.120.318.513.415.213.671.3
82to_love-ru411557813.610.418.310.618.239.242.6
83final_fantasy_tactics4036261.67.74.91.32.52.794.8
84fate/kaleid_liner_prisma_illya389644216.625.320.711.521.123.155.8
85sennen_sensou_aigis38631958.311.613.06.211.122.366.6
86guilty_gear_xrd38235079.211.611.98.45.218.376.4
87aikatsu!38024696.513.513.75.72.18.489.5
88touhou_(pc-98)37523866.47.37.96.30.82.996.3
89pokemon_(anime)37225426.812.610.55.113.214.072.8
90one_piece37015754.38.56.73.53.518.677.8
91marvel35918335.15.89.44.81.75.393.0
92shingeki_no_bahamut35931208.711.812.68.14.78.986.4
93dragon_ball_z35212033.41.55.93.46.34.888.9
94elsword34523276.78.79.76.07.815.476.8
95kumamiko344417712.119.515.910.36.721.571.8
96yuru_yuri34125457.59.49.97.31.55.093.5
97senran_kagura33931469.310.411.48.28.327.164.6
98k-on!33723747.08.18.36.713.19.577.4
99youkai_watch33311703.58.36.63.15.74.290.1
100transformers3326572.01.07.31.90.31.298.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:

Most Uploaded Copyrights 2005-2016

BigQuery: https://bigquery.cloud.google.com:443/savedquery/657582419813:17937b92402b40578136eae1eb018df0
Spreadsheet: https://docs.google.com/spreadsheets/d/1eem8FbABaRyEa5xMy6R9BHzcfe5vT0N4u9JEo5BC26U/edit?usp=sharing

0taguploadsuploads_2005uploads_2006uploads_2007uploads_2008uploads_2009uploads_2010uploads_2011uploads_2012uploads_2013uploads_2014uploads_2015uploads_2016
1touhou521213117428696956350565687271151726357119154681549765525238400
2original266082190153417968156591775221278244283123631472364074023832402
3kantai_collection1974970000000015549630207040848520
4vocaloid757850014084984121751260010276118138189556548533922
5idolmaster701078321286625942855322841239161872794581511313687
6mahou_shoujo_madoka_magica480650000092240285515535698431381446
7fate_(series)44499814213615131702122411454505104714606379757556831
8pokemon363979119143155227305647357866183709403531365121
9idolmaster_cinderella_girls35867000001354886514741421152410132
10precure29604733092193888912118323767306475435830071799
11jojo_no_kimyou_na_bouken265775286424037540027778310316550359202666
12k-on!22991000552127470444431381231687467337
13fate/stay_night2114178621251441166611051003197229071497236030251254
14love_live!2075100000644461656679771475055
15pokemon_(game)20690227275589843428223143322407261315472534
16lyrical_nanoha2026413012463287520923482091122320351087665679264
17final_fantasy196848291587640852864224918061449953107315271805
18suzumiya_haruhi_no_yuuutsu194663406927704077269822101304716465433477244
19love_live!_school_idol_project1916700000644461654678970103618
20gundam1849627764490435372364142812301385130629151578928
21world_witches_series16740115517721546255623712039213117341880690
22strike_witches15941115517711534240422261956203916541708628
23persona15073021232314963146190616803105991984549681
24neon_genesis_evangelion1503594702737174224791974110919551818922857646
25to_aru_majutsu_no_index142332101037212573441336511622572945642455
26fate/zero130610575339559294972521553526367147
27girls_und_panzer105450000000517181577910456389
28lucky_star1047813297233101564111944742421122115056
29mahou_shoujo_lyrical_nanoha10263130116911132398101511866711194648331273135
30kill_la_kill1021900000000230561471187580
31street_fighter10211152121408921045134284099881015751376966
32tales_of_(series)1005511115967221595119924421343628416794694
33inazuma_eleven_(series)99870004627741927219624941439848243
34mahou_shoujo_lyrical_nanoha_strikers9561010223013070144091141057426322318879
35granblue_fantasy94790000000014938785551
36code_geass93550250112339451695634211316190317368306
37touken_ranbu9201000000000063352866
38umineko_no_naku_koro_ni8853005963329343090671279407341249190
39fire_emblem879426515273442858728152778195123721914
40rozen_maiden8762651212576112711744611360292460182188117
41persona_48740000945212273610572277450592295266
42tengen_toppa_gurren_lagann8516011212193515041212716575547397225192
43monogatari_(series)84140011599282061622711454849787609
44blazblue826800095851152310079481810826639569
45smile_precure!80500010014553811606590286140
46naruto799331109530479468113694424203411084903529
47to_aru_kagaku_no_railgun776000221639203310616031950693457301
48bishoujo_senshi_sailor_moon738810168167385308396634805804161910901002
49higurashi_no_naku_koro_ni731812511346892232879560362275330142319271
50league_of_legends703100000291629161590142515871322
51tiger_&_bunny700000000047941821229925014
52shingeki_no_kyojin661900000218745074807460184
53inazuma_eleven_go6402000000903177520441021469190
54the_legend_of_zelda6322154834044596418776457251177768488
55fate/grand_order6259000000022913844862
56ore_no_imouto_ga_konna_ni_kawaii_wake_ga_nai6219000217215915176531086359263163
57sword_art_online6177000011010225110791417757652
58saki59910043813586082841420952788394145
59dragon_quest5979341341661205948828494558535393366318
60to_heart_25908943209156890953431115513092755941
61macross587353788224411887786252661509398301
62pokemon_bw570600001172912321348540340233283
63persona_35662019732052410091169563764469353139155
64touhou_(pc-98)5617838472154377681050790698625566375
65tsukihime5512911710559649416381295552210377327125
66idolmaster_million_live!541900000000838227212671042
67yuu-gi-ou5407252423544614564765504747471145648
68the_king_of_fighters52457241150766670601484479561375403508
69one_piece52017129181580869930562553401337282370
70mahou_shoujo_lyrical_nanoha_a's51406877459210414345152197154452039440
71rebuild_of_evangelion5073101239868403641068896409279206
72danganronpa5045000002410330919491152515993
73guilty_gear498237722167339508345219176434644838553
74bakemonogatari4937000159908186051072556356277248
75macross_frontier48350042182106962553819584515235
76vampire_(game)479019163197596718684459509462321383279
77little_busters!47826524062051959133981079548127799
78clannad47804084425171912604501621462411179442
79gundam_004758004112237113737522616774624227
80ragnarok_online4687149469634124264533137238414513710772
81axis_powers_hetalia462900128953127493358224923429877
82overwatch4573000000000531264394
83yuru_yuri45050000348371877627328488341
84heartcatch_precure!4417000021650111543259137917474
85black_rock_shooter43630015748051070555743134115213153
86dokidoki!_precure42650000000313181625229199
87angel_beats!4247000082396606245156286426124
88pixiv_fantasia4223001126390337910579469554681176
89bleach415633480312954761460293183133159180208
90kamen_rider413142218104873655604592368310391190
91queen's_blade405545338154289357152140417324617295
92inazuma_eleven4038000462773123355150445341048
93final_fantasy_vii402722296181601641506431276153178503239
94to_love-ru401802767690425332434530336297469411
95fate/extra39910000341182175471068447679881
96mahou_shoujo_madoka_magica_movie396400000001225992266740237
97puzzle_&_dragons38470000000449414981296555
98dragon_ball3842914443254423439326264295471672502
99neptune_(series)380000000709035248225012451311
100splatoon37970000000009525961106
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.

Top Copyrights of 2016 (Including Posts Tagged "comic")

BigQuery: https://bigquery.cloud.google.com:443/savedquery/657582419813:7e4e1ec7d09a45729d53b6a7dc51a8ce
Spreadsheet: https://docs.google.com/spreadsheets/d/1ib4PG3ScVN6XAJYKUjFHUTOkvTUbniv4ZP8sgTvgioU/edit?usp=sharing

0tagpostscomicscomic_pctcomics_avg_scoreaverage_scoreavg_score_eqavg_score_srating_s_pct
1kantai_collection48652879518.13.39.714.68.783.0
2touhou38507413710.73.19.413.58.886.1
3original3251630619.41.59.212.67.971.9
4idolmaster1373110787.92.88.614.67.281.2
5idolmaster_cinderella_girls101689729.62.98.914.87.681.8
6fate_(series)68642934.32.38.814.27.884.6
7girls_und_panzer640769610.93.39.313.68.482.2
8granblue_fantasy5563921.74.510.913.59.666.4
9pokemon51363356.54.77.510.76.780.1
10love_live!509062012.21.78.814.77.987.6
11fate/grand_order48902535.22.48.211.97.686.7
12overwatch44081824.15.111.515.410.681.3
13vocaloid3937360.93.48.310.17.983.2
14love_live!_school_idol_project364147012.91.48.314.17.487.6
15touken_ranbu28731665.80.72.54.12.496.4
16jojo_no_kimyou_na_bouken267840515.10.71.33.71.398.6
17pokemon_(game)25421003.96.47.610.96.576.5
18re:zero_kara_hajimeru_isekai_seikatsu2533261.011.713.718.612.580.8
19osomatsu-san192619410.10.92.03.71.997.0
20fire_emblem1917723.82.66.314.25.489.8
21final_fantasy1810261.43.76.914.65.281.8
22precure1801140.83.64.58.03.476.6
23osomatsu-kun175018310.50.71.73.61.797.3
24love_live!_sunshine!!149716110.82.710.016.29.288.0
25mahou_shoujo_madoka_magica144815510.72.17.210.76.785.7
26league_of_legends1323705.32.910.414.88.772.4
27fire_emblem_if1318554.22.96.314.05.692.0
28neptune_(series)1313171.36.36.48.65.877.8
29fate/stay_night1258514.11.38.115.07.187.6
30high_school_dxd121410.10.04.18.43.791.7
31pokemon_sm1159322.87.17.011.06.181.9
32zhan_jian_shao_nyu1154453.92.68.211.27.275.2
33splatoon111523521.12.86.311.85.789.7
34gochuumon_wa_usagi_desu_ka?1064161.56.311.016.19.374.6
35kono_subarashii_sekai_ni_shukufuku_wo!1045262.58.915.318.612.654.7
36idolmaster_million_live!1045282.73.28.812.97.069.7
37danganronpa1026222.11.34.17.73.994.9
38undertale102421220.73.04.47.64.396.4
39idolmaster_cinderella_girls_starlight_stage1018898.71.56.411.16.194.7
40bishoujo_senshi_sailor_moon1002121.24.55.29.45.094.0
41flower_knight_girl980222.22.77.07.95.944.0
42pokemon_go97714614.94.510.314.99.685.7
43street_fighter968121.25.46.88.45.863.4
44idolmaster_side-m950394.10.21.45.21.397.7
45gundam930798.52.45.29.34.279.8
46rwby899323.66.19.920.38.688.8
47fate/extra887232.61.99.715.28.987.4
48voiceroid77830.412.78.17.98.258.9
49phantasy_star76291.25.38.811.18.073.5
50phantasy_star_online_275091.25.38.911.28.173.5
51koutetsujou_no_kabaneri71220.310.012.417.011.075.6
52persona70010314.70.75.912.94.987.0
53world_witches_series694517.32.211.214.29.462.1
54tales_of_(series)69412618.21.24.14.83.870.2
55mahou_girls_precure!68040.66.04.07.83.282.8
56musaigen_no_phantom_world67160.98.512.515.09.948.3
57dagashi_kashi656111.713.816.218.914.357.6
58sword_art_online65240.610.57.315.86.187.9
59yuu-gi-ou651142.21.66.412.04.877.7
60neon_genesis_evangelion646294.50.98.111.97.485.3
61strike_witches631507.92.211.314.09.561.0
62monogatari_(series)61161.04.813.120.610.977.1
63kill_la_kill5826010.30.66.913.55.583.2
64senran_kagura_(series)57510.217.010.211.99.060.0
65blazblue5727.38.56.663.6
66lawson5688715.35.29.514.98.280.3
67boku_no_hero_academia566427.42.16.612.84.978.8
68puzzle_&_dragons55620.42.58.112.96.475.0
69guilty_gear55610.217.08.510.97.876.8
70one-punch_man5485810.61.28.914.76.873.0
71fate/apocrypha540305.61.67.813.97.087.8
72naruto5315310.00.44.68.33.678.9
73new_horizon510316.19.414.119.011.969.0
74the_king_of_fighters50940.86.05.17.64.375.8
75dragon_ball50211923.71.53.96.93.486.5
76monster_musume_no_iru_nichijou4986613.32.68.010.56.970.3
77the_legend_of_zelda495193.85.37.111.45.876.0
78to_aru_majutsu_no_index457102.20.89.716.88.686.9
79wild_arms4450.87.10.696.4
80street_fighter_v43810.215.07.69.46.562.3
81final_fantasy_xiv43651.11.215.219.613.471.3
82to_love-ru412122.915.313.615.810.642.7
83final_fantasy_tactics40310.21.01.66.21.394.8
84fate/kaleid_liner_prisma_illya395184.66.616.622.811.755.4
85sennen_sensou_aigis38620.57.08.312.56.266.6
86guilty_gear_xrd3859.211.88.476.6
87aikatsu!38161.63.56.513.75.789.5
88touhou_(pc-98)379184.72.96.47.86.396.3
89pokemon_(anime)372195.14.16.911.55.172.8
90one_piece370308.10.64.37.03.577.8
91marvel360154.24.65.18.64.893.1
92shingeki_no_bahamut35910.34.08.812.38.286.4
93dragon_ball_z35210128.71.33.43.53.488.9
94elsword3484312.40.46.79.25.976.4
95kumamiko34420.61.012.116.810.371.8
96yuru_yuri34182.35.37.59.87.393.5
97senran_kagura3399.411.38.364.6
98super_danganronpa_233941.21.84.15.84.093.8
99k-on!3377722.81.27.18.26.877.4
100nitroplus3346.78.95.768.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?

Top Copyrights of 2016 (Excluding Posts Tagged "comic")

BigQuery: https://bigquery.cloud.google.com:443/savedquery/657582419813:8b2680dbc83d4b039ebdffc4c7aa208d
Spreadsheet: https://docs.google.com/spreadsheets/d/1IuyLslH86FH_SYN3irrsXUOv_OhBZyESlaN9PwA9etg/edit?usp=sharing

0tagpostsaverage_scoreavg_score_eqavg_score_srating_s_pct
1kantai_collection3985711.114.810.280.0
2touhou3437010.213.79.684.8
3original2945510.012.78.969.7
4idolmaster126539.114.87.780.6
5idolmaster_cinderella_girls91969.515.28.281.1
6fate_(series)65719.114.38.184.1
7girls_und_panzer571110.013.79.280.6
8granblue_fantasy547111.013.69.866.3
9pokemon48017.710.76.979.2
10fate/grand_order46378.512.07.986.2
11love_live!44709.815.08.986.2
12overwatch422611.815.510.980.6
13vocaloid39018.310.18.083.1
14love_live!_school_idol_project31719.314.48.486.1
15touken_ranbu27072.64.32.596.4
16re:zero_kara_hajimeru_isekai_seikatsu250713.718.712.581.0
17pokemon_(game)24427.610.86.676.0
18jojo_no_kimyou_na_bouken22731.44.11.498.5
19fire_emblem18456.514.45.689.6
20precure17874.58.03.476.6
21final_fantasy17847.014.85.381.9
22osomatsu-san17322.13.92.096.9
23osomatsu-kun15671.93.71.897.1
24love_live!_sunshine!!133610.916.410.086.9
25neptune_(series)12966.48.65.777.5
26mahou_shoujo_madoka_magica12937.812.37.286.5
27fire_emblem_if12636.414.25.791.8
28league_of_legends125310.815.09.171.4
29high_school_dxd12134.18.43.791.7
30fate/stay_night12078.415.07.487.2
31pokemon_sm11277.010.96.181.8
32zhan_jian_shao_nyu11098.411.37.574.4
33gochuumon_wa_usagi_desu_ka?104811.116.59.375.2
34kono_subarashii_sekai_ni_shukufuku_wo!101915.518.612.954.3
35idolmaster_million_live!10178.912.97.269.4
36danganronpa10044.17.93.995.0
37bishoujo_senshi_sailor_moon9905.29.45.093.9
38flower_knight_girl9587.17.96.042.8
39street_fighter9566.88.45.963.1
40idolmaster_cinderella_girls_starlight_stage9296.811.16.694.2
41idolmaster_side-m9111.45.21.397.6
42splatoon8807.312.06.687.5
43rwby86710.120.58.788.6
44fate/extra8649.915.29.287.0
45gundam8515.59.44.478.3
46pokemon_go83111.315.110.683.6
47undertale8124.88.64.696.1
48voiceroid7758.17.98.258.8
49phantasy_star7538.911.18.173.2
50phantasy_star_online_27419.011.28.173.1
51koutetsujou_no_kabaneri71012.417.011.075.6
52mahou_girls_precure!6764.07.83.283.0
53musaigen_no_phantom_world66512.615.010.048.3
54sword_art_online6487.315.96.188.1
55dagashi_kashi64516.319.014.358.3
56world_witches_series64311.915.59.863.1
57yuu-gi-ou6376.512.04.977.2
58neon_genesis_evangelion6178.412.27.785.1
59monogatari_(series)60513.220.711.077.0
60persona5976.813.25.785.4
61strike_witches58112.115.310.162.0
62senran_kagura_(series)57410.211.99.060.1
63blazblue5727.38.56.663.6
64tales_of_(series)5684.76.94.177.8
65guilty_gear5558.510.87.876.9
66puzzle_&_dragons5548.112.96.574.9
67boku_no_hero_academia5247.012.75.377.9
68kill_la_kill5227.613.56.281.2
69fate/apocrypha5108.214.37.387.5
70the_king_of_fighters5055.17.54.375.8
71one-punch_man4909.914.77.870.0
72lawson48110.315.08.976.9
73new_horizon47914.419.112.167.2
74naruto4785.08.34.076.6
75the_legend_of_zelda4767.211.45.875.2
76to_aru_majutsu_no_index4479.917.38.887.0
77wild_arms4450.87.10.696.4
78street_fighter_v4377.59.46.462.2
79monster_musume_no_iru_nichijou4328.810.57.966.0
80final_fantasy_xiv43115.419.613.671.0
81final_fantasy_tactics4021.66.21.394.8
82to_love-ru40013.515.810.643.5
83guilty_gear_xrd3859.211.88.476.6
84sennen_sensou_aigis3848.312.56.266.7
85dragon_ball3834.67.64.183.8
86fate/kaleid_liner_prisma_illya37717.123.411.954.9
87aikatsu!3756.513.75.789.3
88touhou_(pc-98)3616.57.86.596.1
89shingeki_no_bahamut3588.812.58.286.6
90pokemon_(anime)3537.011.45.372.2
91marvel3455.18.24.993.0
92kumamiko34212.216.810.471.6
93one_piece3404.67.03.875.9
94senran_kagura3399.411.38.364.6
95super_danganronpa_23354.16.14.094.3
96nitroplus3346.78.95.768.3
97yuru_yuri3337.59.97.494.0
98ensemble_stars!3282.63.42.597.0
99ikkitousen3264.79.34.594.8
100monster_girl_encyclopedia3268.59.97.761.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:

BigQuery Dump

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:

Missing cosplay tags (*_(cosplay) -cosplay)

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.donmai.us/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.

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 :)

1 2 3