Page MenuHomePhabricator

DBQ-101 Templates transcluded on the most User pages
Closed, ResolvedPublic

Description

This issue was converted from https://jira.toolserver.org/browse/DBQ-101.
Summary: Templates transcluded on the most User pages
Issue type: Task - A task that needs to be done.
Priority: Minor
Status: Done
Assignee: EdoDodo <dodo.wikipedia@gmail.com>


From: Paolo Massa <massa@fbk.eu>

Date: Thu, 09 Sep 2010 12:31:26

I've seen the list of Templates with the most transclusions at http://en.wikipedia.org/wiki/Wikipedia:Database_reports/Templates_transcluded_on_the_most_pages
Very interesting!

The SQL query is at http://en.wikipedia.org/wiki/Wikipedia:Database_reports/Templates_transcluded_on_the_most_pages/Configuration
and I copy and paste it here:

SELECT
tl_title,
COUNT![][1]
FROM templatelinks
WHERE tl_namespace = 10
GROUP BY tl_title
ORDER BY COUNT![][1] DESC
LIMIT 1000;

I would like to see the results of a very similar query, for getting Templates with the most transclusions on pages in namespace User: , and in namespace User_talk: (separately), i.e. two separate lists.

I modify a bit the SQL for the previous query, basically just by changing tl=namespace = 2 (and 3). I don't know if this is correct so I ask you to check .

Query for User:

SELECT
tl_title,
COUNT![][1]
FROM templatelinks
WHERE tl_namespace = 2
GROUP BY tl_title
ORDER BY COUNT![][1] DESC
LIMIT 1000;

Query for User_talk:

SELECT
tl_title,
COUNT![][1]
FROM templatelinks
WHERE tl_namespace = 3
GROUP BY tl_title
ORDER BY COUNT![][1] DESC
LIMIT 1000;

The format can be the same as the original query or a CVS file, it is the same.

[1]: https://jira.toolserver.org/images/icons/emoticons/star_yellow.gif

Version: unspecified
Severity: minor

Details

Reference
bz59353

Event Timeline

bzimport raised the priority of this task from to Needs Triage.Nov 22 2014, 2:25 AM
bzimport set Reference to bz59353.

From: EdoDodo <dodo.wikipedia@gmail.com>

Date: Sun, 12 Sep 2010 16:54:56

Hmm... Actually, I think that wouldn't work. That is changing the query to look for transcluded user pages, instead of transcluded templates. I think this query should work:

SELECT
tl_title,
COUNT![][1]
FROM templatelinks LEFT JOIN page ON tl_from = page_id
WHERE tl_namespace = 10 AND page_namespace = 2
GROUP BY tl_title
ORDER BY COUNT![][1] DESC
LIMIT 1000;

Running it now (along with a slightly modified one for user talk), will post results in a CSV file when it is done.

[1]: https://jira.toolserver.org/images/icons/emoticons/star_yellow.gif

From: EdoDodo <dodo.wikipedia@gmail.com>

Date: Sun, 12 Sep 2010 17:03:40

Query results are attached. Will close this issue, if there is something wrong with the results, or my query, feel free to reopen.


From: Paolo Massa <massa@fbk.eu>

Date: Tue, 14 Sep 2010 07:05:59

Thanks EdoDodo! This is precisely what I needed, I already started digging in the results! Thanks!

The query sparked an additional curiosity: on toolserver, you have access to databases for every wikipedia in every language, right?

If yes, would it be possible to run the same query in other wikipedias, for example in the it.wikipedia, es.wikipedia, fr.wikipedia, de.wikipedia, ja.wikipedia, zh.wikipedia?

Thanks!


From: EdoDodo <dodo.wikipedia@gmail.com>

Date: Wed, 15 Sep 2010 12:02:20

Hey, yeah, I do have access to all the databases, so I could run it for those other Wikipedias. I'm not entirely sure if the namespace numbers are the same across all Wikipedias, though. Anyway, if you give me a list of the wikis you'd like me to run the query on I'll look into it and attach the results.


From: DaB. <dab@ts.wikimedia.org>

Date: Wed, 15 Sep 2010 12:14:19

Every namespace below 100 is identical on all wikis AFAIK.


From: Paolo Massa <massa@fbk.eu>

Date: Wed, 15 Sep 2010 12:43:21

Great!

So i would like to see results for it es fr de ja zh, and, if possible, also for vec (wikipedia in venetian, a local Italian dialect), that is a small wikipedia but that I did some research about.

Both for user: and user_talk: would be great!

Thanks!


From: EdoDodo <dodo.wikipedia@gmail.com>

Date: Wed, 15 Sep 2010 13:31:02

Okay, working on it, will post the upload the results later today, or tomorrow.


From: EdoDodo <dodo.wikipedia@gmail.com>

Date: Wed, 15 Sep 2010 17:08:40

All of the results are in the attached zip file.


From: Paolo Massa <massa@fbk.eu>

Date: Thu, 16 Sep 2010 09:29:01

Thanks, you are amazing!
Thanks!


From: EdoDodo <dodo.wikipedia@gmail.com>

Date: Thu, 16 Sep 2010 14:41:20

You're welcome.

This bug was imported as RESOLVED. The original assignee has therefore not been
set, and the original reporters/responders have not been added as CC, to
prevent bugspam.

If you re-open this bug, please consider adding these people to the CC list:
Original assignee: dodo.wikipedia@gmail.com
CC list: dodo.wikipedia@gmail.com, wikimedia-bugzilla@dabpunkt.eu