######### SSB Q4
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX qb: <http://purl.org/linked-data/cube#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX ssb: <http://ssb.org/schemas/> 
PREFIX ssb-inst: <http://ssb.org/data/>
SELECT (SUM(?revenue) AS ?lo_revenue) ?year ?brand 
WHERE {
	
	?li a qb:Observation ; 
		ssb:l_revenue ?revenue ;
		ssb:l_part ?part ;
		ssb:l_supplier ?supplier ;
		ssb:l_orderdate ?Date .
	?part skos:broader ?brand .
	?brand skos:broader ?category .
	?category ssb:category_name 'MFGR_12' .
	?supplier skos:broader ?city .
	?city skos:broader ?nation .
	?nation skos:broader ?region .
	?region ssb:region_name 'AMERICA' . 
	?Date skos:broader ?month .
	?month skos:broader ?year .
}
GROUP BY ?year ?brand 
ORDER BY ?year ?brand

#########  SSB Q5
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX qb: <http://purl.org/linked-data/cube#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX ssb: <http://ssb.org/schemas/> 
PREFIX ssb-inst: <http://ssb.org/data/>
SELECT (SUM(?revenue) AS ?lo_revenue) ?year ?brand 
WHERE {
	?li a qb:Observation ; 
		ssb:l_revenue ?revenue ;
		ssb:l_part ?part ;
		ssb:l_supplier ?supplier ;
		ssb:l_orderdate ?Date .

	?part skos:broader ?brand .
	?brand skos:broader ?category .
	?supplier skos:broader ?city .
	?city skos:broader ?nation .
	?nation skos:broader ?region .
	?Date skos:broader ?month .
	?month skos:broader ?year .
	?region ssb:region_name 'ASIA' . 
	?brand ssb:brand_name ?brandname .
	FILTER(STR(?brandname) >= 'MFGR_2221' && STR(?brandname) <= 'MFGR_2228')
}
GROUP BY ?year ?brand 
ORDER BY ?year ?brand

#########  SSB Q6
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX qb: <http://purl.org/linked-data/cube#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX ssb: <http://ssb.org/schemas/> 
PREFIX ssb-inst: <http://ssb.org/data/>
SELECT (SUM(?revenue) AS ?lo_revenue) ?year ?brand 
WHERE {
	?li a qb:Observation ; 
		ssb:l_revenue ?revenue ;
		ssb:l_part ?part ;
		ssb:l_supplier ?supplier ;
		ssb:l_orderdate ?Date .
	?part skos:broader ?brand .
	?brand skos:broader ?category .
	?supplier skos:broader ?city .
	?city skos:broader ?nation .
	?nation skos:broader ?region .
	?region ssb:region_name 'EUROPE' . 
	?Date skos:broader ?month .
	?month skos:broader ?year .
	?brand ssb:brand_name ?brandname .
	FILTER (STR(?brandname) = 'MFGR_2239')
}
GROUP BY ?year ?brand 
ORDER BY ?year ?brand


######### SSB Q7
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX qb: <http://purl.org/linked-data/cube#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX ssb: <http://ssb.org/schemas/> 
PREFIX ssb-inst: <http://ssb.org/data/>
SELECT  ?c_nation ?s_nation ?year (SUM(?revenue) AS ?lo_revenue) 
WHERE {
	?li a qb:Observation ; 
		ssb:l_revenue ?revenue ;
		ssb:l_part ?part ;
		ssb:l_supplier ?supplier ;
		ssb:l_customer ?customer ;
		ssb:l_orderdate ?Date .

	?customer skos:broader ?c_city .
	?c_city skos:broader ?c_nation .
	?supplier skos:broader ?s_city .
	?s_city skos:broader ?s_nation .			
	?Date skos:broader ?month .
	?month skos:broader ?year .
	?c_nation skos:broader ?c_region .
	?c_region ssb:region_name 'ASIA' .
	?s_nation skos:broader ?s_region .
	?s_region ssb:region_name 'ASIA' .
}
GROUP BY ?c_nation ?s_nation ?year 
ORDER BY ?year DESC(?lo_revenue)

