SQL-SRA-QRY-wordlists_study-joined-by-year.txt

select 'table: study-exp joined -- all records';

select '(study-exp join)experiment:library_construction_protocol', 'total ', m, count(c)
from
(
select
  stu.study_accession id, strftime('%Y', submission_date) as m,
  count(library_construction_protocol) as c from study as stu
    join experiment as exp on exp.study_accession = stu.study_accession
    join submission as sub on sub.submission_accession = stu.submission_accession
    group by id
) group by m;


select 'table: study-exp joined -- all anno types, by year';

select '(study-exp join)experiment:library_construction_protocol', 'all_anno', m, count(c)
from
(
select
  stu.study_accession id, strftime('%Y', submission_date) as m,
  count(library_construction_protocol) as c from study as stu
 join experiment as exp on exp.study_accession = stu.study_accession
 join submission as sub on sub.submission_accession = stu.submission_accession
where
(
   library_construction_protocol like "% shear%"
or library_construction_protocol like "% restriction%"
or library_construction_protocol like "% digest%"
or library_construction_protocol like "% fragment%"
or library_construction_protocol like "% breaks %"
or library_construction_protocol like "% nebulis%" or library_construction_protocol like "% nebuliz%"
or library_construction_protocol like "% acoustic%"
or library_construction_protocol like "% sonic%"
)
AND
(
   library_construction_protocol like "% adapter%"
or library_construction_protocol like "% ligat%"
or library_construction_protocol like "% blunt%"
or library_construction_protocol like "% phosphorylat%"
or library_construction_protocol like "% overhang"
or library_construction_protocol like "% t4-PNK%"
or library_construction_protocol like "% t4%"
or library_construction_protocol like "% pnk%"
or library_construction_protocol like "% kinase%"
or library_construction_protocol like "% a-tail%"
)
AND
(
   library_construction_protocol like "% clone%"
or library_construction_protocol like "% clonin%"
or library_construction_protocol like "% pcr %"
or library_construction_protocol like "% amplif%"
or library_construction_protocol like "% polymerase%"
or library_construction_protocol like "% taq%"
or library_construction_protocol like "% phusion%"
or library_construction_protocol like "% temperat%"
or library_construction_protocol like "% thermal%"
or library_construction_protocol like "% anneal%"
or library_construction_protocol like "% denature%"
)
group by id
) group by m;