######### SSB Q1.1 (1)
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT (SUM(?lo_extendedprice * ?lo_discount) AS ?sum_revenue)
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_extendedprice ?lo_extendedprice ;
	   rdfh:lo_discount ?lo_discount ;
	   rdfh:lo_quantity ?lo_quantity .

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
	}

	FILTER(?d_year = 1993)
	FILTER((?lo_discount >= 1) && (?lo_discount <= 3) && (?lo_quantity < 25)) 
}
		
######### SSB Q1.2 (2)
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT (SUM(?lo_extendedprice * ?lo_discount) AS ?sum_revenue)
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_extendedprice ?lo_extendedprice ;
	   rdfh:lo_discount ?lo_discount ;
	   rdfh:lo_quantity ?lo_quantity .

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
		?lo_orderdate rdfh:d_month ?d_month .
	}

	FILTER(?d_year = 1993 && ?d_month = 'January')
	FILTER((?lo_discount >= 1) && (?lo_discount <= 3) && (?lo_quantity < 25)) 
}
		
######### SSB Q1.3 (3)
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT (SUM(?lo_extendedprice * ?lo_discount) AS ?sum_revenue)
WHERE {
	?li a rdfh:lineorder ;
		rdfh:lo_orderdate ?lo_orderdate ;
		rdfh:lo_extendedprice ?lo_extendedprice ;
		rdfh:lo_discount ?lo_discount ;
		rdfh:lo_quantity ?lo_quantity .

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_weeknuminyear ?d_weeknuminyear .
		?lo_orderdate rdfh:d_year ?d_year .
	}

	FILTER(?d_weeknuminyear = 6 && ?d_year = 1994)
	FILTER((?lo_discount >= 5) && (?lo_discount <= 7) && (?lo_quantity >= 26) && (?lo_quantity <= 35)) 
}

######### SSB Q2.1 (4)
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT (SUM(?rdfh_lo_revenue) AS ?lo_revenue) ?d_year ?p_brand1 
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_partkey ?lo_partkey ;
	   rdfh:lo_suppkey ?lo_suppkey ;
	   rdfh:lo_revenue ?rdfh_lo_revenue .

	?lo_partkey rdfh:p_brand1 ?p_brand1 .
	?lo_partkey rdfh:p_category 'MFGR#12' .
	?lo_suppkey rdfh:s_region 'AMERICA' . 

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
	}
}
GROUP BY ?d_year ?p_brand1 
ORDER BY ?d_year ?p_brand1
		
#########  SSB Q2.2 (5)
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT (SUM(?rdfh_lo_revenue) AS ?lo_revenue) ?d_year ?p_brand1 
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_partkey ?lo_partkey ;
	   rdfh:lo_suppkey ?lo_suppkey ;
	   rdfh:lo_revenue ?rdfh_lo_revenue .
	?lo_partkey rdfh:p_brand1 ?p_brand1 .
	?lo_suppkey rdfh:s_region 'ASIA' . 

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
	}

	FILTER(STR(?p_brand1) >= 'MFGR#2221' && STR(?p_brand1) <= 'MFGR#2228')
}
GROUP BY ?d_year ?p_brand1 
ORDER BY ?d_year ?p_brand1

#########  SSB Q2.3 (6)
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT (SUM(?rdfh_lo_revenue) AS ?lo_revenue) ?d_year ?p_brand1 
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_partkey ?lo_partkey ;
	   rdfh:lo_suppkey ?lo_suppkey ;
	   rdfh:lo_revenue ?rdfh_lo_revenue .

	?lo_partkey rdfh:p_brand1 ?p_brand1 .
	?lo_suppkey rdfh:s_region 'EUROPE' . 

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
	}

	FILTER (STR(?p_brand1) = 'MFGR#2239')
}
GROUP BY ?d_year ?p_brand1 
ORDER BY ?d_year ?p_brand1
		
######### SSB Q3.1 (7)
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT  ?c_nation ?s_nation ?d_year (SUM(?rdfh_lo_revenue) AS ?lo_revenue) 
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_custkey ?lo_custkey ;
	   rdfh:lo_suppkey ?lo_suppkey ;
	   rdfh:lo_revenue ?rdfh_lo_revenue .

	?lo_custkey rdfh:c_nation ?c_nation .
	?lo_custkey rdfh:c_region ?c_region .
	?lo_suppkey rdfh:s_nation ?s_nation .
	?lo_suppkey rdfh:s_region ?s_region . 

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
	}
	FILTER(?c_region = 'ASIA')
	FILTER(?s_region = 'ASIA')
	FILTER(?d_year >= 1992 && ?d_year <= 1997)
}
GROUP BY ?c_nation ?s_nation ?d_year 
ORDER BY ASC(?d_year) DESC(?lo_revenue)
	
########### SSB Q3.2 (8)
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT ?c_city ?s_city ?d_year (SUM(?rdfh_lo_revenue) AS ?lo_revenue) 
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_custkey ?lo_custkey ;
	   rdfh:lo_suppkey ?lo_suppkey ;
	   rdfh:lo_revenue ?rdfh_lo_revenue .

	?lo_custkey rdfh:c_nation 'UNITED STATES' .
	?lo_custkey rdfh:c_city ?c_city .
	?lo_suppkey rdfh:s_nation 'UNITED STATES' .
	?lo_suppkey rdfh:s_city ?s_city . 

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
	}

	FILTER(?d_year >= 1992 && ?d_year <= 1997)
}
GROUP BY ?c_city ?s_city ?d_year 
ORDER BY ASC(?d_year) DESC(?lo_revenue)
		
