NEO4J Queries.txt

#1. What states receive the most SBIR awards in a variety of technology areas?
MATCH (sbir_award:Sbir_Award)-[:AWARDED_TO]->(principal:Principal)-[:WORKSIN]->(location:Location)
MATCH (sbir_award:Sbir_Award)-[:CONTAINS]->(sbir_abstract)-[:HOLDS]->(e:Entities)
WHERE toLower(e.entities) = "communication"
RETURN location.state, COUNT(DISTINCT sbir_award.key) AS sbir_awards
ORDER BY sbir_awards DESC LIMIT 10;

 
#2. What states are leading research in the area of technology x (ex: communication)?
CALL {
    MATCH (title_pt)-[:ASSIGN_PT]->(assignee_pt)
    MATCH (title_pt)-[:CREATED_BY_PT]->(inventors_pt)-[:RESEARCHED_PT]->(Patent_Abstract)-[:HOLDS_PT]->(e:Entities)
    WHERE toLower(e.entities) = 'communication'
    RETURN assignee_pt.state as states, COUNT(DISTINCT title_pt.key) AS SBIR_PATENT_COUNT
    UNION
    MATCH (title_pt)-[:ASSIGN_PT]->(assignee_pt)
    MATCH (title_pt)-[:CREATED_BY_PT]->(inventors_pt)-[:RESEARCHED_PT]->(Patent_Abstract)-[:HOLDS_PT]->(e:Entities)
    WHERE toLower(e.entities) = 'communication'
    RETURN inventors_pt.state as states,COUNT(DISTINCT title_pt.key) AS SBIR_PATENT_COUNT
    UNION
    MATCH (sbir_award:Sbir_Award)-[:AWARDED_TO]->(principal:Principal)-[:WORKSIN]->(location:Location)
    MATCH (sbir_award:Sbir_Award)-[:CONTAINS]->(sbir_abstract)-[:HOLDS]->(e:Entities)
    WHERE toLower(e.entities) = "communication"
    RETURN location.state as states, COUNT(DISTINCT sbir_award.key) AS SBIR_PATENT_COUNT
}
WITH states, SUM(SBIR_PATENT_COUNT) as NUMBER_OF_ABSTRACTS
ORDER BY states DESC
RETURN states, NUMBER_OF_ABSTRACTS
ORDER BY NUMBER_OF_ABSTRACTS DESC


#3. What states are benefiting the most from SBIR?
MATCH (sbir:Sbir_Award)-[:AWARDED_TO]->(principal:Principal)-[:WORKSIN]->(location:Location)
RETURN location.state, SUM(sbir.award_amount) AS Total_Award_Amount
ORDER BY Total_Award_Amount DESC

#4.Which companies received the most SBIR awards?
MATCH (Sbir_Award)-[:AWARDED_TO]->(Principal)
RETURN Principal.company, sum(Sbir_Award.award_amount) AS total_award
ORDER BY total_award desc;

#5. Which companies received a SBIR award and a patent on a similar topic?
MATCH (assignee_pt:Assignee_PT)<-[]-(Patent_Abstract)-[]->(Entities)<-[]-(Sbir_Abstract)<-[]-(p:Principal)
WHERE toLower(assignee_pt.orgname) contains toLower(p.company)
RETURN distinct assignee_pt.orgname as patent_assignee, p.company as SBIR_awardee
order by patent_assignee;

#6. Which companies completed which SBIRS that lead to which patents being generated after completing a SBIR award in which years?
MATCH (ap:Assignee_PT)<-[]-(pa:Patent_Abstract)-[]->(Entities)<-[]-(sa:Sbir_Abstract)<-[]-(p:Principal), 
(sa)<-[]-(sbir:Sbir_Award), (arp:Application_Ref_PT)-[]->(pa)
WHERE toLower(ap.orgname) contains toLower(p.company)
RETURN DISTINCT sbir.key as SBIR_AWARD_ID,p.company as SBIR_awardee, sbir.award_year as SBIR_AWARD_YEAR, ap.orgname as Patent_Created_By, arp.date, arp.doc_number
order by SBIR_awardee;

#7. What companies are working in similar research areas in x (ex: AI)?
As GRAPH:

CALL {
    MATCH (p:Principal)-[:RESEARCHED]->(sbir_abstract:Sbir_Abstract)-[:HOLDS]->(e:Entities)
    WHERE EXISTS {
        MATCH ()-[r:HOLDS|HOLDS_PT]->(e)
        WITH e, count(r) as rel_cnt
        WHERE rel_cnt > 1
    }
    RETURN e as entity, p as company
    ORDER BY entity 
    UNION
    MATCH (ap:Assignee_PT)<-[:ASSIGN_PT]-(title_pt:Title_PT)-[:HAS_PT]->(patent_abstract:Patent_Abstract)-[:HOLDS_PT]->(e:Entities)
    WHERE EXISTS {
        MATCH ()-[r:HOLDS|HOLDS_PT]->(e)
        WITH e, count(r) as rel_cnt
        WHERE rel_cnt > 1
    }
    RETURN e as entity, ap as company
    ORDER BY entity 
}
RETURN entity, company
ORDER BY entity 

