Useful AACT queries

aact.sql

--
-- Count of clinical trials for each intervention type
--
select i.intervention_type, count(distinct i.nct_id)
from 
    interventions as i
group by
    i.intervention_type

--
-- Progression of intervention types over the years
--
select 
    i.intervention_type, 
    date_part('year', s.start_date) as year, 
    count(distinct i.nct_id) as studies
from 
    interventions as i,
    studies as s
where
    s.nct_id = i.nct_id
group by
    i.intervention_type,
    date_part('year', s.start_date)
order by
    i.intervention_type,
    year
    
---
--- Most recent new interventions, grouped by study source
---
select 
    i.intervention_type as intervention_type, 
    lower(i.name) as intervention_name,
    s.study_type as study_type,
    s.source as study_source,
    count(distinct i.nct_id) as studies,
    min(date_part('year', s.study_first_submitted_date)) as year_of_first_study
from 
    interventions as i,
    studies as s
where
    s.nct_id = i.nct_id and
    lower(i.name) not like '%placebo%' and
    s.study_first_submitted_date > '2015-01-01'
group by
    i.intervention_type,
    i.name,
    s.study_type,
    s.source
order by
    year_of_first_study desc,
    studies desc,
    i.intervention_type,
    i.name
limit 100                  

--
-- Most recent new interventions, grouped by study source and sponsors
--
select 
    i.intervention_type as intervention_type, 
    lower(i.name) as intervention_name,
    s.study_type as study_type,
    s.source as study_source,
    ss.name,
    count(distinct i.nct_id) as studies,
    min(date_part('year', s.study_first_submitted_date)) as year_of_first_study
from 
    interventions as i
left outer join sponsors as ss
    on i.nct_id = ss.nct_id
left join studies as s
    on i.nct_id = s.nct_id
where
    s.nct_id = i.nct_id and
    lower(i.name) not like '%placebo%' and
    s.study_first_submitted_date > '2015-01-01'
group by
    i.intervention_type,
    i.name,
    s.study_type,
    s.source,
    ss.name
order by
    year_of_first_study desc,
    studies desc,
    i.intervention_type,
    i.name
limit 200

--
-- Trials for new interventions, group by sponsr, sorted by count of new interventions
--
with interventions1 as (
select 
    i.intervention_type as intervention_type, 
    lower(i.name) as intervention_name,
    min(date_part('year', s.study_first_submitted_date)) as year_of_first_study
from 
    interventions as i
left join studies as s
    on i.nct_id = s.nct_id
where
    i.intervention_type = 'Drug'and
    s.nct_id = i.nct_id and
    lower(i.name) not like '%placebo%'
group by
    i.intervention_type,
    i.name
order by
    year_of_first_study desc,
    i.intervention_type,
    i.name
)
select
    ss.name as sponsor_name,
    ss.agency_class as sponsor_agency_class,
    ss.lead_or_collaborator, 
    count(distinct i2.intervention_name) as interventions
from 
    interventions as i1,
    interventions1 as i2,
    sponsors as ss
where
    lower(i1.name) = i2.intervention_name and
    i1.nct_id = ss.nct_id and
    i2.year_of_first_study >=2018
group by
    ss.name,
    sponsor_agency_class,
    ss.lead_or_collaborator
order by
    interventions desc,
    ss.name asc,
    ss.lead_or_collaborator asc