########### SSB Q3.3 (9)
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT ?c_city ?s_city ?d_year (SUM(?rdfh_lo_revenue) AS ?lo_revenue) 
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_custkey ?lo_custkey ;
	   rdfh:lo_suppkey ?lo_suppkey ;
	   rdfh:lo_revenue ?rdfh_lo_revenue .
	?lo_custkey rdfh:c_city ?c_city .
	?lo_suppkey rdfh:s_city ?s_city . 

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
	}

	FILTER(?d_year >= 1992 && ?d_year <= 1997)
	FILTER((?c_city='UNITED KI1' || ?c_city='UNITED KI5') && (?s_city='UNITED KI1' || ?s_city='UNITED KI5'))
}
GROUP BY ?c_city ?s_city ?d_year 
ORDER BY ASC(?d_year) DESC(?lo_revenue)

########### SSB Q3.4 (10) 
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT ?c_city ?s_city ?d_year (SUM(?rdfh_lo_revenue) AS ?lo_revenue)
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_custkey ?lo_custkey ;
	   rdfh:lo_suppkey ?lo_suppkey ;
	   rdfh:lo_revenue ?rdfh_lo_revenue .
	?lo_custkey rdfh:c_city ?c_city .
	?lo_suppkey rdfh:s_city ?s_city . 

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
		?lo_orderdate rdfh:d_yearmonth ?d_yearmonth .
	}

	FILTER(?d_yearmonth = 'Dec1997')
	FILTER((?c_city='UNITED KI1' || ?c_city='UNITED KI5') && (?s_city='UNITED KI1' || ?s_city='UNITED KI5'))
}
GROUP BY ?c_city ?s_city ?d_year 
ORDER BY ASC(?d_year) DESC(?lo_revenue) 

########### SSB Q4.1 (11) 
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT ?d_year ?c_nation (SUM(?lo_revenue - ?lo_supplycost) AS ?sum_profit) 
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_custkey ?lo_custkey ;
	   rdfh:lo_suppkey ?lo_suppkey ;
	   rdfh:lo_revenue ?lo_revenue ;
	   rdfh:lo_partkey ?lo_partkey ;
	   rdfh:lo_supplycost ?lo_supplycost .

	?lo_custkey rdfh:c_region 'AMERICA' .
	?lo_suppkey rdfh:s_region 'AMERICA' .
	?lo_custkey rdfh:c_nation ?c_nation .
	?lo_partkey rdfh:p_mfgr ?p_mfgr . 

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
	}
	FILTER(?d_year = 1992)
	FILTER((?p_mfgr = 'MFGR#1' || ?p_mfgr = 'MFGR#2')).
}
GROUP BY ?d_year ?c_nation 
ORDER BY ?d_year ?c_nation
		
############# SSB Q4.2 (12)
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT ?d_year ?s_nation ?p_category (SUM(?lo_revenue - ?lo_supplycost) AS ?sum_profit) 
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_custkey ?lo_custkey ;
	   rdfh:lo_partkey ?lo_partkey ;
	   rdfh:lo_suppkey ?lo_suppkey ;
	   rdfh:lo_revenue ?lo_revenue ;
	   rdfh:lo_supplycost ?lo_supplycost .

	?lo_custkey rdfh:c_region 'AMERICA' .
	?lo_suppkey rdfh:s_region 'AMERICA' .
	?lo_suppkey rdfh:s_nation ?s_nation .
	?lo_partkey rdfh:p_mfgr ?p_mfgr .
	?lo_partkey rdfh:p_category ?p_category .  

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
	}

	FILTER(?d_year = 1997 || ?d_year = 1998)
	FILTER(?p_mfgr = 'MFGR#1' || ?p_mfgr = 'MFGR#2')
}
GROUP BY ?d_year ?s_nation ?p_category 
ORDER BY ?d_year ?s_nation ?p_category
		
########### SSB Q4.3 (13)
PREFIX rdfh: <http://lod2.eu/schemas/rdfh#>
SELECT ?d_year ?s_city ?p_brand1 SUM(?lo_revenue - ?lo_supplycost) AS ?sum_profit 
WHERE {
	?li a rdfh:lineorder ;
	   rdfh:lo_orderdate ?lo_orderdate ;
	   rdfh:lo_custkey ?lo_custkey ;
	   rdfh:lo_partkey ?lo_partkey ;
	   rdfh:lo_suppkey ?lo_suppkey ;
	   rdfh:lo_revenue ?lo_revenue ;
	   rdfh:lo_supplycost ?lo_supplycost .

	?lo_custkey rdfh:c_region 'AMERICA' .
	?lo_suppkey rdfh:s_city ?s_city .
	?lo_suppkey rdfh:s_nation 'UNITED STATES' .
	?lo_partkey rdfh:p_category 'MFGR#14' .  
	?lo_partkey rdfh:p_brand1 ?p_brand1 .

	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
	}

	FILTER(?d_year = 1997 || ?d_year = 1998)
}
GROUP BY ?d_year ?s_city ?p_brand1 
ORDER BY ?d_year ?s_city ?p_brand1