Q3: Total sales of customers in 1997, located in cities that are within a 100 km buffer area from a given point.

SELECT ?custName ?yearNo (SUM(?sales) AS ?totalSales)
WHERE { ?o rdf:type qb:Observation; gnw:customerID ?cust; 
   gnw:salesAmount ?sales; gnw:orderDateID ?time.
   ?time gnw:yearNo ?yearNo.?cust gnw:customerName ?custName; 
   skos:broader ?city. ?city gnw:cityGeo ?cityGeo. 
FILTER (?yearNo = "1997" && bif:st_within (?cityGeo, 
bif:st_point(2.3522219, 48.856614),  100))}
GROUP BY ?custName ?yearNo ORDER BY ?custName

			

Q4 Total sales amount to the customers that have orders delivered by suppliers such that their locations are less than 200 km from each other.

SELECT ?custName ?distance (SUM(?sales) AS ?totalSales) 
WHERE { ?o a qb:Observation; 
	gnw:customerID ?cust; gnw:supplierID ?sup; 
	gnw:salesAmount ?sales. ?cust gnw:customerName ?custName; 
	gnw:customerGeo ?custGeo. ?sup gnw:supplierGeo ?supGeo. 
BIND (bif:st_distance (?custGeo, ?supGeo) AS ?distance)	
FILTER(?distance < 200 )}
GROUP BY ?custName ?distance ORDER BY ?custName


			

Q5: Distance between the customers' locations and the center of the city which they are located

SELECT DISTINCT ?custName ?cityName ?distance 
WHERE { ?o a qb:Observation; gnw:customerID ?cust.
	?cust gnw:customerName ?custName; 
	gnw:customerGeo ?custGeo;skos:broader ?city. 
	?city gnw:cityGeo ?cityGeo; gnw:cityName ?cityName.
BIND (bif:st_distance (?cityGeo, ?custGeo) AS ?distance)}
ORDER BY ?custName
			

Q6: Number of customers located at more than 100 km from the supplier.

SELECT ?supName, (COUNT(?cust) AS ?nbCustomers)
WHERE { ?o a qb:Observation; gnw:customerID ?cust;
	gnw:supplierID ?sup. ?cust gnw:customerGeo ?custGeo.
	?sup gnw:supplierGeo ?supGeo; gnw:supplierName ?supName.
FILTER (bif:st_distance(?supGeo, ?custGeo) > 100 ) }
GROUP BY ?supName ORDER BY DESC (?nbCustomers)
			

Q7: For each customer, total sales amount to its closest supplier.

SELECT ?custName ?sup ?minDistance (SUM(?sales) AS ?totalSales)
WHERE {?o a qb:Observation; gnw:customerID ?cust;
	gnw:supplierID ?sup;gnw:salesAmount ?sales.
	?cust qb4o:inLevel gnw:customer;gnw:customerGeo ?custGeo;
	gnw:customerName ?custName. ?sup gnw:supplierGeo ?supGeo.
#Inner Select:Total sales to the closest supplier of the customer
  {SELECT ?cust1 (MIN(?distance) AS ?minDistance)
  WHERE{ ?o a qb:Observation; gnw:customerID ?cust1;
	gnw:supplierID ?sup1.?sup1 gnw:supplierGeo ?sup1Geo.
	?cust1 gnw:customerGeo ?cust1Geo .
	BIND (bif:st_distance(?cust1Geo,?sup1Geo) AS ?distance)}
	GROUP BY ?cust1}
FILTER (?cust = ?cust1 && bif:st_distance( ?custGeo, ?supGeo) = 
?minDistance)} GROUP BY ?custName ?minDistance ?sup
			

Q8: Distance between the customer and supplier for customers that have orders delivered by suppliers of the same country.

 
SELECT ?custName ?supName ?distance
WHERE{?o a qb:Observation; gnw:customerID ?cust; 
   gnw:supplierID ?sup; gnw:salesAmount ?sales.
   ?cust gnw:customerName ?custName; gnw:customerGeo ?custGeo; 
   skos:broader ?custCity.{?custCity skos:broader ?custCountry.}
UNION {?custCity skos:broader ?custState.
   {?custState skos:broader ?custCountry.}
UNION {?custState skos:broader ?custRegion.
   ?custRegion skos:broader ?custCountry.}}
   ?sup gnw:supplierName ?supName; gnw:supplierGeo ?supGeo; 
   skos:broader ?supCity.{?supCity skos:broader ?supCountry.}
UNION {?supCity skos:broader ?supState.
   {?supState skos:broader ?supCountry.}
UNION {?supState skos:broader ?supRegion.
   ?supRegion skos:broader ?supCountry.}} 
BIND (bif:st_distance (?custGeo , ?supGeo) AS ?distance)
FILTER (?custCountry = ?supCountry)}
GROUP BY ?custName ?supName ?distance
			

Q9: Total sales to customers located in a state that contains the capital city of the country.

SELECT ?custName (SUM(?sales) AS ?totalSales)
WHERE {?o rdf:type qb:Observation; gnw:customer ?cust; 
   gnw:salesAmount ?sales. ?cust gnw:customerName ?custName; 
   skos:broader ?city. ?city  skos:broader ?state.
   ?state gnw:stateGeo ?stateGeo.{?state skos:broader ?country.}
UNION {?state skos:broader ?region.
   ?region skos:broader ?country.} 
   ?country gnw:capitalGeo ?capitalGeo. 
FILTER ( bif:st_contains(?stateGeo, ?capitalGeo))}
GROUP BY ?custName ORDER BY ?custName