Jump to content

Topic on User talk:Matěj Suchánek

ZI Jony (talkcontribs)

Hi @Matěj Suchánek:, Could you please help me for two quarry? I need quarry that will give me.

Those users who edited only page namespace 2 & 3.

  1. User name
  2. User editcount
  3. User registration
  4. User page name
  5. User page size
  6. No of page created

Those users who edited only page namespace 2 & 3 and locked/blocked

  1. User name
  2. User editcount
  3. User registration
  4. User page name
  5. User page size
  6. Bane of page created
  7. Size of page created
Matěj Suchánek (talkcontribs)

First one:

SELECT user.*, COUNT(rev_page) AS created, page_title AS userpage, page_len
FROM actor
JOIN user ON user_id = actor_user
LEFT JOIN revision_userindex ON rev_actor = actor_user AND rev_parent_id = 0
LEFT JOIN page ON page_namespace = 2 AND page_title = REPLACE(user_name, ' ', '_')
WHERE NOT EXISTS (
  SELECT 1 FROM revision_userindex AS R1 JOIN page AS P1 ON P1.page_id = R1.rev_page
  WHERE R1.rev_actor = actor_id AND P1.page_namespace NOT IN (2, 3)
)
AND user_editcount > 0
GROUP BY actor_id

I tested it in query optimizer but I cannot guarantee you will get your results because it checks all (ever registered) users.

What is Bane of page created?

ZI Jony (talkcontribs)

Thanks!