All the queries in this tutorial can be verified against the live instance http://demo.openlinksw.com ,
where you have /sparql (basic SPARQL Endpoint) and /isparql (SPARQL Query By Example UI and Endpoint).
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?l+>tpch:returnflag,
?l+>tpch:linestatus,
SUM(?l+>tpch:linequantity) as ?sum_qty,
SUM(?l+>tpch:lineextendedprice) as ?sum_base_price,
SUM(?l+>tpch:lineextendedprice*(1 - ?l+>tpch:linediscount)) as ?sum_disc_price,
SUM(?l+>tpch:lineextendedprice*(1 - ?l+>tpch:linediscount)*(?l+>tpch:linetax)) as ?sum_charge,
AVG(?l+>tpch:linequantity) as ?avg_qty,
AVG(?l+>tpch:lineextendedprice) as ?avg_price,
AVG(?l+>tpch:linediscount) as ?avg_disc,
COUNT(1) as ?count_order
WHERE
{
?l a tpch:lineitem .
FILTER (?l+>tpch:shipdate <= bif:dateadd ("day", 90, ''1998-12-01''^^xsd:date))
}
ORDER BY ?l+>tpch:returnflag ?l+>tpch:linestatus
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
SELECT
?supp+>tpch:acctbal,
?supp+>tpch:name,
?supp+>tpch:has_nation+>tpch:name as ?nation_name,
?part+>tpch:partkey,
?part+>tpch:mfgr,
?supp+>tpch:address,
?supp+>tpch:phone,
?supp+>tpch:comment
WHERE
{
?ps a tpch:partsupp;
tpch:has_supplier ?supp;
tpch:has_part ?part .
?supp+>tpch:has_nation+>tpch:has_region tpch:name ''EUROPE'' .
?part tpch:size 15 .
?ps tpch:supplycost ?minsc .
{
SELECT ?part min(?ps+>tpch:supplycost) as ?minsc
WHERE
{
?ps a tpch:partsupp;
tpch:has_part ?part;
tpch:has_supplier ?ms .
?ms+>tpch:has_nation+>tpch:has_region tpch:name ''EUROPE'' .
}
}
FILTER (?part+>tpch:type like ''%BRASS'')
}
ORDER BY
desc (?supp+>tpch:acctbal)
?supp+>tpch:has_nation+>tpch:name
?supp+>tpch:name
?part+>tpch:partkey
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?ord+>tpch:orderkey,
SUM(?li+>tpch:lineextendedprice*(1 - ?li+>tpch:linediscount)) as ?revenue,
?ord+>tpch:orderdate,
?ord+>tpch:shippriority
WHERE
{
?cust a tpch:customer ;
tpch:mktsegment "BUILDING" ;
tpch:customer_of ?ord .
?li tpch:has_order ?ord .
FILTER ((?ord+>tpch:orderdate < "1995-03-15"^^xsd:date) &&
(?li+>tpch:shipdate > "1995-03-15"^^xsd:date) )
}
ORDER BY
desc (SUM (?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)))
?ord+>tpch:orderdate
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT (?ord+>tpch:orderpriority), COUNT(1) as ?order_count
WHERE
{
?ord a tpch:order .
{
SELECT ?ord COUNT(?li) as ?cnt
WHERE
{
?li tpch:has_order ?ord .
FILTER ( ?li+>tpch:commitdate < ?li+>tpch:receiptdate )
}
}
FILTER ((?ord+>tpch:orderdate >= "1993-07-01"^^xsd:date) &&
(?ord+>tpch:orderdate < bif:dateadd ("month", 3, "1993-07-01"^^xsd:date)) &&
(?cnt > 0) )
}
ORDER BY
?ord+>tpch:orderpriority
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?supp+>tpch:has_nation+>tpch:name as ?nation,
SUM(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)) as ?revenue
WHERE
{
?li a tpch:lineitem ; tpch:has_order ?ord ; tpch:has_supplier ?supp .
?ord tpch:has_customer ?cust .
?supp+>tpch:has_nation+>tpch:has_region tpch:name "ASIA" .
FILTER ((?cust+>tpch:has_nation = ?supp+>tpch:has_nation) &&
(?ord+>tpch:orderdate >= "1994-01-01"^^xsd:date) &&
(?ord+>tpch:orderdate < bif:dateadd ("year", 1,"1994-01-01" ^^xsd:date)) )
}
ORDER BY
desc (SUM(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)))
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
SUM(?li+>tpch:lineextendedprice * ?li+>tpch:linediscount) as ?revenue
WHERE
{
?li a tpch:lineitem .
FILTER ( (?li+>tpch:shipdate >= "1994-01-01"^^xsd:date) &&
(?li+>tpch:shipdate < bif:dateadd ("year", 1, "1994-01-01"^^xsd:date)) &&
(?li+>tpch:linediscount >= 0.06 - 0.01) &&
(?li+>tpch:linediscount <= 0.06 + 0.01) &&
(?li+>tpch:linequantity < 24) )
}
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT ?supp_nation ?cust_nation ?li_year
SUM (?value) as ?revenue
WHERE
{
{
SELECT
?suppn+>tpch:name as ?supp_nation,
?custn+>tpch:name as ?cust_nation,
(bif:year (?li+>tpch:shipdate)) as ?li_year,
(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)) as ?value
WHERE
{
?li a tpch:lineitem ; tpch:has_order ?ord ; tpch:has_supplier ?supp .
?ord tpch:has_customer ?cust .
?cust tpch:has_nation ?custn .
?supp tpch:has_nation ?suppn .
FILTER ((
(?custn+>tpch:name = "FRANCE" and ?suppn+>tpch:name = "GERMANY") ||
(?custn+>tpch:name = "GERMANY" and ?suppn+>tpch:name = "FRANCE") ) &&
(?li+>tpch:shipdate >= "1995-01-01"^^xsd:date) &&
(?li+>tpch:shipdate <= "1996-12-31"^^xsd:date) )
}
}
}
ORDER BY
?supp_nation
?li_year
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?o_year,
(?sum1 / ?sum2) as ?mkt_share
WHERE
{
{
SELECT
?o_year
SUM (?volume * bif:equ (?nation, "BRAZIL")) as ?sum1
SUM (?volume) as ?sum2
WHERE
{
{
SELECT
(bif:year (?ord+>tpch:orderdate)) as ?o_year,
(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)) as ?volume,
?n2+>tpch:name as ?nation
WHERE
{
?li a tpch:lineitem ; tpch:has_order ?ord ; tpch:has_part ?part .
?li+>tpch:has_supplier tpch:has_nation ?n2 .
?order+>tpch:has_customer+>tpch:has_nation+>tpch:has_region tpch:name "AMERICA" .
?part tpch:type "ECONOMY ANODIZED STEEL" .
FILTER ((?ord+>tpch:orderdate >= "1995-01-01"^^xsd:date) &&
(?ord+>tpch:orderdate <= "1996-12-31"^^xsd:date) )
}
}
}
}
}
ORDER BY
?o_year
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?nation,
?o_year,
SUM(?amount) as ?sum_profit
WHERE
{
{
SELECT
?supp+>tpch:has_nation+>tpch:name as ?nation,
(bif:year (?ord+>tpch:orderdate)) as ?o_year,
(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount) - ?ps+>tpch:supplycost * ?li+>tpch:linequantity) as ?amount
WHERE
{
?li a tpch:lineitem ;
tpch:has_order ?ord ;
tpch:has_supplier ?supp ;
tpch:has_part ?part .
?ps a tpch:partsupp ;
tpch:has_part ?part ;
tpch:has_supplier ?supp .
FILTER (?part+>tpch:name like "%green%")
}
}
}
ORDER BY ?nation desc (?o_year)
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?cust+>tpch:custkey,
?cust+>tpch:companyName,
(SUM(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount))) as ?revenue,
?cust+>tpch:acctbal,
?cust+>tpch:has_nation+>tpch:name as ?nation,
?cust+>tpch:address,
?cust+>tpch:phone,
?cust+>tpch:comment
WHERE
{
?li tpch:returnflag "R" ; tpch:has_order ?ord .
?ord tpch:has_customer ?cust .
FILTER ((?ord+>tpch:orderdate >= "1993-10-01"^^xsd:date) &&
(?ord+>tpch:orderdate < bif:dateadd ("month", 3, "1993-10-01"^^xsd:date)) )
}
ORDER BY
desc (SUM(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)))
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?bigps+>tpch:has_part,
?bigpsvalue
WHERE
{
{
SELECT
(SUM(?thr_ps+>tpch:supplycost * ?thr_ps+>tpch:availqty) * 0.0001) as ?threshold
WHERE
{
?thr_tps a tpch:partsupp .
?thr_ps+>tpch:has_supplier+>tpch:has_nation tpch:name "GERMANY" .
}
}
{ SELECT
?bigps+>tpch:has_part as ?bpart,
SUM(?bigps+>tpch:supplycost * ?bigps+>tpch:availqty) as ?bigpsvalue
WHERE
{
?bigps a tpch:partsupp .
?bigps+>tpch:has_supplier+>tpch:has_nation tpch:name "GERMANY" .
}
}
FILTER (?bigpsvalue > ?threshold)
}
ORDER BY desc (?bigpsvalue)
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?li+>tpch:shipmode,
SUM (
bif:__or (
bif:equ (?ord+>tpch:orderpriority, "1-URGENT"),
bif:equ (?ord+>tpch:orderpriority, "2-HIGH") ) ) as ?high_line_count,
SUM (1 -
bif:__or (
bif:equ (?ord+>tpch:orderpriority, "1-URGENT"),
bif:equ (?ord+>tpch:orderpriority, "2-HIGH") ) ) as ?low_line_count
WHERE
{
?li tpch:has_order ?ord .
FILTER (?li+>tpch:shipmode in ("MAIL", "SHIP") &&
(?li+>tpch:commitdate < ?li+>tpch:receiptdate) &&
(?li+>tpch:shipdate < ?li+>tpch:commitdate) &&
(?li+>tpch:receiptdate >= "1994-01-01"^^xsd:date) &&
(?li+>tpch:receiptdate < bif:dateadd ("year", 1, "1994-01-01"^^xsd:date)) )
}
ORDER BY ?li+>tpch:shipmode
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?c_count,
COUNT(1) as ?custdist
WHERE
{
{
SELECT
?cust+>tpch:custkey,
count (?ord) as ?c_count
WHERE
{
?cust a tpch:customer .
OPTIONAL { ?cust tpch:customer_of ?ord
FILTER (!(?ord+>tpch:comment like "%special%requests%")) }
}
}
}
ORDER BY
desc (COUNT(1))
desc (?c_count)
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
(100 * SUM (
bif:equ(bif:LEFT(?part+>tpch:type, 5), "PROMO") *
?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount) ) /
SUM (?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount)) ) as ?promo_revenue
WHERE
{
?li a tpch:lineitem ; tpch:has_part ?part .
FILTER ((?li+>tpch:shipdate >= "1995-09-01"^^xsd:date) &&
(?li+>tpch:shipdate < bif:dateadd("month", 1, "1995-09-01"^^xsd:date)) )
}
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?supplier ?supplier+>tpch:name ?supplier+>tpch:address
?supplier+>tpch:phone ?total_revenue
WHERE
{
?supplier a tpch:supplier .
{
SELECT
?supplier
(SUM(?l_extendedprice * (1 - ?l_discount))) as ?total_revenue
WHERE
{
[ a tpch:lineitem ; tpch:shipdate ?l_shipdate ;
tpch:lineextendedprice ?l_extendedprice ; tpch:linediscount ?l_discount ;
tpch:has_supplier ?supplier ] .
FILTER (
?l_shipdate >= "1996-01-01"^^xsd:date and
?l_shipdate < bif:dateadd ("month", 3, "1996-01-01"^^xsd:date) )
}
}
{
SELECT max (?l2_total_revenue) as ?maxtotal
WHERE
{
{
SELECT
?supplier2
(SUM(?l2_extendedprice * (1 - ?l2_discount))) as ?l2_total_revenue
WHERE
{
[ a tpch:lineitem ; tpch:shipdate ?l2_shipdate ;
tpch:lineextendedprice ?l2_extendedprice ; tpch:linediscount ?l2_discount ;
tpch:has_supplier ?supplier2 ] .
FILTER ( ?l2_shipdate >= "1996-01-01"^^xsd:date &&
?l2_shipdate < bif:dateadd ("month", 3, "1996-01-01"^^xsd:date) )
}
}
}
}
FILTER (?total_revenue = ?maxtotal)
}
ORDER BY ?supplier
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?part+>tpch:brand,
?part+>tpch:type,
?part+>tpch:size,
(COUNT(distinct ?ps)) as ?supplier_cnt
WHERE
{
?ps tpch:has_part ?part .
OPTIONAL {
?ps tpch:comment ?badcomment . FILTER (?badcomment like "%Customer%Complaints%") }
FILTER (
(?part+>tpch:brand != "Brand#45") &&
!(?part+>tpch:type like "MEDIUM POLISHED%") &&
(?part+>tpch:size in (49, 14, 23, 45, 19, 3, 36, 9)) &&
!bound (?badcomment) )
}
ORDER BY
desc ((COUNT(distinct ?ps)))
?part+>tpch:brand
?part+>tpch:type
?part+>tpch:size
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
(SUM(?li+>tpch:lineextendedprice) / 7.0) as ?avg_yearly
WHERE
{
?li a tpch:lineitem ; tpch:has_part ?part .
?part tpch:container "MED BOX" ; tpch:brand "Brand#23" .
{
SELECT ?part, (0.2 * AVG(?li2+>tpch:linequantity)) as ?threshold
WHERE
{ ?li2 a tpch:lineitem ;
tpch:has_part ?part
}
}
FILTER (?li+>tpch:linequantity < ?threshold)
}
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT ?cust+>foaf:name ?cust ?ord ?ord+>tpch:orderdate
?ord+>tpch:ordertotalprice SUM(?li+>tpch:linequantity)
WHERE
{
?cust a tpch:customer ;
foaf:name ?c_name .
?ord a tpch:order ;
tpch:has_customer ?cust .
?li a tpch:lineitem ;
tpch:has_order ?ord .
{
SELECT ?sum_order SUM (?li2+>tpch:linequantity) as ?sum_q
WHERE
{
?li2 a tpch:lineitem ; tpch:has_order ?sum_order .
}
} .
FILTER (?sum_order = ?ord and ?sum_q > 250)
}
ORDER BY desc (?ord+>tpch:ordertotalprice) ?ord+>tpch:orderdate
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
(SUM(?li+>tpch:lineextendedprice * (1 - ?li+>tpch:linediscount))) as ?revenue
WHERE
{
?li a tpch:lineitem ; tpch:has_part ?part ; tpch:shipinstruct "DELIVER IN PERSON" .
FILTER (?li+>tpch:shipmode in ("AIR", "AIR REG") &&
( ( (?part+>tpch:brand = "Brand#12") &&
(?part+>tpch:container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
(?li+>tpch:linequantity >= 1) && (?li+>tpch:linequantity <= 1 + 10) &&
(?part+>tpch:size >= 1) && (?part+>tpch:size <= 5) ) ||
( (?part+>tpch:brand = "Brand#23") &&
(?part+>tpch:container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
(?li+>tpch:linequantity >= 10) && (?li+>tpch:linequantity <= 10 + 10) &&
(?part+>tpch:size >= 1) && (?part+>tpch:size <= 10) ) ||
( (?part+>tpch:brand = "Brand#34") &&
(?part+>tpch:container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
(?li+>tpch:linequantity >= 20) && (?li+>tpch:linequantity <= 20 + 10) &&
(?part+>tpch:size >= 1) && (?part+>tpch:size <= 15) ) ) )
}
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?supp+>tpch:name,
?supp+>tpch:address
WHERE
{
{
SELECT
?supp, count (?big_ps) as ?big_ps_cnt
WHERE
{
?big_ps a tpch:partsupp ; tpch:has_supplier ?supp .
?supp+>tpch:has_nation tpch:name "CANADA" .
{
SELECT ?forest_part
WHERE
{
?forest_part a tpch:part .
FILTER ( ?forest_part+>tpch:name like "forest%" )
}
}
{
SELECT
?big_ps, (0.5 * SUM(?li+>tpch:linequantity)) as ?qty_threshold
WHERE
{
?li a tpch:lineitem ;
tpch:has_part ?big_ps+>tpch:has_part ;
tpch:has_supplier ?bigps+>tpch:has_supplier .
FILTER ((?li+>tpch:shipdate >= "1994-01-01"^^xsd:date) &&
(?li+>tpch:shipdate < bif:dateadd ("year", 1, "1994-01-01"^^xsd:date)) )
}
}
FILTER (?big_ps+>tpch:availqty > ?qty_threshold)
}
}
}
ORDER BY ?supp+>tpch:name
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
?supp+>tpch:name,
(COUNT(1)) as ?numwait
WHERE
{
{
SELECT ?l1 ?ord ?supp (COUNT(1)) as ?l2_cnt
WHERE
{
?supp a tpch:supplier .
?supp+>tpch:has_nation tpch:name "SAUDI ARABIA" .
?l1 a tpch:lineitem ;
tpch:has_supplier ?supp ;
tpch:has_order ?ord .
?ord tpch:orderstatus "F" .
?l2 a tpch:lineitem ; tpch:has_supplier ?supp2 ; tpch:has_order ?ord .
OPTIONAL {
{
SELECT ?l1 (count (1)) as ?l3_cnt
WHERE
{
?l1 a tpch:lineitem ;
tpch:has_supplier ?supp ;
tpch:has_order ?ord .
?l3 a tpch:lineitem ;
tpch:has_supplier ?supp3 ;
tpch:has_order ?ord .
FILTER ((?l3+>tpch:receiptdate > ?l3+>tpch:commitdate) &&
(?supp3 != ?supp))
}
}
}
FILTER ((?l1+>tpch:receiptdate > ?l1+>tpch:commitdate) &&
(?supp2 != ?supp) && !bound (?l3_cnt))
}
}
}
ORDER BY
desc (COUNT(1))
?supp+>tpch:name
DEFINE sql:signal-void-variables 1
PREFIX tpch: <http://www.openlinksw.com/schemas/tpch#>
PREFIX oplsioc: <http://www.openlinksw.com/schemas/oplsioc#>
PREFIX sioc: <http://rdfs.org/sioc/ns#>
PREFIX foaf: <http://xmlns.com/foaf/0.1/>
SELECT
(bif:LEFT (?cust+>tpch:phone, 2)) as ?cntrycode,
(count (1)) as ?numcust,
SUM (?cust+>tpch:acctbal) as ?totacctbal
WHERE
{
{
SELECT
(avg (?cust2+>tpch:acctbal)) as ?acctbal_threshold
WHERE
{
?cust2 a tpch:customer .
FILTER ((?cust2+>tpch:acctbal > 0.00) &&
bif:LEFT (?cust2+>tpch:phone, 2) in
("13", "35", "31", "23", "29", "30", "17", "18") )
}
}
?cust a tpch:customer .
OPTIONAL {
SELECT ?cust (COUNT(?ord)) as ?ord_cnt
WHERE
{
?cust a tpch:customer ;
tpch:customer_of ?ord
}
}
FILTER ((?cust+>tpch:acctbal > ?acctbal_threshold) &&
bif:LEFT (?cust+>tpch:phone, 2) in
("13", "35", "31", "23", "29", "30", "17", "18") &&
!bound (?ord_cnt) )
}
ORDER BY (bif:LEFT (?cust+>tpch:phone, 2))