Jump to content

User:Milimetric/Queries/Top ten links by type

From Wikitech

These are the top ten most used templates, images, categories, etc, across all databases, as of:

2021-09-01

Top most used thing as of right now is... (drum roll): https://www.wikidata.org/wiki/Property:P31

Categories

   wiki_db   |                  cl_to                   |  usages 
-------------+------------------------------------------+---------
 commonswiki | Self-published_work                      | 30900841
 commonswiki | Pages_with_maps                          | 21852507
 commonswiki | CC-BY-SA-4.0                             | 18823661
 commonswiki | CC-BY-SA-3.0                             | 10178277
 commonswiki | CC-PD-Mark                               |  7666724
 ruwikinews  | Страницы_с_графиками                     |  6770073
 ruwikinews  | Статистика_страниц                       |  6770048
 commonswiki | CC-BY-SA-2.0                             |  6701735
 commonswiki | CC-BY-2.0                                |  5587234
 commonswiki | Flickr_images_reviewed_by_FlickreviewR_2 |  5263362

External

   wiki_db      |                                         el_to                                             | usages
----------------+-------------------------------------------------------------------------------------------+---------
 commonswiki    | https://creativecommons.org/share-your-work/licensing-considerations/compatible-licenses  | 39754022
 commonswiki    | https://creativecommons.org/licenses/by-sa/4.0/deed.en                                    | 18813397
 viwiki         | https://www.facebook.com/vi.wikipedia.org/                                                | 15098929
 viwiki         | https://www.facebook.com/groups/vi.wikipedia                                              | 15098928
 commonswiki    | https://creativecommons.org/licenses/by-sa/3.0/deed.en                                    | 12562633
 commonswiki    | https://webchat.freenode.net?channels=%23wikimedia-commons&uio=d4                         |  9574330
 commonswiki    | https://creativecommons.org/licenses/by-sa/2.0/deed.en                                    |  8104131
 commonswiki    | https://creativecommons.org/publicdomain/mark/1.0/deed.en                                 |  7673947
 commonswiki    | https://creativecommons.org/licenses/by/2.0/deed.en                                       |  5605128
 commonswiki    | https://creativecommons.org/publicdomain/zero/1.0/deed.en                                 |  4971386

Images

   wiki_db   |            il_to            |  usages 
-------------+-----------------------------+---------
 commonswiki | CC_some_rights_reserved.svg | 50483826
 commonswiki | Cc-by_new_white.svg         | 50457222
 commonswiki | Cc-sa_white.svg             | 38917892
 commonswiki | Openstreetmap_logo.svg      | 25990408
 commonswiki | PD-icon.svg                 | 20640088
 commonswiki | OOjs_UI_icon_help.svg       | 20510717
 commonswiki | Checked_copyright_icon.svg  | 13547272
 commonswiki | Wikidata-logo.svg           | 12370640
 commonswiki | Dialog-warning.svg          | 10691695
 commonswiki | Blue_pencil.svg             | 10271055

Inter-wiki

   wiki_db   |                iwl_title                |  usages 
-------------+-----------------------------------------+---------
 commonswiki | en:Creative_Commons                     | 55371333
 commonswiki | public_domain                           | 18507155
 commonswiki | wiki                                    | 10214933
 commonswiki | Wikipedia:Sign_your_posts_on_talk_pages | 10203982
 commonswiki | wikimedia-commons                       | 10093111
 commonswiki | Wikipedia:IRC_tutorial                  |  9814180
 commonswiki | Flickr                                  |  9583522
 commonswiki | en:public_domain                        |  6889440
 commonswiki | en:Virtual_International_Authority_File |  6522526
 commonswiki | List_of_countries'_copyright_lengths    |  6329653

Inter-language

wiki_db         | ll_lang   | ll_title                      | usages
----------------+-----------+-------------------------------+-------
incubatorwiki   | en        | ''                            | 1641
tawiki          | en        | wikipedia:Main Page           | 1096
iawiktionary    | uk        | ''                            | 1058
enwiki          | ckb       | بەکارھێنەر:سب/بەکارھێنانەوە     | ...
                                                            | 988