As text:
CALL {
    MATCH (p:Principal)-[:RESEARCHED]->(sbir_abstract:Sbir_Abstract)-[:HOLDS]->(e:Entities)
    WHERE EXISTS {
        MATCH ()-[r:HOLDS|HOLDS_PT]->(e)
        WITH e, count(r) as rel_cnt
        WHERE rel_cnt > 1
    }
    RETURN toLower(e.entities) as entity, toLower(p.company) as company
    ORDER BY entity 
    UNION
    MATCH (ap:Assignee_PT)<-[:ASSIGN_PT]-(title_pt:Title_PT)-[:HAS_PT]->(patent_abstract:Patent_Abstract)-[:HOLDS_PT]->(e:Entities)
    WHERE EXISTS {
        MATCH ()-[r:HOLDS|HOLDS_PT]->(e)
        WITH e, count(r) as rel_cnt
        WHERE rel_cnt > 1
    }
    RETURN toLower(e.entities) as entity, toLower(ap.orgname) as company
    ORDER BY entity 
}
RETURN entity, company
ORDER BY entity 

#8. What technology areas are receiving the most interest from Government agencies and have patent filings?
MATCH (agency:Agency)-[:OVERSEES]->(sbir_award:Sbir_Award)-[:CONTAINS]->(sbir_abstract:Sbir_Abstract)-[:HOLDS]->(entities:Entities)<-[:HOLDS_PT]-(patent_abstract:Patent_Abstract)
RETURN entities.entities AS INTRESTING_TECHNOLOGY, COUNT(entities.entities) AS ENTITY_COUNT ORDER BY ENTITY_COUNT DESC;

#9. How much funding has been received by minority or disadvantage companies and how has the percentage changed over the years?

Part 1: Total Award amount for Minority, disadvantaged, women owned, etc. SBIR
MATCH (s:Sbir_Award)-[:AWARDED_TO]->(p:Principal)
WHERE p.hubzone_owned = "Y" OR p.women_owned = "Y" or p.socially_economically_disadvantaged = "Y"
RETURN sum(s.award_amount) AS total_mdw_award
ORDER BY total_mdw_award desc;

Part 2: Trend over the years

CALL {
    CALL {
        MATCH (s:Sbir_Award)-[:AWARDED_TO]->(p:Principal)
        WITH s.award_year as Year, s.award_amount AS Award_Amount, COLLECT {
            MATCH (s)-[:AWARDED_TO]->(p)
            WHERE p.hubzone_owned = "Y" OR p.women_owned = "Y" or p.socially_economically_disadvantaged = "Y"
            RETURN s.award_amount
            ORDER BY s.award_year ASC
        } AS MDW_award
        RETURN Year, SUM(Award_Amount) AS TA, reduce(total = 0, n IN MDW_award | total + n) AS TMA
        ORDER BY Year
    }
    RETURN Year, SUM(TA) AS Total_Amount , SUM(TMA) AS Total_MDW_Award
    ORDER BY Year ASC
}
WITH Year, Total_Amount, Total_MDW_Award
WHERE Total_Amount > 0
RETURN Year, Total_Amount, Total_MDW_Award,  round((Total_MDW_Award / Total_Amount), 2) AS Percent_MDW
ORDER BY Year ASC

#10. Are there any Patents that are similar to SBIRs?

As a Graph:
MATCH (p:Principal)<-[]-(sbr:Sbir_Award)-[]->(sa:Sbir_Abstract)-[r:SIMILARITY_SCORE]->(pa:Patent_Abstract)-[]->(as:Assignee_PT)
WHERE r.score>0.8 
RETURN sbr, sa, p, pa, as

As Text:
MATCH (p:Principal)<-[]-(sbr:Sbir_Award)-[]->(sa:Sbir_Abstract)-[r:SIMILARITY_SCORE]->(pa:Patent_Abstract)-[]->(as:Assignee_PT), (arp:Application_Ref_PT)-[]->(pa)
WHERE r.score>0.8 
RETURN DISTINCT p.company AS SBIR_Awardee, sbr.key AS SBIR_ID, round(r.score, 3) AS similarity_score, as.orgname AS Patent_Created_By, arp.doc_number AS Patent_ID
ORDER BY similarity_score DESC