######### Q1.1 (1)
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT (SUM(?lo_extendedprice * ?lo_discount) AS ?sum_revenue)
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_extendedprice ?lo_extendedprice ;
        schema:lo_discount ?lo_discount ;
        schema:lo_quantity ?lo_quantity .
    ?orderdatekey schema:year ?d_year .
    
    FILTER(?d_year = 1993)
    FILTER((?lo_discount >= 1) && (?lo_discount <= 3) && (?lo_quantity < 25)) 
}


######### Q1.2 (2)
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT (SUM(?lo_extendedprice * ?lo_discount) AS ?sum_revenue)
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_extendedprice ?lo_extendedprice ;
        schema:lo_discount ?lo_discount ;
        schema:lo_quantity ?lo_quantity .
    ?orderdatekey schema:year ?d_year .
    ?orderdatekey schema:month ?d_month .	

    FILTER(?d_year = 1993 && ?d_month = 'January')
    FILTER((?lo_discount >= 1) && (?lo_discount <= 3) && (?lo_quantity < 25)) 
}


######### Q1.3 (3)
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT (SUM(?lo_extendedprice * ?lo_discount) AS ?sum_revenue)
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_extendedprice ?lo_extendedprice ;
        schema:lo_discount ?lo_discount ;
        schema:lo_quantity ?lo_quantity .
    ?orderdatekey schema:year ?d_year .
    ?orderdatekey schema:weeknuminyear ?d_weeknuminyear .
    FILTER(?d_weeknuminyear = 6 && ?d_year = 1994)
    FILTER((?lo_discount >= 5) && (?lo_discount <= 7) && (?lo_quantity >= 26) && (?lo_quantity <= 35)) 
}


######### Q2.1 (4)
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT (SUM(?revenue) AS ?sum_revenue) ?d_year ?p_brand1 ?s_cont_name
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_suppkey ?suppkey ;
        schema:lo_partkey ?lo_partkey ;
        schema:lo_revenue ?revenue .

    ?orderdatekey schema:year ?d_year .
    ?lo_partkey schema:brand1 ?p_brand1 .
    ?lo_partkey schema:category 'MFGR#12' .
    ?suppkey schema:supcity ?s_city .
    ?s_city owl:sameAs ?geo_scity .
    ?suppkey schema:supregion 'AMERICA' .
	
    SERVICE <http://10.15.120.1:8890/sparql> {
        ?geo_scity gn:parentCountry ?s_country .
        ?s_country gn:parentFeature ?s_continent .
        ?s_continent gn:name ?s_cont_name . 
    }
}
GROUP BY ?d_year ?p_brand1 ?s_cont_name
ORDER BY ?d_year ?p_brand1 


######### Q2.2 (5)
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT (SUM(?revenue) AS ?sum_revenue) ?d_year ?p_brand1 ?s_cont_name
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_custkey ?custkey ;
        schema:lo_suppkey ?suppkey ;
        schema:lo_partkey ?lo_partkey ;
        schema:lo_revenue ?revenue .

    ?lo_partkey schema:brand1 ?p_brand1 .
    ?suppkey schema:supcity ?s_city .
    ?s_city owl:sameAs ?geo_scity .
    ?suppkey schema:supregion 'ASIA' . 
    ?orderdatekey schema:year ?d_year .    
    SERVICE <http://10.15.120.1:8890/sparql> {
        ?geo_scity gn:parentCountry ?s_country .
        ?s_country gn:parentFeature ?s_continent .
        ?s_continent gn:name ?s_cont_name .
    }
    
    FILTER(STR(?p_brand1) >= 'MFGR#2221' && STR(?p_brand1) <= 'MFGR#2228')
}
GROUP BY ?d_year ?p_brand1 ?s_cont_name
ORDER BY ?d_year ?p_brand1


######### Q2.3 (6)
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT (SUM(?revenue) AS ?sum_revenue) ?d_year ?p_brand1 ?s_cont_name 
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_suppkey ?suppkey ;
        schema:lo_partkey ?lo_partkey ;
        schema:lo_revenue ?revenue .

    ?lo_partkey schema:brand1 ?p_brand1 .
    ?suppkey schema:supcity ?s_city .
    ?s_city owl:sameAs ?geo_scity .
    ?suppkey schema:supregion 'EUROPE' . 
    ?orderdatekey schema:year ?d_year .
    SERVICE <http://10.15.120.1:8890/sparql> {
        ?geo_scity gn:parentCountry ?s_country .
        ?s_country gn:parentFeature ?s_continent .
        ?s_continent gn:name ?s_cont_name .  
    }
    
    FILTER (STR(?p_brand1) = 'MFGR#2239')
}
GROUP BY ?d_year ?p_brand1 ?s_cont_name 
ORDER BY ?d_year ?p_brand1
        