########### SSB Q8
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX qb: <http://purl.org/linked-data/cube#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX ssb: <http://ssb.org/schemas/> 
PREFIX ssb-inst: <http://ssb.org/data/>
SELECT ?c_city ?s_city ?year (SUM(?revenue) AS ?lo_revenue) 
WHERE {
	?li a qb:Observation ; 
		ssb:l_revenue ?revenue ;
		ssb:l_customer ?customer ;
		ssb:l_supplier ?supplier ;
		ssb:l_orderdate ?Date .

	?customer skos:broader ?c_city .
	?c_city skos:broader ?c_nation .
	?c_nation ssb:nation_name  'UNITEDSTATES' .

	?supplier skos:broader ?s_city .
	?s_city skos:broader ?s_nation .
	?s_nation ssb:nation_name 'UNITEDSTATES' .

	?Date skos:broader ?month .
	?month skos:broader ?year .
	?year ssb:date_year ?d_year .

	FILTER(?d_year >= 1992 && ?d_year <= 1997)
}
GROUP BY ?c_city ?s_city ?year 
ORDER BY ASC(?year) DESC(?lo_revenue)

########### SSB Q9
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX qb: <http://purl.org/linked-data/cube#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX ssb: <http://ssb.org/schemas/> 
PREFIX ssb-inst: <http://ssb.org/data/>
SELECT ?c_city ?s_city ?year (SUM(?revenue) AS ?lo_revenue) 
WHERE {
	?li a qb:Observation ; 
		ssb:l_revenue ?revenue ;
		ssb:l_customer ?customer ;
		ssb:l_supplier ?supplier ;
		ssb:l_orderdate ?Date .

	?customer skos:broader ?c_city .
	?c_city ssb:city_name ?c_cityname .
	
	?supplier skos:broader ?s_city .
	?s_city ssb:city_name ?s_cityname . 

	?Date skos:broader ?month .
	?month skos:broader ?year .
	?year ssb:date_year ?d_year .

	FILTER(?d_year >= 1992 && ?d_year <= 1997)
	FILTER((?c_cityname='UNITEDKI1' || ?c_cityname='UNITEDKI5') && (?s_cityname='UNITEDKI1' || ?s_cityname='UNITEDKI5'))
}
GROUP BY ?c_city ?s_city ?year 
ORDER BY ASC(?year) DESC(?lo_revenue)

########### SSB Q10
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX qb: <http://purl.org/linked-data/cube#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX ssb: <http://ssb.org/schemas/> 
PREFIX ssb-inst: <http://ssb.org/data/>
SELECT ?c_city ?s_city ?year (SUM(?revenue) AS ?lo_revenue)
WHERE {
	?li a qb:Observation ; 
		ssb:l_revenue ?revenue ;
		ssb:l_customer ?customer ;
		ssb:l_supplier ?supplier ;
		ssb:l_orderdate ?Date .


	?customer skos:broader ?c_city .
	?c_city ssb:city_name ?c_cityname .
	
	?supplier skos:broader ?s_city .
	?s_city ssb:city_name ?s_cityname . 

	?Date skos:broader ?month .
	?month skos:broader ?year .
	?year ssb:date_year ?d_year .
	?month ssb:date_yearmonth ?d_yearmonth .

	FILTER(?d_yearmonth = '199712')
	FILTER((?c_cityname='UNITEDKI1' || ?c_cityname='UNITEDKI5') && (?s_cityname='UNITEDKI1' || ?s_cityname='UNITEDKI5'))
}
GROUP BY ?c_city ?s_city ?year 
ORDER BY ASC(?year) DESC(?lo_revenue)

