2026-01-16

#sql
#quarry
#globustut
#commons: red category links with one or more files from a specific user

SELECT
    CONCAT('https://commons.wikimedia.org/wiki/Category:', REPLACE(cl.cl_to, ' ', '_')) AS category_url,
    COUNT(*) AS file_count
  FROM page p
  JOIN image i ON i.img_name = p.page_title
  JOIN actor a ON a.actor_id = i.img_actor
  JOIN categorylinks cl ON cl.cl_from = p.page_id
  LEFT JOIN page c
    ON c.page_title = cl.cl_to
   AND c.page_namespace = 14
  WHERE p.page_namespace = 6
    AND a.actor_name = 'Globustut'
    AND c.page_id IS NULL
  GROUP BY cl.cl_to
  ORDER BY file_count DESC, cl.cl_to

#commons: #count uploads from a specific user for a period of time, #python:

import requests

user = 'Globustut'
start = '2026-01-20T00:00:00Z'  # newer
end   = '2026-01-01T00:00:00Z'  # older

params = {
    'action': 'query',
    'format': 'json',
    'list': 'usercontribs',
    'ucuser': user,
    'ucnamespace': '6',
    'ucshow': 'new',
    'ucstart': start,
    'ucend': end,
    'uclimit': 'max',
}
headers = {'User-Agent': 'commons-upload-count/1.0'}

total = 0
s = requests.Session()
while True:
    data = s.get('https://commons.wikimedia.org/w/api.php', params=params, headers=headers, timeout=30).json()
    total += len(data.get('query', {}).get('usercontribs', []))
    if 'continue' not in data:
        break
    print('.', end='')
    params.update(data['continue'])

print(total)

https://quarry.wmcloud.org/query/100891

#sql
#quarry
#globustut
#commons: files from a specific user in a non-existing categories, #sql:

sql
SELECT
    img_name,
    cl_to AS missing_category
  FROM image
  JOIN actor ON actor_id = img_actor
  JOIN page ON page_namespace = 6 AND page_title = img_name
  JOIN categorylinks ON cl_from = page_id
  LEFT JOIN page AS cat
    ON cat.page_namespace = 14
   AND cat.page_title = cl_to
  WHERE actor_name = 'Globustut'
    AND cat.page_id IS NULL
  ORDER BY img_name;

https://quarry.wmcloud.org/query/101097