gene_summary_stats.sql

/**
  SQL used to generate the counts of gene summaries for all genes in Chado.

  e.g.
  psql -f gene_summary_stats.sql -h chado.flybase.org -U flybase flybase > gene_summary_chado_stats.tsv

  This file is then consumed by merge_chado_alliance_gene_summary_counts.py to produce the final file.
 */
copy (
select
  f.uniquename,
  flybase.current_symbol(f.uniquename),
  gene_snapshot,
  uniprot_function,
  flybase_pathway,
  flybase_group,
  interactive_fly
  from feature f
    left join lateral (
      select count(*) as gene_snapshot
      from flybase.get_featureprop(f.uniquename,'gene_summary_text')
    ) gs on true

    left join lateral (
      select count(*) as uniprot_function
      from feature_dbxref fdbx join dbxref dbx on fdbx.dbxref_id = dbx.dbxref_id
                               join dbxrefprop dbxp on dbx.dbxref_id = dbxp.dbxref_id
                               join db on dbx.db_id = db.db_id
                               join cvterm dbxpt on dbxp.type_id = dbxpt.cvterm_id
      where f.feature_id = fdbx.feature_id 
        and lower(dbxpt.name) = 'uniprot_function_comment'
        and lower(db.name) = 'uniprot/swiss-prot'
    ) us on true

    left join lateral (
      select count(*) as flybase_pathway
      from feature_grpmember fgm join grpmember gm on fgm.grpmember_id = gm.grpmember_id
                                 join cvterm gmt on gm.type_id = gmt.cvterm_id
                                 join grp on gm.grp_id = grp.grp_id
                                 join grp_cvterm g_cvt on grp.grp_id = g_cvt.grp_id
                                 join cvterm gt on g_cvt.cvterm_id = gt.cvterm_id
                                 join dbxref dbx on gt.dbxref_id = dbx.dbxref_id
                                 join db on dbx.db_id = db.db_id
      where f.feature_id = fgm.feature_id 
        and gmt.name = 'grpmember_feature'
        and db.name = 'FBcv'
        and dbx.accession = '0007034'
    ) fbpath on true

    left join lateral (
      select count(*) as flybase_group
      from feature_grpmember fgm join grpmember gm on fgm.grpmember_id = gm.grpmember_id
                                 join cvterm gmt on gm.type_id = gmt.cvterm_id
                                 join grp on gm.grp_id = grp.grp_id
                                 join grp_cvterm g_cvt on grp.grp_id = g_cvt.grp_id
                                 join cvterm gt on g_cvt.cvterm_id = gt.cvterm_id
                                 join dbxref dbx on gt.dbxref_id = dbx.dbxref_id
                                 join db on dbx.db_id = db.db_id
      where f.feature_id = fgm.feature_id 
        and gmt.name = 'grpmember_feature'
        and db.name = 'FBcv'
        and dbx.accession != '0007034'
    ) fbgroup on true

    left join lateral (
      select count(*) as interactive_fly
      from feature_dbxref fdbx join dbxref dbx on fdbx.dbxref_id = dbx.dbxref_id
                               join dbxrefprop dbxp on dbx.dbxref_id = dbxp.dbxref_id
                               join db on dbx.db_id = db.db_id
                               join cvterm dbxpt on dbxp.type_id = dbxpt.cvterm_id
      where f.feature_id = fdbx.feature_id 
        and lower(dbxpt.name) = 'if_summary'
        and lower(db.name) = 'interactivefly'
        and dbxp.value is not null
    ) if on true

  where f.is_obsolete = false
    and f.is_analysis = false
    and f.uniquename ~ '^FBgn\d+$'
) to stdout;