encode_submission/entex-lab.sql

   1 \pset format unaligned
   2 \pset fieldsep '\t'
   3 with donor as (
   4   select uri as Donor,
   5          payload->>'accession'
   6   from item
   7   ),
   8   experiment as (
   9   select uri as Experiment,
  10          payload->>'accession' as Experiment_Accession,
  11          payload->>'description' as Experiment_Description,
  12          payload->>'status' as Experiment_Status,
  13          payload->>'date_released' as Experiment_Released,
  14          payload->>'lab' as Experiment_Lab,
  15          jsonb_array_elements_text(payload->'replicates') as Replicate
  16   from item
  17   where object_type = 'Experiment'
  18   ),
  19   replicate as (
  20     select uri as Replicate,
  21            payload->>'library' as Library
  22     from item
  23     where object_type = 'Replicate'
  24   ),
  25   library as (
  26     select uri as Library,
  27            payload->>'accession' as Library_Accession,
  28            payload->>'date_created' as Library_Created,
  29            payload->>'biosample' as Biosample,
  30            jsonb_array_elements_text(payload->'aliases') as Library_Alias
  31     from item
  32     where object_type = 'Library'
  33   ),
  34   biosample as (
  35      select uri as Biosample,
  36             payload->>'accession' as Biosample_Accession,
  37             payload->>'date_created' as Biosample_Created,
  38             payload->>'donor' as Donor
  39      from item
  40      where object_type = 'Biosample'
  41   )
  42 select Experiment_Lab, Experiment_Status, count(Experiment_Accession)
  43 from experiment
  44      LEFT JOIN replicate ON experiment.Replicate = replicate.Replicate
  45      LEFT JOIN library on replicate.Library = library.Library
  46      LEFT JOIN biosample on library.Biosample = biosample.Biosample
  47 where biosample.Donor in ('/human-donors/ENCDO845WKR/','/human-donors/ENCDO451RUA/','/human-donors/ENCDO793LXB/','/human-donors/ENCDO271OUW/')
  48 group by Experiment_Lab, Experiment_Status
  49 -- order by experiment_released, experiment_status, experiment_accession
  50 -- limit 10
  51
  52
  53
  54 -- ENTEX donors