queries_tenders.cql

//The biggest tenders and the authorities behind it
Match (a:AUTHORITY)-[IS_AUTHORITY_OF]->(b:CONTRACT)
WHERE b.contract_contract_value_cost <> 'null'
RETURN b,a
ORDER BY b.contract_contract_value_cost	DESC 
LIMIT 10

//The biggest spenders
Match (a:AUTHORITY)
WHERE a.total_value_cost_eur <> 'null'
RETURN a
ORDER BY a.total_value_cost_eur	DESC 
LIMIT 10

//The biggest providers
Match (a:OPERATOR)
WHERE a.total_value_cost_eur <> 'null'
RETURN a
ORDER BY a.total_value_cost_eur	DESC 
LIMIT 10

//The providers with the most contracts awarded
MATCH (a:OPERATOR)
WHERE a.number_of_contract <> 'null'
RETURN a
ORDER BY a.number_of_contract	DESC 
LIMIT 10

//The providers which share the highest number of similar customers
MATCH (a:OPERATOR)-[:IS_CONTRACTED_BY]->(b:AUTHORITY)<-[:IS_CONTRACTED_BY]-(c:OPERATOR)
WHERE a <> c
WITH count(b) as count, a, c
RETURN a, c, count
ORDER BY count DESC
LIMIT 5

//The providers which share the highest number of similar tenders (contract_cpv_code)
MATCH (a:OPERATOR)-[r:IS_OPERATOR_OF]->(b:CONTRACT), (c:OPERATOR)-[s:IS_OPERATOR_OF]->(d:CONTRACT)
WHERE a <> c AND r.contract_cpv_code = s.contract_cpv_code
WITH count(r) as count, a, c
RETURN a, c, count
ORDER BY count DESC
LIMIT 5

//The providers which work the most together but in separate fields (contract_cpv_code)

//What are the other customers of a given provider

//The insititutions which share the highest number of providers
MATCH (a:AUTHORITY)<-[:IS_CONTRACTED_BY]-(b:OPERATOR)-[:IS_CONTRACTED_BY]->(c:AUTHORITY)
WHERE a <> c
WITH count(b) as count, a, c
RETURN a, c, count
ORDER BY count DESC
LIMIT 5

//The providers which have markets in the highest number of fields
MATCH (a:OPERATOR)-[IS_OPERATOR_OF]->(b:CONTRACT)
WITH count(distinct b.contract_cpv_code) as count, a
RETURN a, count
ORDER BY count DESC
LIMIT 20

//The insitutions which have a high budget to providers ration (concentrated market)

//L'institution au centre du cluster qui représente le plus d'argent : (institution_secondaire)<-[]-(prestataire)-[]->(grosse_institution)

//The most lucrative sector
Match (a:CONTRACT)
WHERE a.contract_contract_value_cost <> 'null'
WITH a, a.contract_location_nuts as country, a.contract_cpv_code as sector
RETURN sector, country, sum(a.contract_contract_value_cost) as budget
ORDER BY budget	DESC 
LIMIT 10

//All spendings related to IT sector in France
Match (a:CONTRACT)
WHERE a.contract_contract_value_cost <> 'null' AND a.contract_location_nuts = 'FR' AND a.contract_cpv_code = '72000000-5'
RETURN sum(a.contract_contract_value_cost) as budget
ORDER BY budget	DESC 
LIMIT 10

//Institions with transnational suppliers (institutions awarding contracts to foreign companies)
MATCH (b:AUTHORITY)<-[:IS_CONTRACTED_BY]-(a:OPERATOR)
WHERE a.country <> b.country
WITH count(distinct a.country) as count, b
RETURN b, count
ORDER BY count DESC
LIMIT 5

//Is there a number of tenders received? if so, look for the most competitive tenders (and the least sorted by highest prize)