Vitality Metrics: Basic Page Data (EN)

This query performs basic filtering on the Wikipedia (EN) pages, then gathers data like the human-readable article name. It's largely just a demo / starting-point for later joins, which may be handled in a ToolDB user image.

Anything that doesn't play well with the database schema or needs an analytical tool (like page-views) belongs elsewhere.

SQL

# Set a query timeout for politeness
SET STATEMENT max_statement_time = 300 FOR

# Indices don't allow for an efficient join ...
# ... so we'll just grab page IDs for now
SELECT
  page_id AS "Article ID",
  page_title AS "Article Name",
  page_is_redirect AS "Redirecting?",
  page_latest AS "Latest Revision ID",
  page_len AS "Article Size (B)"
FROM page

WHERE (
  # We're only interested in articles (main namespace)
  page_namespace = 0 AND
  
  # Keep redirects for later joins with revision history
  
  # Stubs, however, are another matter ...
  # ... 2500 B threshold based on 500 words * ~5 chars / word
  (page_is_redirect = 0 AND page_len > 2500)
)

# We can't select article pages without an efficient join ...
# ... so we'll just oversample for now

# TESTING: Start with a small limit to gauge time
LIMIT 100000;

SQL

Checking query status...