commonswiki     | ru        | Димитрий Ростовский           | 955
incubatorwiki   | ru        | ''                            | 872
enwiki          | de        | ''                            | 868
enwiki          | fr        | ''                            | 867
enwiki          | es        | ''                            | 866
enwiki          | zh        | ''                            | 863

Pages

wiki_db         | pl_namespace  | pl_title  | usages
----------------+---------------+-----------+---------
wikidatawiki    | 120           | P31       | 91664564
wikidatawiki    | 120           | P248      | 62555655
wikidatawiki    | 120           | P813      | 49304638
wikidatawiki    | 120           | P577      | 43690626
wikidatawiki    | 120           | P1476     | 41482870
wikidatawiki    | 120           | P854      | 39054849
wikidatawiki    | 120           | P1545     | 37635789
wikidatawiki    | 120           | P1433     | 37512471
wikidatawiki    | 0             | Q13442814 | 37366564
wikidatawiki    | 120           | P2093     | 35700481

Templates

wiki_db     | tl_namespace  | tl_title              | usages
------------+---------------+-----------------------+---------
commonswiki | 10            | Dir                   | 90977044
commonswiki | 828           | No_globals            | 82338538
commonswiki | 828           | DateI18n              | 79582876
commonswiki | 828           | ISOdate               | 79474769
commonswiki | 828           | Core                  | 77727669
commonswiki | 10            | License_template_tag  | 77323838
commonswiki | 10            | Infobox_template_tag  | 76999575
commonswiki | 828           | Information           | 65857936
commonswiki | 10            | Information           | 65759584
commonswiki | 10            | Lang                  | 57584387

Wikidata items by aspect

wiki_db       | eu_entity_id    | eu_aspect | usages
--------------+-----------------+-----------+--------
commonswiki   | Q36524          | D         | 9590993
commonswiki   | Q36524          | L.en      | 9582577
commonswiki   | Q54919          | S         | 6531344
commonswiki   | Q423048         | S         | 5767319
commonswiki   | Q2668072        | L.en      | 5711828
commonswiki   | Q36774          | D         | 5695738
commonswiki   | Q36774          | L.en      | 5687607
commonswiki   | Q2668072        | D         | 5679158
commonswiki   | Q131454         | S         | 5578400
commonswiki   | Q13219454       | S         | 5578396

Code

 select wiki_db,
        cl_to,
        count(*) usages
   from mediawiki_categorylinks
  where snapshot='2021-09'
  group by wiki_db,
        cl_to
  order by usages desc
  limit 10
;

 select wiki_db,
        el_to,
        count(*) usages
   from mediawiki_externallinks
  where snapshot='2021-09'
  group by wiki_db,
        el_to
  order by usages desc
  limit 10
;

 select wiki_db,
        il_to,
        count(*) usages
   from mediawiki_imagelinks
  where snapshot='2021-09'
  group by wiki_db,
        il_to
  order by usages desc
  limit 10
;

 select wiki_db,
        iwl_title,
        count(*) usages
   from mediawiki_iwlinks
  where snapshot='2021-09'
  group by wiki_db,
        iwl_title
  order by usages desc
  limit 10
;

 select wiki_db,
        ll_lang,
        ll_title,
        count(*) usages
   from mediawiki_langlinks
  where snapshot='2021-09'
  group by wiki_db,
        ll_lang,
        ll_title
  order by usages desc
  limit 10
;

 select wiki_db,
        pl_namespace,
        pl_title,
        count(*) usages
   from mediawiki_pagelinks
  where snapshot='2021-09'
  group by wiki_db,
        pl_namespace,
        pl_title
  order by usages desc
  limit 10
;

 select wiki_db,
        tl_namespace,
        tl_title,
        count(*) usages
   from mediawiki_templatelinks
  where snapshot='2021-09'
  group by wiki_db,
        tl_namespace,
        tl_title
  order by usages desc
  limit 10
;

 select wiki_db,
        eu_entity_id,
        eu_aspect,
        count(*) usages
   from mediawiki_wbc_entity_usage
  where snapshot='2021-09'
  group by wiki_db,
        eu_entity_id,
        eu_aspect
  order by usages desc
  limit 10
;