SQL on idwiki side #1

SQL

SELECT
    main.page_title AS title_en,
    ll.ll_title AS title_id,
    cl_prio.cl_to AS priority,
    cl_qual.cl_to AS quality,
    main.page_is_redirect AS is_redirect,
    main.page_len AS article_length,
    talk.page_len AS talk_length,  # excluding subpage(s)
    COUNT(DISTINCT t_ll.ll_lang) AS total_lang,
    COUNT(DISTINCT t_in_pl.pl_from) AS total_inlink

FROM page AS talk
# inner join to cut table size
# focus to all possible priority

INNER JOIN categorylinks AS cl_prio ON
    cl_prio.cl_from = talk.page_id
    AND cl_prio.cl_to IN (
        'Top-priority_mathematics_articles',
        'High-priority_mathematics_articles',
        'Mid-priority_mathematics_articles',
        'Low-priority_mathematics_articles',
        'NA-priority_mathematics_articles',
        'Unknown-priority_mathematics_articles')

# focus to all possible quality for an article in main namespace
INNER JOIN categorylinks AS cl_qual ON
    cl_qual.cl_from = talk.page_id
    AND cl_qual.cl_to IN (
        'FA-Class_mathematics_articles',
        'FL-Class_mathematics_articles',
        'FM-Class_mathematics_articles',
        'GA-Class_mathematics_articles',
        'B-Class_mathematics_articles',
        'C-Class_mathematics_articles',
        'Start-Class_mathematics_articles',
        'Stub-Class_mathematics_articles',
        'List-Class_mathematics_articles',
        'Disambig-Class_mathematics_articles',
        'Redirect-Class_mathematics_articles',
        'Unassessed_mathematics_articles')
# join to get the main article's ID
INNER JOIN page AS main ON
    main.page_namespace = 0
    AND main.page_title = talk.page_title

# join to get the title in id language
LEFT JOIN langlinks AS ll ON
    ll.ll_from = main.page_id
    AND ll.ll_lang = 'id'

# join to count the total languages available
LEFT JOIN langlinks AS t_ll ON
    t_ll.ll_from = main.page_id

LEFT JOIN pagelinks AS t_in_pl ON
    t_in_pl.pl_target_id = main.page_id
    AND t_in_pl.pl_from_namespace = 0
LEFT JOIN linktarget AS t_in_lt ON
    t_in_lt.lt_id = t_in_pl.pl_target_id
    AND t_in_lt.lt_namespace = 0

GROUP BY
    main.page_title,
    ll.ll_title,
    cl_prio.cl_to,
    cl_qual.cl_to,
    main.page_is_redirect,
    main.page_len,
    talk.page_len

LIMIT 10;
SQL

Checking query status...
