######### 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' .
     
    SERVICE <http://10.15.121.3:8890/sparql> {
        ?lo_orderdate rdfh:d_year ?d_year .
    }

    SERVICE <http://10.15.121.2:8890/sparql> {
	    ?lo_suppkey rdfh:s_region ?s_region .
        FILTER(?s_region = 'AMERICA')
    }
}
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 .
    
    SERVICE <http://10.15.121.3:8890/sparql> {
        ?lo_orderdate rdfh:d_year ?d_year .
    }
    SERVICE <http://10.15.121.2:8890/sparql> {
        ?lo_suppkey rdfh:s_region ?s_region .
        FILTER(?s_region = 'ASIA')
    }

    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 .
    
    SERVICE <http://10.15.121.3:8890/sparql> {
        ?lo_orderdate rdfh:d_year ?d_year .
    }

    SERVICE <http://10.15.121.2:8890/sparql> {
        ?lo_suppkey rdfh:s_region ?s_region .
        FILTER(?s_region = 'EUROPE')
    }
    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 'ASIA' .
	
	SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
		FILTER(?d_year >= 1992 && ?d_year <= 1997)
	}
    SERVICE <http://10.15.121.2:8890/sparql> {
        ?lo_suppkey rdfh:s_nation ?s_nation .
        ?lo_suppkey rdfh:s_region ?s_region .
        FILTER(?s_region = 'ASIA')
    }    
}
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 .
     
	
    SERVICE <http://10.15.121.3:8890/sparql> {
		?lo_orderdate rdfh:d_year ?d_year .
		FILTER(?d_year >= 1992 && ?d_year <= 1997)
	}

    SERVICE <http://10.15.121.2:8890/sparql> {
        ?lo_suppkey rdfh:s_city ?s_city .
        ?lo_suppkey rdfh:s_nation ?s_nation .
        FILTER(?s_nation = 'UNITED STATES')
    }    
}
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 . 
	
    SERVICE <http://10.15.121.3:8890/sparql> {
        ?lo_orderdate rdfh:d_year ?d_year .
        ?lo_orderdate rdfh:d_yearmonth ?d_yearmonth .
		FILTER(?d_year >= 1992 && ?d_year <= 1997)
    }
	
	SERVICE <http://10.15.121.2:8890/sparql> {
        ?lo_suppkey rdfh:s_city ?s_city . 
        FILTER(?s_city='UNITED KI1' || ?s_city='UNITED KI5')
    }
    
	FILTER(?c_city='UNITED KI1' || ?c_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 . 
	
    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')
    }
	
	SERVICE <http://10.15.121.2:8890/sparql> {
        ?lo_suppkey rdfh:s_city ?s_city . 
        FILTER(?s_city='UNITED KI1' || ?s_city='UNITED KI5')
    }
    
	FILTER(?c_city='UNITED KI1' || ?c_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_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)
    }

    SERVICE <http://10.15.121.2:8890/sparql> {
        ?lo_suppkey rdfh:s_region ?s_region .
        FILTER(?s_region='AMERICA')
    }

    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_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)
    }
    
    SERVICE <http://10.15.121.2:8890/sparql> {
        ?lo_suppkey rdfh:s_nation ?s_nation .
        ?lo_suppkey rdfh:s_region ?s_region .
        FILTER(?s_region='AMERICA')
    }
    
	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_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)
    }
	
    SERVICE <http://10.15.121.2:8890/sparql> {
        ?lo_suppkey rdfh:s_city ?s_city .
        ?lo_suppkey rdfh:s_nation ?s_nation .        
        FILTER(?s_nation='UNITED STATES')
    }
}
GROUP BY ?d_year ?s_city ?p_brand1 
ORDER BY ?d_year ?s_city ?p_brand1