select database() as wiki, ss_good_articles as articles
from site_stats;

multiquery ~/queries/num_articles.sql -h analytics-store.eqiad.wmnet -d ~/dblists/wikipedias.tsv --defaults-file=/etc/mysql/conf.d/analytics-research-client.cnf > ~/data/wikipedia_articles.tsv

multiquery ~/queries/num_articles.sql -h analytics-store.eqiad.wmnet -d ~/dblists/wiktionaries.tsv --defaults-file=/etc/mysql/conf.d/analytics-research-client.cnf > ~/data/wiktionary_entries.tsv

select license, count(*) as files
from
(select
  case
    when cats like '%CC-BY-NC-ND%' then 'CC-BY-NC-ND'
    when cats like '%CC-BY-NC-SA%' then 'CC-BY-NC-SA'
    when cats like '%CC-BY-ND%' then 'CC-BY-ND'
    when cats like '%CC-BY-SA%' then 'CC-BY-SA'
    when cats like '%CC-BY-NC%' then 'CC-BY-NC'
    when cats like '%CC-BY%' then 'CC-BY'
    when cats like '%CC-SA%' then 'CC-SA'
    when cats like '%CC-Zero%' then 'CC-0'
    when cats like '%CC-PD%' then 'CC-PD'
    else 'Other CC'
  end as license
  from
  (select cl_from, group_concat(cl_to) as cats
    from commonswiki.categorylinks
    inner join
      (select cat_title from commonswiki.category where
        cat_title like 'CC-%' and
        cat_title not like '%aircraft%' and
        cat_title not regexp 'CC-[[:upper:][:digit:]]{3}'
      ) cc_cats
    on cat_title = cl_to
    where cl_type = "file"
    group by cl_from
  ) cc_files
) licenses
group by license;

select img_media_type, count(*) as files
from
(select distinct cl_from
  from commonswiki.categorylinks
  inner join
    (select cat_title from commonswiki.category where
      cat_title like 'CC-%' and
      cat_title not like '%aircraft%' and
      cat_title not regexp 'CC-[[:upper:][:digit:]]{3}'
    ) cc_cats
  on cat_title = cl_to
  where cl_type = "file"
) cc_files
inner join commonswiki.page on cl_from = page_id
inner join commonswiki.image on page_title = img_name
group by img_media_type;