######### Q3.1 (7)
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT  ?c_nation ?s_nation ?d_year ?s_reg_name ?c_reg_name (SUM(?revenue) AS ?sum_revenue) 
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_custkey ?custkey ;
        schema:lo_suppkey ?suppkey ;
        schema:lo_revenue ?revenue .
    ?custkey schema:custcity ?c_city .
    ?c_city owl:sameAs ?geo_ccity .
    ?suppkey schema:supcity ?s_city .
    ?s_city owl:sameAs ?geo_scity .
    ?custkey schema:custnation ?c_nation .
    ?custkey schema:custregion 'ASIA' .
    ?suppkey schema:supnation ?s_nation .
    ?suppkey schema:supregion 'ASIA' . 
    ?orderdatekey schema:year ?d_year .		
	
    SERVICE <http://10.15.120.1:8890/sparql> {
        ?geo_scity gn:parentADM1 ?s_adm1 .
        ?s_adm1 gn:name ?s_reg_name .
        ?geo_ccity gn:parentADM1 ?c_adm1 .
        ?c_adm1 gn:name ?c_reg_name .
    }
    FILTER(?d_year >= 1992 && ?d_year <= 1997)
}
GROUP BY ?c_nation ?s_nation ?d_year ?s_reg_name ?c_reg_name
ORDER BY ASC(?d_year) DESC(?sum_revenue)

########### Q3.2 (8)
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT ?cust_adm1name ?sup_adm1name ?d_year (SUM(?revenue) AS ?sum_revenue) 
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_custkey ?custkey ;
        schema:lo_suppkey ?suppkey ;
        schema:lo_revenue ?revenue .

    ?custkey schema:custnation ?custnation .
    ?custnation schema:countryname 'UNITED_STATES' .
    ?custkey schema:custcity ?c_city .
    ?c_city owl:sameAs ?geo_ccity .
    ?suppkey schema:supnation ?supnation .
    ?supnation schema:countryname 'UNITED_STATES' .
    ?suppkey schema:supcity ?s_city .
    ?s_city owl:sameAs ?geo_scity .	
    ?orderdatekey schema:year ?d_year .
    FILTER(?d_year >= 1992 && ?d_year <= 1997)
    
    SERVICE <http://10.15.120.1:8890/sparql> {
        ?geo_ccity gn:parentFeature ?cust_adm1 .
        ?cust_adm1 gn:name ?cust_adm1name . 
        ?geo_scity gn:parentFeature ?sup_adm1 .
        ?sup_adm1 gn:name ?sup_adm1name . 
    }
}
GROUP BY ?cust_adm1name ?sup_adm1name ?d_year 
ORDER BY ASC(?d_year) DESC(?sum_revenue)


########### Q3.3 (9)
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT ?cust_adm1name ?sup_adm1name ?d_year (SUM(?revenue) AS ?sum_revenue) 
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_custkey ?custkey ;
        schema:lo_suppkey ?suppkey ;
        schema:lo_revenue ?revenue .
    ?custkey schema:custcity ?c_city .
    ?suppkey schema:supcity ?s_city .
    ?c_city owl:sameAs ?geo_ccity .
    ?s_city owl:sameAs ?geo_scity .
    ?orderdatekey schema:year ?d_year .
	
    SERVICE <http://10.15.120.1:8890/sparql> {
        ?geo_ccity gn:parentFeature ?cust_adm1 .
        ?cust_adm1 gn:name ?cust_adm1name . 
        ?geo_scity gn:parentFeature ?sup_adm1 .
        ?sup_adm1 gn:name ?sup_adm1name .
    }

    FILTER(?d_year >= 1992 && ?d_year <= 1997) #http://ssb.org/inst#city_UNITEDKI1
    FILTER((?c_city=inst:city_UNITEDKI1 || ?c_city=inst:city_UNITEDKI5) && (?s_city=inst:city_UNITEDKI9 || ?s_city=inst:city_UNITEDKI5))
}
GROUP BY ?cust_adm1name ?sup_adm1name ?d_year 
ORDER BY ASC(?d_year) DESC(?sum_revenue)


########### Q3.4 (10) 
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT ?cust_adm1name ?sup_adm1name ?d_year (SUM(?revenue) AS ?sum_revenue)
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_custkey ?custkey ;
        schema:lo_suppkey ?suppkey ;
        schema:lo_revenue ?revenue .
    ?custkey schema:custcity ?c_city .
    ?suppkey schema:supcity ?s_city . 
    ?c_city owl:sameAs ?geo_ccity .
    ?s_city owl:sameAs ?geo_scity .
    ?orderdatekey schema:year ?d_year .
    ?orderdatekey schema:yearmonth ?d_yearmonth .
    
    SERVICE <http://10.15.120.1:8890/sparql> {
        ?geo_ccity gn:parentFeature ?cust_adm1 .
        ?cust_adm1 gn:name ?cust_adm1name . 
        ?geo_scity gn:parentFeature ?sup_adm1 .
        ?sup_adm1 gn:name ?sup_adm1name .
    }
	
    FILTER(?d_yearmonth = 'Dec1997')
    FILTER((?c_city=inst:city_UNITEDKI1 || ?c_city=inst:city_UNITEDKI5) && (?s_city=inst:city_UNITEDKI9 || ?s_city=inst:city_UNITEDKI5))
}
GROUP BY ?cust_adm1name ?sup_adm1name ?d_year 
ORDER BY ASC(?d_year) DESC(?sum_revenue)


