Untitled query #29550

This query is marked as a draft This query has been published by 軽快.
SQL

select a.wiki, local_article, local_avg_len, global_article, global_avg_len
from
(
  select "ja" as wiki, count(*) as local_article, avg(page_len) as local_avg_len
  from jawiki_p.page
  where 
  not exists
  (
    select *
    from jawiki_p.langlinks
    where page_id = ll_from
  ) and
  page_namespace = 0 and
  page_is_redirect = 0
) as a
inner join
(
  select "ja" as wiki, count(*) as global_article, avg(page_len) as global_avg_len
  from jawiki_p.page
  where 
  exists
  (
    select *
    from jawiki_p.langlinks
    where page_id = ll_from
  ) and
  page_namespace = 0 and
  page_is_redirect = 0
) as b on a.wiki = b.wiki
union
select c.wiki, local_article, local_avg_len, global_article, global_avg_len
from
(
  select "en" as wiki, count(*) as local_article, avg(page_len) as local_avg_len
  from enwiki_p.page
  where 
  not exists
  (
    select *
    from enwiki_p.langlinks
    where page_id = ll_from
  ) and
  page_namespace = 0 and
  page_is_redirect = 0
) as c
inner join
(
  select "en" as wiki, count(*) as global_article, avg(page_len) as global_avg_len
  from enwiki_p.page
  where 
  exists
  (
    select *
    from enwiki_p.langlinks
    where page_id = ll_from
  ) and
  page_namespace = 0 and
  page_is_redirect = 0
) as d on c.wiki = d.wiki
union
select e.wiki, local_article, local_avg_len, global_article, global_avg_len
from
(
  select "de" as wiki, count(*) as local_article, avg(page_len) as local_avg_len
  from dewiki_p.page
  where 
  not exists
  (
    select *
    from dewiki_p.langlinks
    where page_id = ll_from
  ) and
  page_namespace = 0 and
  page_is_redirect = 0
) as e
inner join
(
  select "de" as wiki, count(*) as global_article, avg(page_len) as global_avg_len
  from dewiki_p.page
  where 
  exists
  (
    select *
    from dewiki_p.langlinks
    where page_id = ll_from
  ) and
  page_namespace = 0 and
  page_is_redirect = 0
) as f on e.wiki = f.wiki
union
select g.wiki, local_article, local_avg_len, global_article, global_avg_len
from
(
  select "ko" as wiki, count(*) as local_article, avg(page_len) as local_avg_len
  from kowiki_p.page
  where 
  not exists
  (
    select *
    from kowiki_p.langlinks
    where page_id = ll_from
  ) and
  page_namespace = 0 and
  page_is_redirect = 0
) as g
inner join
(
  select "ko" as wiki, count(*) as global_article, avg(page_len) as global_avg_len
  from kowiki_p.page
  where 
  exists
  (
    select *
    from kowiki_p.langlinks
    where page_id = ll_from
  ) and
  page_namespace = 0 and
  page_is_redirect = 0
) as h on g.wiki = h.wiki
union
select i.wiki, local_article, local_avg_len, global_article, global_avg_len
from
(
  select "zh" as wiki, count(*) as local_article, avg(page_len) as local_avg_len
  from zhwiki_p.page
  where 
  not exists
  (
    select *
    from zhwiki_p.langlinks
    where page_id = ll_from
  ) and
  page_namespace = 0 and
  page_is_redirect = 0
) as i
inner join
(
  select "zh" as wiki, count(*) as global_article, avg(page_len) as global_avg_len
  from zhwiki_p.page
  where 
  exists
  (
    select *
    from zhwiki_p.langlinks
    where page_id = ll_from
  ) and
  page_namespace = 0 and
  page_is_redirect = 0
) as j on i.wiki = j.wiki
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...
