SELECT

    main.page_title AS title_en,

    REPLACE(ll.ll_title,' ','_')  AS title_id,

    REPLACE(cl_prio.cl_to,'-priority_mathematics_articles','') AS priority,

    REPLACE(REPLACE(cl_qual.cl_to,'_mathematics_articles',''),'-Class','') AS quality,

    main.page_len AS article_length,

    talk.page_len AS talk_length,           #! excluding subpage(s)

    # main.page_touched AS last_touched,    #! not informative... i think

    (SELECT

        # count unique(?) language of this article

        COUNT(t_ll.ll_lang)

        FROM langlinks AS t_ll

        WHERE t_ll.ll_from = main.page_id

    ) AS total_lang,

    (SELECT 

        # count unique(?) articles in main namespace, that linked to this article 

        COUNT(t_pl.pl_from)

        FROM pagelinks AS t_pl

        INNER JOIN linktarget AS t_lt ON

            t_lt.lt_id = t_pl.pl_target_id

            AND t_lt.lt_namespace = 0

            AND t_pl.pl_from_namespace = 0

        WHERE t_lt.lt_title = main.page_title

    ) AS total_inlink,

    (SELECT

        # count unique(?) article in main namespace, that linked from this article

        COUNT(t_pl.pl_target_id)

        FROM pagelinks AS t_pl

        INNER JOIN linktarget AS t_lt ON

            t_lt.lt_id = t_pl.pl_target_id

            AND t_lt.lt_namespace = 0

            AND t_pl.pl_from_namespace = 0

        WHERE t_pl.pl_from = main.page_id

    ) AS total_outlink

​

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',    # featured article

    'FL-Class_mathematics_articles',    # featured list

    'GA-Class_mathematics_articles',    # good article

    '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 because i need this data to help idwiki. left join is

# intentional, to get empty result if page isn't created yet 

LEFT JOIN langlinks AS ll ON

    ll.ll_from = main.page_id

    AND ll.ll_lang = 'id'

​

WHERE

    main.page_is_redirect = 0

    AND main.page_title NOT IN (

      SELECT page_title FROM page

      INNER JOIN categorylinks AS cl_biog

      ON cl_biog.cl_from = talk.page_id AND cl_biog.cl_to IN (

          'FA-Class_biography_articles',

          'FL-Class_biography_articles',

          'FM-Class_biography_articles',

          'GA-Class_biography_articles',

          'B-Class_biography_articles',

          'C-Class_biography_articles',

          'Start-Class_biography_articles'

          'Stub-Class_biography_articles',

          'List-Class_biography_articles',

          'Disambig-Class_biography_articles',

          'Redirect-Class_biography_articles',

          'Unassessed_biography_articles')

    )

​

LIMIT 10;