########### SSB Q11
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX qb: <http://purl.org/linked-data/cube#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX ssb: <http://ssb.org/schemas/> 
PREFIX ssb-inst: <http://ssb.org/data/>
SELECT ?year ?c_nation (SUM(?revenue - ?supplycost) AS ?sum_profit) 
WHERE {
	?li a qb:Observation ; 
		ssb:l_revenue ?revenue ;
		ssb:l_supplycost ?supplycost ;
		ssb:l_part ?part ;
		ssb:l_supplier ?supplier ;
		ssb:l_customer ?customer ;
		ssb:l_orderdate ?Date .

	?customer skos:broader ?c_city .
	?c_city skos:broader ?c_nation .
	?supplier skos:broader ?s_city .
	?s_city skos:broader ?s_nation .
	?Date skos:broader ?month .
	?month skos:broader ?year .
	?part skos:broader ?brand .
	?brand skos:broader ?category .
	?category skos:broader ?manufacturer .		
	?c_nation skos:broader ?c_region .
	?c_region ssb:region_name 'AMERICA' .
	?s_nation skos:broader ?s_region .
	?s_region ssb:region_name 'AMERICA' .
	?manufacturer ssb:manufacturer_name ?mnf_name .
	FILTER(?year = <http://ssb.org/data/year_1992>)
	FILTER((?mnf_name = 'MFGR_1' || ?mnf_name = 'MFGR_2')).
}
GROUP BY ?year ?c_nation 
ORDER BY ?year ?c_nation

############# SSB Q12
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX qb: <http://purl.org/linked-data/cube#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX ssb: <http://ssb.org/schemas/> 
PREFIX ssb-inst: <http://ssb.org/data/>
SELECT ?year ?s_nation ?category (SUM(?revenue - ?supplycost) AS ?sum_profit) 
WHERE {
	?li a qb:Observation ; 
		ssb:l_revenue ?revenue ;
		ssb:l_supplycost ?supplycost ;
		ssb:l_part ?part ;
		ssb:l_supplier ?supplier ;
		ssb:l_customer ?customer ;
		ssb:l_orderdate ?Date .

	?customer skos:broader ?c_city .
	?c_city skos:broader ?c_nation .
	?supplier skos:broader ?s_city .
	?s_city skos:broader ?s_nation .
	?Date skos:broader ?month .
	?month skos:broader ?year .
	?part skos:broader ?brand .
	?brand skos:broader ?category .
	?category skos:broader ?manufacturer .
	?c_nation skos:broader ?c_region .
	?c_region ssb:region_name 'AMERICA' .
	?s_nation skos:broader ?s_region .
	?s_region ssb:region_name 'AMERICA' .
	?manufacturer ssb:manufacturer_name ?mnf_name .
	FILTER(?year = <http://ssb.org/data/year_1997> || ?year = <http://ssb.org/data/year_1998>)
	FILTER(?mnf_name = 'MFGR_1' || ?mnf_name = 'MFGR_2')
}
GROUP BY ?year ?s_nation ?category 
ORDER BY ?year ?s_nation ?category

########### SSB Q13
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#> 
PREFIX qb: <http://purl.org/linked-data/cube#> 
PREFIX skos: <http://www.w3.org/2004/02/skos/core#> 
PREFIX ssb: <http://ssb.org/schemas/> 
PREFIX ssb-inst: <http://ssb.org/data/>
SELECT ?year ?s_city ?brand SUM(?revenue - ?supplycost) AS ?sum_profit 
WHERE {
	?li a qb:Observation ; 
		ssb:l_revenue ?revenue ;
		ssb:l_customer ?customer ;
		ssb:l_supplier ?supplier ;
		ssb:l_orderdate ?Date ;
		ssb:l_part ?part ;
		ssb:l_supplycost ?supplycost .


	?customer skos:broader ?c_city .
	?c_city skos:broader ?c_nation .
	?c_nation skos:broader ?c_region .
	?c_region ssb:region_name  'AMERICA' .
	
	?supplier skos:broader ?s_city .
	?s_city skos:broader ?s_nation .
	?s_nation ssb:nation_name 'UNITEDSTATES' .
	
	?part skos:broader ?brand .
	?brand skos:broader ?category .
	?category ssb:category_name 'MFGR_14' .
	
	?Date skos:broader ?month .
	?month skos:broader ?year .

	FILTER(?year = <http://ssb.org/data/year_1997> || ?year = <http://ssb.org/data/year_1998>)
}
GROUP BY ?year ?s_city ?brand 
ORDER BY ?year ?s_city ?brand