Historical evolution of FSD distribution for a category

Fork of Historical evolution of FSD distribution for singers by Asoulet

This query is marked as a draft This query has been published by Asoulet.

SQL

use enwiki_p;
select year, fsd, count(*) as frequency
from 
(select year(a.rev_timestamp) as year, page_id, substr(max(a.rev_len), 1, 1) as fsd
from revision a, revision b, revision c, revision d, page, categorylinks
where 
a.rev_page = page_id
and page_id = cl_from
and cl_to = '1940_births'
and a.rev_parent_id = b.rev_id
and b.rev_parent_id = c.rev_id
and c.rev_parent_id = d.rev_id
and abs(cast(a.rev_len as double) - cast(d.rev_len as double)) / cast(d.rev_len as double) < 0.1
and page_namespace = 0
group by year(a.rev_timestamp), page_id) distrbution
group by year, fsd;

SQL

Checking query status...