########### Q4.1 (11) 
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT ?d_year ?cust_regname (SUM(?revenue - ?supplycost) AS ?sum_profit) 
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_custkey ?custkey ;
        schema:lo_suppkey ?suppkey ;
        schema:lo_revenue ?revenue ;
        schema:lo_partkey ?partkey ;
        schema:lo_supplycost ?supplycost .

    ?custkey schema:custcity ?c_city .
    ?c_city owl:sameAs ?geo_ccity .
    ?suppkey schema:supcity ?s_city .
    ?s_city owl:sameAs ?geo_scity .
    ?custkey schema:custregion 'AMERICA' .
    ?suppkey schema:supregion 'AMERICA' .
    ?partkey schema:mfgr ?p_mfgr . 
    ?orderdatekey schema:year ?d_year .

    SERVICE <http://10.15.120.1:8890/sparql> {
        ?geo_ccity gn:parentCountry ?cust_country .
        ?cust_country gn:parentFeature ?cust_reg .
        ?cust_reg gn:name ?cust_regname . 
    }
    FILTER(?d_year = 1992)
    FILTER((?p_mfgr = 'MFGR#1' || ?p_mfgr = 'MFGR#2')).
}
GROUP BY ?d_year ?cust_regname
ORDER BY ?d_year ?cust_regname


############# Q4.2 (12)
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT ?d_year ?sup_regname ?p_category (SUM(?revenue - ?supplycost) AS ?sum_profit) 
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_custkey ?custkey ;
        schema:lo_suppkey ?suppkey ;
        schema:lo_revenue ?revenue ;
        schema:lo_partkey ?partkey ;
        schema:lo_supplycost ?supplycost .

    ?custkey schema:custcity ?c_city .
    ?c_city owl:sameAs ?geo_ccity .
    ?suppkey schema:supcity ?s_city .
    ?s_city owl:sameAs ?geo_scity .
    ?custkey schema:custregion 'AMERICA' .
    ?suppkey schema:supregion 'AMERICA' .
    ?partkey schema:mfgr ?p_mfgr .
    ?partkey schema:category ?p_category .  
    ?orderdatekey schema:year ?d_year .

    SERVICE <http://10.15.120.1:8890/sparql> {
        ?geo_scity gn:parentCountry ?sup_country .
        ?sup_country gn:parentFeature ?sup_reg .
        ?sup_reg gn:name ?sup_regname . 
    }

    FILTER(?d_year = 1997 || ?d_year = 1998)
    FILTER(?p_mfgr = 'MFGR#1' || ?p_mfgr = 'MFGR#2')
}
GROUP BY ?d_year ?sup_regname ?p_category 
ORDER BY ?d_year ?sup_regname ?p_category


########### Q4.3 (13)
PREFIX xsd: <http://www.w3.org/2001/XMLSchema#>
PREFIX schema: <http://ssb.org/schema#>
PREFIX inst: <http://ssb.org/inst#>
PREFIX owl: <http://www.w3c.org/2002/07/owl#>
PREFIX gn: <http://www.geonames.org/ontology#>

SELECT ?d_year ?sup_adm1name ?p_brand1 (SUM(?revenue - ?supplycost) AS ?sum_profit) 
WHERE {
    ?li a schema:lineorder ;
        schema:lo_orderdate ?orderdatekey ;
        schema:lo_custkey ?custkey ;
        schema:lo_suppkey ?suppkey ;
        schema:lo_revenue ?revenue ;
        schema:lo_partkey ?partkey ;
        schema:lo_supplycost ?supplycost .

    ?custkey schema:custregion 'AMERICA' .
    ?custkey schema:custcity ?c_city .
    ?c_city owl:sameAs ?geo_ccity .
    ?suppkey schema:supcity ?s_city .
    ?s_city owl:sameAs ?geo_scity .
    ?suppkey schema:supnation ?supnation .
    ?supnation schema:countryname 'UNITED_STATES' .
    ?partkey schema:category 'MFGR#14' .  
    ?partkey schema:brand1 ?p_brand1 .
    ?orderdatekey schema:year ?d_year .
	
    SERVICE <http://10.15.120.1:8890/sparql> { 
        ?geo_scity gn:parentFeature ?sup_adm1 .
        ?sup_adm1 gn:name ?sup_adm1name .
    }
    
    FILTER(?d_year = 1997 || ?d_year = 1998)
}
GROUP BY ?d_year ?sup_adm1name ?p_brand1 
ORDER BY ?d_year ?sup_adm1name ?p_brand1