SQL on idwiki side #1

This query is marked as a draft This query has been published by Kekavigi.
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)
    # 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')
        
INNER JOIN categorylinks AS cl_biog ON
	cl_biog.cl_from = talk.page_id
  	AND cl_biog.cl_to NOT 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')

# 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'
By running queries you agree to the Cloud Services Terms of Use and you irrevocably agree to release your SQL under CC0 License.
Submit Query Stop Query
All SQL code is licensed under CC0 License.
SQL

Checking query status...
