Optimizing RDF Data Cubes for Efficient Processing of Analytical Queries

Kim Ahlstrøm Jakobsen, Alex B. Andersen, Katja Hose, Torben Bach Pedersen

In: Proceedings of the 6th International Workshop on Consuming Linked Data (COLD 2015) co-located with the 14th International Semantic Web Conference (ISWC 2015)

PDF BibTeX


Abstract

In today's data-driven world, analytical querying, typically based on the data cube concept, is the cornerstone of answering important business questions and making data-driven decisions. Traditionally, the underlying analytical data was mostly internal to the organization and stored in relational data ware houses and data cubes. Today, external data sources are essential for analytics and, as the Semantic Web gains popularity, more and more external sources are available in native RDF. With the recent SPARQL 1.1 standard, performing analytical queries over RDF data sources has finally become feasible. However, unlike their relational counterparts, RDF data cubes stores lack optimizations that enable fast querying. In this paper, we present an approach to optimizing RDF data cubes that is based on three novel cube patterns that optimize RDF data cubes, as well as associated algorithms that transform the RDF data cube. An extensive experimental evaluation shows that the approach allows trading additional storage and/or load times in return for significantly increased query performance. We further provide guidelines for which patterns to apply for specific scenarios and systems.

Experiments

Repositories

The implementation and the code for running the experiments are available at the following two GitHub projects.

SWOD Implementation

This program generates a series of SPARQL construct queries that create the snowflake pattern and fully denormalized pattern cubes.

This Java program uses Apache Maven to manage dependencies.

The SWOD Tools project contains generated SPARQL queries, thus it is not necessary to run the SWOD program in order to run the experiments.

SWOD Tools

These tools will allow you to generate the TPC-H data in triples (generate.sh), load the data into Virtuoso and Apache Jena (load.sh), run the TPC-H queries on the triple stores (query.sh), and analyse the results by comparing the queries (extractQueryTimes.py, compareResults.py).

All scripts are written in bash and Python, which might result in some problem on windows systems.

The batch scripts take a series of "sources" as input, these modular configurations files are located in the "source" folder. Be aware the these configuration files need to be set up manually before running any of the programs.

The Python scripts have a help flag (--help) that displays the allowed parameters.

Workflow

  1. Download and install the following program

  2. Create configuration files (source files) that match your system (source/machine/) and wanted configuration (scale factor etc.)

  3. Generate or download the dataset

    • Generation requires Virtuoso for running the construct queries

  4. Install Virtuoso or Apache Jena

  5. Load the data into the Jena TDB or Virtuoso by using the appropriate configuration files

  6. Change the querymix configuration (source/) to match which queries you want to execute, run the querymix.sh program to propagate these settings.

  7. Run the query.sh script with the appropriate configuration files to start the experiments

  8. Use the extractQueryTimes.py on the generated logfiles (logs/) to extract and aggregate the query times.

  9. The experiments can now be compare using the compareResults.py script

Feel free to post bug report and ask questions.

TPC-H relational diagram

The TPC-H relational diagram

Dataset

Scale 0.1 Scale 0.2 Scale 0.3 Scale 0.5
Snowflake Download Download Download Download
Star Download Download Download Download
Denormalized Download Download Download Download

Queries

Virtuoso queries Snowflake pattern

Prefix


prefix xsd: <http://www.w3.org/2001/XMLSchema#> 
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
          

Query 1


select
  ?l_returnflag 
  ?l_linestatus 
  (sum(xsd:decimal(?l_linequantity)) as ?sum_qty) 
  (ROUND(sum(xsd:decimal(?l_lineextendedprice))*100)/100 as ?sum_base_price)
  (ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount)))*100)/100 as ?sum_disc_price)
  (ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax)))*100)/100 as ?sum_charge)
  (ROUND(avg(xsd:decimal(?l_linequantity))*100)/100 as ?avg_qty)
  (ROUND(avg(xsd:decimal(?l_lineextendedprice))*100)/100 as ?avg_price)
  (ROUND(avg(xsd:decimal(?l_linediscount))*100)/100 as ?avg_disc)  
  (count(1) as ?count_order)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_returnflag ?l_returnflag ;
       ltpch:l_linestatus ?l_linestatus ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linetax ?l_linetax ;
       ltpch:l_shipdate ?l_shipdate ;
       ltpch:l_linediscount ?l_linediscount .
   filter (xsd:boolean(xsd:dateTime(?l_shipdate) <= xsd:dateTime(bif:dateadd ("day", -%DELTA%, "1998-12-01"^^xsd:date))))
} 
group by
  ?l_returnflag
  ?l_linestatus
order by
  ?l_returnflag
  ?l_linestatus


			

Query 2


select
  ?s_acctbal,
  ?s_name,
  ?nation_name,
  ?p_partkey,
  ?p_mfgr,
  ?s_address,
  ?s_phone,
  ?s_comment
where {
  ?ps a ltpch:partsupp;
      ltpch:ps_has_supplier ?supp;
      ltpch:ps_has_part ?part ;
      ltpch:ps_supplycost ?minsc .
  ?supp a ltpch:supplier ;
     ltpch:s_acctbal ?s_acctbal ;
   ltpch:s_name ?s_name ;
     ltpch:s_has_nation ?s_has_nation ;
     ltpch:s_address ?s_address ;
     ltpch:s_phone ?s_phone ;
     ltpch:s_comment ?s_comment .
  ?s_has_nation ltpch:n_name ?nation_name ;
     ltpch:n_has_region ?s_has_region .
  ?s_has_region ltpch:r_name "%REGION%" .
  ?part a ltpch:part ;
      ltpch:p_partkey ?p_partkey ;
      ltpch:p_mfgr ?p_mfgr ;
      ltpch:p_size "%SIZE%" ;
      ltpch:p_type ?p_type .
  { select ?part min(?s_cost) as ?minsc
    where {
        ?ps a ltpch:partsupp;
            ltpch:ps_has_part ?part;
            ltpch:ps_has_supplier ?ms;
            ltpch:ps_supplycost ?s_cost .
        ?ms ltpch:s_has_nation ?m_has_nation .
        ?m_has_nation ltpch:n_has_region ?m_has_region .
        ?m_has_region ltpch:r_name "%REGION%" .
      } 
    }
    filter (?p_type like "%%TYPE%") 
  }
order by
  desc (?s_acctbal)
  ?nation_name
  ?s_name
  ?p_partkey
limit 100


			

Query 3


select
  ?o_orderkey
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?o_orderdate
  ?o_shippriority
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
    ltpch:l_lineextendedprice ?l_lineextendedprice ;
    ltpch:l_linediscount ?l_linediscount ;
    ltpch:l_has_order ?ord ;
    ltpch:l_shipdate ?l_shipdate .
  ?ord ltpch:o_orderdate ?o_orderdate ;
    ltpch:o_shippriority ?o_shippriority ;
    ltpch:o_orderkey ?o_orderkey ;
    ltpch:o_has_customer ?cust .
  ?cust ltpch:c_mktsegment ?c_mktsegment .
  filter ((xsd:dateTime(?o_orderdate) < xsd:dateTime("%DATE%"^^xsd:date)) &&
    (xsd:dateTime(?l_shipdate) > xsd:dateTime("%DATE%"^^xsd:date)) &&
    (?c_mktsegment = "%SEGMENT%") ) 
}
group by
  ?o_orderkey
  ?o_orderdate
  ?o_shippriority
order by
  desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
  ?o_orderdate
limit 10

			

Query 4


select
  ?o_orderpriority
  (count(*) as ?order_count)
where  
{
  {
    select distinct
      ?o_orderpriority
      ?ord
    where 
    {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_has_order ?ord ;
          ltpch:l_commitdate ?l_commitdate ;
          ltpch:l_receiptdate ?l_receiptdate .
      ?ord ltpch:o_orderpriority ?o_orderpriority ;
           ltpch:o_orderdate ?o_orderdate .
      filter (
        (xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
        (xsd:boolean(xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date))) &&
        (xsd:boolean(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date))))
      )
    }
  }
}
group by
  ?o_orderpriority
order by
  ?o_orderpriority


			

Query 5


select
  ?nation
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where  {
   ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_has_order ?ord ;
       ltpch:l_has_partsupplier ?ps ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount .
    ?ord ltpch:o_has_customer ?cust ;
         ltpch:o_orderdate ?o_orderdate .
    ?ps ltpch:ps_has_supplier ?supp .
    ?supp ltpch:s_has_nation ?s_nation .
    ?s_nation ltpch:n_has_region ?s_region ;
              ltpch:n_name ?nation .
    ?s_region ltpch:r_name ?r_name .
    ?cust ltpch:c_has_nation ?c_nation.
    filter ((?c_nation = ?s_nation) &&
      (xsd:dateTime(?o_orderdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
      (xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("year", 1,"%YEAR%-01-01" ^^xsd:date))) &&
      (?r_name = "%REGION%") ) 
  }
group by
  ?nation
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))

			

Query 6


 
select
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_shipdate ?l_shipdate .
    filter ( (xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
      (xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date))) &&
      (xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
      (xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
      (xsd:decimal(?l_linequantity) < %QUANTITY%) ) 
}


			

Query 7


select 
  ?supp_nation 
  ?cust_nation 
  ?li_year
  (sum (?volume) as ?revenue)
where {
  {
    select
      ?supp_nation
      ?cust_nation
      ?li_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_has_order ?ord ;
        ltpch:l_has_partsupplier ?ps ;
        ltpch:l_shipdate ?l_shipdate ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount .
      ?ord ltpch:o_has_customer ?cust .
      ?cust ltpch:c_has_nation ?custn .
      ?custn ltpch:n_name ?cust_nation .
      ?ps ltpch:ps_has_supplier ?supp .
      ?supp ltpch:s_has_nation ?suppn .
      ?suppn ltpch:n_name ?supp_nation .
      BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
      filter ((
        (?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
        (?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
        (xsd:dateTime(?l_shipdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
        (xsd:dateTime(?l_shipdate) <= xsd:dateTime("1996-12-31"^^xsd:date)) ) 
      } 
   } 
}
group by
  ?supp_nation
  ?cust_nation
  ?li_year
order by
  ?supp_nation
  ?cust_nation
  ?li_year

			

Query 8


select
  ?o_year
  ((?sum1 / ?sum2) as ?mkt_share)
where {
  { select
    ?o_year
    (sum (?volume * bif:equ (?nation, "%NATION%")) as ?sum1)
    (sum (?volume) as ?sum2)
    where {
      { select
           ((YEAR (xsd:dateTime(?o_orderdate))) as ?o_year)
           ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
           ?nation
         where {
           ?li qb:dataSet ltpch:lineitemCube ;
               ltpch:l_has_partsupplier ?ps ;
               ltpch:l_has_order ?ord ;
               ltpch:l_has_partsupplier ?ps ;
               ltpch:l_lineextendedprice ?l_lineextendedprice ;
               ltpch:l_linediscount ?l_linediscount .
           ?ps ltpch:ps_has_supplier ?s_supplier .
           ?s_supplier ltpch:s_has_nation ?n2 .
           ?n2 ltpch:n_name ?nation .
           ?ps ltpch:ps_has_part ?part .
           ?part ltpch:p_type ?type .
           ?ord ltpch:o_orderdate ?o_orderdate ;
             ltpch:o_has_customer ?c_customer .
           ?c_customer ltpch:c_has_nation ?n_nation .
           ?n_nation ltpch:n_has_region ?r_region .
           ?r_region ltpch:r_name ?region.
           filter ((xsd:dateTime(?o_orderdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
             (xsd:dateTime(?o_orderdate) <= xsd:dateTime("1996-12-31"^^xsd:date) &&
              ?region = "%REGION%" &&
              ?type = "%TYPE%") 
           ) 
        } 
      } 
    }
    group by
      ?o_year 
  } 
}
order by
  ?o_year


			

Query 9


select
  ?nation
  ?o_year
  (sum(?amount) as ?sum_profit)
where {
  { select
      ?nation
      ?o_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_has_order ?ord ;
          ltpch:l_has_partsupplier ?ps ;
          ltpch:l_linequantity ?l_linequantity ;
          ltpch:l_lineextendedprice ?l_lineextendedprice ;
          ltpch:l_linediscount ?l_linediscount .
      ?ps ltpch:ps_has_part ?part ;
          ltpch:ps_has_supplier ?supp .
      ?supp ltpch:s_has_nation ?s_nation .
      ?s_nation ltpch:n_name ?nation .
      ?ord ltpch:o_orderdate ?o_orderdate .
      ?ps ltpch:ps_supplycost ?ps_supplycost .
      ?part ltpch:p_name ?p_name .
      filter (REGEX (?p_name, "%COLOR%"))
      BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
    } 
  } 
}
group by
  ?nation
  ?o_year
order by
  ?nation
  desc (?o_year)

			

Query 10


select
  ?c_custkey
  ?c_companyName
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
      ltpch:l_returnflag ?l_returnflag ;
      ltpch:l_has_order ?ord ;
      ltpch:l_lineextendedprice ?l_lineextendedprice ;
      ltpch:l_linediscount ?l_linediscount .
  ?ord ltpch:o_has_customer ?cust ;
       ltpch:o_orderdate ?o_orderdate .
  ?cust ltpch:c_address ?c_address ;
      ltpch:c_phone ?c_phone ;
      ltpch:c_comment ?c_comment ;
      ltpch:c_acctbal ?c_acctbal ;
      ltpch:c_custkey ?c_custkey ;
      ltpch:c_has_nation ?c_nation ;
      ltpch:c_name ?c_companyName .
   ?c_nation ltpch:n_name ?nation .
   filter ((xsd:boolean(xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date))) &&
      (xsd:boolean(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)))) &&
      (xsd:boolean(?l_returnflag = "R")) 
   ) 
}
group by
  ?c_custkey
  ?c_companyName
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20

			

Query 11


select
  ?bigpspart,
  ?bigpsvalue
where {
      { select
          ?bigpspart,
          sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue
        where
          {
            ?bigps a ltpch:partsupp ;
                   ltpch:ps_has_part ?bigpspart ;
                   ltpch:ps_supplycost ?b_supplycost ;
                   ltpch:ps_availqty ?b_availqty ;
                   ltpch:ps_has_supplier ?b_supplier .
            ?b_supplier ltpch:s_has_nation ?b_nation .
            ?b_nation ltpch:n_name "%NATION%" .
          }
      }
    filter (?bigpsvalue > (
      select
        (sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty) * %FRACTION%)) as ?threshold
      where
        {
          ?thr_ps a ltpch:partsupp ;
                  ltpch:ps_has_part ?t_part ;
                  ltpch:ps_supplycost ?t_supplycost ;
                  ltpch:ps_availqty ?t_availqty ;
                  ltpch:ps_has_supplier ?t_supplier .
          ?t_supplier ltpch:s_has_nation ?t_nation .
          ?t_nation ltpch:n_name "%NATION%" .
        }
      )
    )
  }
order by
  desc (?bigpsvalue)

          

Query 12


select
  ?l_shipmode
  (sum (
    bif:__or (
      bif:equ (?o_orderpriority, "1-URGENT"),
      bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
  (sum (1 -
    bif:__or (
      bif:equ (?o_orderpriority, "1-URGENT"),
      bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where  {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_has_order ?ord ;
       ltpch:l_commitdate ?l_commitdate ;
       ltpch:l_receiptdate ?l_receiptdate ;
       ltpch:l_shipmode ?l_shipmode ;
       ltpch:l_shipdate ?l_shipdate .
    ?ord ltpch:o_orderpriority ?o_orderpriority .
    filter (xsd:boolean(?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%")) &&
      (xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
      (xsd:boolean(xsd:dateTime(?l_shipdate) < xsd:dateTime(?l_commitdate))) &&
      (xsd:boolean(xsd:dateTime(?l_receiptdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date))) &&
      (xsd:boolean(xsd:dateTime(?l_receiptdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))) )
  }
group by
  ?l_shipmode
order by
  ?l_shipmode

          

Query 13


select
  ?c_count
  (count(1) as ?custdist)
where {
    { select
        ?c_custkey
        (count (?ord) as ?c_count)
      where
        {
          ?cust ltpch:c_custkey ?c_custkey .
           optional {
             ?ord a ltpch:orders ;
                  ltpch:o_has_customer ?cust ;
                  ltpch:o_comment ?o_comment .
              filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) . 
          }
        }
      group by 
        ?c_custkey
    }
  }
group by
  ?c_count
order by
  desc (count(1))
  desc (?c_count)

          

Query 14


select
       (100 * sum(bif:equ(bif:LEFT(?p_type, 5), "PROMO") * xsd:decimal(?l_lineextendedprice) *  (xsd:decimal(1) - xsd:decimal(?l_linediscount)))  / sum(xsd:decimal(?l_lineextendedprice) *  (xsd:decimal(1) - xsd:decimal(?l_linediscount)))) as ?promo_revenue
where
{

    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount ;
        ltpch:l_shipdate ?l_shipdate ;
        ltpch:l_has_partsupplier ?ps .
    ?ps ltpch:ps_has_part ?part .
    ?part ltpch:p_type ?p_type .
    filter (xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
      (xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd("month", 1, "%MONTH%-01"^^xsd:date))) )
}

          

Query 15


select
  ?s_suppkey
  ?s_name
  ?s_address
  ?s_phone
  ?total_revenue
where  {
    ?supplier a ltpch:supplier ;
        ltpch:s_suppkey ?s_suppkey ;
        ltpch:s_name ?s_name ;
        ltpch:s_address ?s_address ;
        ltpch:s_phone ?s_phone .
    { select
          ?supplier
          (sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount))) as ?total_revenue)
       where {
            ?li1 qb:dataSet ltpch:lineitemCube ;
                 ltpch:l_shipdate ?l_shipdate ;
                 ltpch:l_lineextendedprice ?l_extendedprice ;
                 ltpch:l_linediscount ?l_discount ;
                 ltpch:l_has_partsupplier ?ps1 .
            ?ps1 ltpch:ps_has_supplier ?supplier .
            filter (
                xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
                xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
        }
      group by
        ?supplier
      }
      { select (max (?l2_total_revenue) as ?maxtotal)
        where {
            { select
                  ?supplier2
                  (sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount))) as ?l2_total_revenue)
               where {
                    ?li2 qb:dataSet ltpch:lineitemCube ;
                      ltpch:l_shipdate ?l2_shipdate ;
                      ltpch:l_lineextendedprice ?l2_extendedprice ;
                      ltpch:l_linediscount ?l2_discount ;
                       ltpch:l_has_partsupplier ?ps2 .
                  ?ps2 ltpch:ps_has_supplier ?supplier2 .
                    filter (
                        xsd:dateTime(?l2_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
                        xsd:dateTime(?l2_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
               }
               group by 
                ?supplier2
            }
        }
    }
    filter (?total_revenue = ?maxtotal)
}
order by
  ?supplier

          

Query 16


select
  ?p_brand,
  ?p_type,
  ?p_size,
  (count(distinct ?supp)) as ?supplier_cnt
where {
    ?ps a ltpch:partsupp ;
        ltpch:ps_has_part ?part ;
        ltpch:ps_has_supplier ?supp .
    ?part ltpch:p_brand ?p_brand ;
        ltpch:p_type ?p_type ;
        ltpch:p_size ?p_size .    
    filter (
      (?p_brand != "%BRAND%") &&
      !(?p_type like "%TYPE%%") &&
      (xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
    )
    filter NOT EXISTS {
       ?supp a ltpch:supplier;
             ltpch:s_comment ?badcomment .
       filter (?badcomment like "%Customer%Complaints%") 
    }
  }
group by
  ?p_brand
  ?p_type
  ?p_size
order by
  desc ((count(distinct ?supp)))
  ?p_brand
  ?p_type
  ?p_size

          

Query 17


select
  ((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_has_partsupplier ?ps .
    ?ps ltpch:ps_has_part ?part .
    ?part ltpch:p_brand ?p_brand ;
          ltpch:p_container ?p_container .
          {
            select 
              ?part
              ((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
            where { 
              ?li2  a ltpch:lineitem ;
                    ltpch:l_linequantity ?l2_linequantity ; 
                    ltpch:l_has_partsupplier ?ps2 .
              ?ps2  ltpch:ps_has_part ?part .
          } 
          group by
            ?part
        }
    filter (xsd:decimal(?l_linequantity) < ?threshold && REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%") 
}

          

Query 18


select
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
   (sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
    ?li qb:dataSet ltpch:lineitemCube  ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_has_order ?ord .
    ?ord ltpch:o_orderkey ?o_orderkey ;
         ltpch:o_orderdate ?o_orderdate ;
         ltpch:o_ordertotalprice ?o_ordertotalprice ;
         ltpch:o_has_customer ?cust .
    ?cust ltpch:c_custkey ?c_custkey ;
          ltpch:c_name ?c_name .  
    { select 
         ?sum_order 
         (sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
       where {
           ?li2 qb:dataSet ltpch:lineitemCube ;
                ltpch:l_linequantity ?l2_linequantity ;
                ltpch:l_has_order ?sum_order .
       }
       group by ?sum_order 
    } .
    filter (?sum_order = ?ord && xsd:decimal(?sum_q) > xsd:decimal(%QUANTITY%))
}
group by
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
order by
  desc (?o_ordertotalprice)
  ?o_orderdate
limit 100

          

Query 19


select
  ((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_shipmode ?l_shipmode ;
        ltpch:l_shipinstruct ?l_shipinstruct ;
        ltpch:l_has_partsupplier ?ps .
    ?ps ltpch:ps_has_part ?part .
     ?part ltpch:p_brand ?p_brand ;
          ltpch:p_size ?p_size ;
          ltpch:p_container ?p_container .
     
     filter (?l_shipmode in ("AIR", "AIR REG") &&
      ?l_shipinstruct = "DELIVER IN PERSON" &&
      ( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
          (?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
          (xsd:integer(?l_linequantity) <= %QUANTITY1% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
        ( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
          (?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY2%) && 
          (xsd:integer(?l_linequantity) <= %QUANTITY2% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
        ( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
          (?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
          (xsd:integer(?l_linequantity) <= %QUANTITY3% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
  }

          

Query 20


select
  ?s_name
  ?s_address
where
{
  ?supp ltpch:s_name ?s_name ;
        ltpch:s_address ?s_address .
  { 
    select distinct 
      ?supp 
    where 
    {
      ?big_ps ltpch:ps_has_part ?part ;
              ltpch:ps_availqty ?big_ps_availqty ;
              ltpch:ps_has_supplier ?supp .
      ?supp ltpch:s_has_nation ?s_nation .
      ?s_nation ltpch:n_name ?n_name .
      ?part ltpch:p_name ?p_name . 
      filter (REGEX (?p_name , "^%COLOR%") && 
              ?n_name = "%NATION%" && 
              xsd:decimal(?big_ps_availqty) > ?qty_threshold)
      {
        select 
          ((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
          ?big_ps
        where
        {
          ?li qb:dataSet ltpch:lineitemCube ;
              ltpch:l_shipdate ?l_shipdate ;
              ltpch:l_linequantity ?l_linequantity ;
              ltpch:l_has_partsupplier ?big_ps .
          filter ((xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
            (xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))
          )
        }
        group by 
          ?big_ps
      }
    } 
  }
}
order by ?s_name

          

Query 21


select
    ?s_name
    (count(1) as ?numwait)
where {
          ?li1 qb:dataSet ltpch:lineitemCube;
              ltpch:l_receiptdate ?l1_receiptdate ;
              ltpch:l_commitdate ?l1_commitdate ;
              ltpch:l_has_partsupplier ?ps ;
              ltpch:l_has_order ?ord .
          ?ps ltpch:ps_has_supplier ?supp .
          ?supp ltpch:s_name ?s_name ;
               ltpch:s_has_nation ?s_nation .
          ?ord ltpch:o_orderstatus ?orderstatus .
          ?s_nation ltpch:n_name ?name
          filter ( 
            xsd:boolean(xsd:dateTime(?l1_receiptdate) > xsd:dateTime(?l1_commitdate)) && 
            ?name = "%NATION%" && 
            ?orderstatus = "F"
            ) 
          filter exists {
            ?li2 ltpch:l_has_order ?ord ;
                 ltpch:l_has_partsupplier ?ps2 .
            ?ps2 ltpch:ps_has_supplier ?supp2 .
            filter (?supp != ?supp2)
          }
          filter not exists {
              ?li3 ltpch:l_has_order ?ord ;
                   ltpch:l_receiptdate ?l3_receiptdate ;
                   ltpch:l_commitdate ?l3_commitdate ;
                   ltpch:l_has_partsupplier ?ps3 .
              ?ps3 ltpch:ps_has_supplier ?supp3 .
              filter (
                 xsd:boolean(xsd:dateTime(?l3_receiptdate) > xsd:dateTime(?l3_commitdate)) &&
                 ?supp3 != ?supp
              )
         }
       }
group by
   ?s_name
order by
    desc (count(1))
    ?s_name
limit 100

          

Query 22


select
  (bif:LEFT (?c_phone, 2)) as ?cntrycode,
  (count (1)) as ?numcust,
  sum (xsd:decimal(?c_acctbal)) as ?totacctbal
where {
    ?cust a ltpch:customer ;
      ltpch:c_acctbal ?c_acctbal ;
      ltpch:c_phone ?c_phone .
      {
        select (avg (xsd:decimal(?c_acctbal2))) as ?acctbal_threshold
          where
            {
              ?cust2 a ltpch:customer ;
                 ltpch:c_acctbal ?c_acctbal2 ;
                 ltpch:c_phone ?c_phone2 .
              filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
                bif:LEFT (?c_phone2, 2) in (%COUNTRY_CODE_SET%) )
            }
      }
    filter (
      bif:LEFT (?c_phone, 2) in (%COUNTRY_CODE_SET%) &&
      (xsd:decimal(?c_acctbal) > ?acctbal_threshold )
    ) 
    filter not exists { ?ord ltpch:o_has_customer ?cust }
  }
group by (bif:LEFT (?c_phone, 2))
order by (bif:LEFT (?c_phone, 2))

          
Virtuoso queries star pattern

Prefix


prefix xsd: <http://www.w3.org/2001/XMLSchema#> 
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
          

Query 1


select
  ?l_returnflag 
  ?l_linestatus 
  (sum(xsd:decimal(?l_linequantity)) as ?sum_qty) 
  (ROUND(sum(xsd:decimal(?l_lineextendedprice))*100)/100 as ?sum_base_price)
  (ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount)))*100)/100 as ?sum_disc_price)
  (ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax)))*100)/100 as ?sum_charge)
  (ROUND(avg(xsd:decimal(?l_linequantity))*100)/100 as ?avg_qty)
  (ROUND(avg(xsd:decimal(?l_lineextendedprice))*100)/100 as ?avg_price)
  (ROUND(avg(xsd:decimal(?l_linediscount))*100)/100 as ?avg_disc)  
  (count(1) as ?count_order)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_returnflag ?l_returnflag ;
       ltpch:l_linestatus ?l_linestatus ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linetax ?l_linetax ;
       ltpch:l_shipdate ?l_shipdate ;
       ltpch:l_linediscount ?l_linediscount .
   filter (xsd:dateTime(?l_shipdate) <= xsd:dateTime(bif:dateadd ("day", -%DELTA%, "1998-12-01"^^xsd:date)))
} 
group by
  ?l_returnflag
  ?l_linestatus
order by
  ?l_returnflag
  ?l_linestatus

            

Query 2


select
  ?s_acctbal,
  ?s_name,
  ?nation_name,
  ?p_partkey,
  ?p_mfgr,
  ?s_address,
  ?s_phone,
  ?s_comment
where {
  ?ps ltpch:supplier_acctbal ?s_acctbal ;
    ltpch:supplier_name ?s_name ;
    ltpch:partsupplier_supplycost ?minsc ;    
    ltpch:supplier_address ?s_address ;
    ltpch:supplier_phone ?s_phone ;
    ltpch:supplier_comment ?s_comment ;
    ltpch:nation_name ?nation_name ;
    ltpch:region_name "%REGION%" ;
    ltpch:part_partkey ?p_partkey ;
    ltpch:part_mfgr ?p_mfgr ;
    ltpch:part_size ?size ;
    ltpch:part_type ?p_type .
  FILTER (?size = str(%SIZE%) && contains(?p_type, "%TYPE%"))
  { select ?p_partkey  min(?s_cost) as ?minsc
    where {
        ?ps ltpch:part_partkey ?p_partkey;
            ltpch:partsupplier_supplycost ?s_cost ;
            ltpch:region_name ?region2 .
            filter (?region2 = "%REGION%")
      } 
    }
     
  }
order by
  desc (?s_acctbal)
  ?nation_name
  ?s_name
  ?p_partkey
limit 100


            

Query 3


select
  ?o_orderkey
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?o_orderdate
  ?o_shippriority
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
    ltpch:l_lineextendedprice ?l_lineextendedprice ;
    ltpch:l_linediscount ?l_linediscount ;
    ltpch:l_has_order ?ord ;
    ltpch:l_shipdate ?l_shipdate .
  ?ord ltpch:order_orderdate ?o_orderdate ;
    ltpch:order_shippriority ?o_shippriority ;
    ltpch:order_orderkey ?o_orderkey ;
    ltpch:customer_mktsegment ?c_mktsegment .
  filter ((xsd:dateTime(?o_orderdate) < xsd:dateTime("%DATE%"^^xsd:date)) &&
    (xsd:dateTime(?l_shipdate) > xsd:dateTime("%DATE%"^^xsd:date)) &&
    (?c_mktsegment = "%SEGMENT%") ) 
}
group by
  ?o_orderkey
  ?o_orderdate
  ?o_shippriority
order by
  desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
  ?o_orderdate
limit 10
            

Query 4


select
  ?o_orderpriority
  (count(*) as ?order_count)
where  
{
  {
    select distinct
      ?o_orderpriority
      ?ord
    where 
    {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_has_order ?ord ;
          ltpch:l_commitdate ?l_commitdate ;
          ltpch:l_receiptdate ?l_receiptdate .
      ?ord ltpch:order_orderpriority ?o_orderpriority ;
           ltpch:order_orderdate ?o_orderdate .
      filter (
        (xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
        (xsd:boolean(xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date))) &&
        (xsd:boolean(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date))))
      )
    }
  }
}
group by
  ?o_orderpriority
order by
  ?o_orderpriority

            

Query 5


select
  ?nation
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where  {
   ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_has_order ?ord ;
       ltpch:l_has_partsupplier ?ps ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount .
    ?ord ltpch:order_orderdate ?o_orderdate ;
         ltpch:nation_name ?c_nation .
    ?ps ltpch:nation_name ?nation ;
        ltpch:region_name ?r_name .
    
    filter ((?c_nation = ?nation) &&
      (xsd:dateTime(?o_orderdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
      (xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("year", 1,"%YEAR%-01-01" ^^xsd:date))) &&
      (?r_name = "%REGION%") ) 
  }
group by
  ?nation
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))

            

Query 6


select
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_shipdate ?l_shipdate .
    filter ( (xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
      (xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date))) &&
      (xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
      (xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
      (xsd:decimal(?l_linequantity) < %QUANTITY%) ) 
}

            

Query 7


select 
  ?supp_nation 
  ?cust_nation 
  ?li_year
  (sum (xsd:decimal(?volume)) as ?revenue)
where {
  {
    select
      ?supp_nation
      ?cust_nation
      ?li_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_has_order ?ord ;
        ltpch:l_has_partsupplier ?ps ;
        ltpch:l_shipdate ?l_shipdate ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount .
      ?ord ltpch:nation_name ?cust_nation .
      ?ps ltpch:nation_name ?supp_nation .
      BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
      filter ((
        (?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
        (?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
        (xsd:dateTime(?l_shipdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
        (xsd:dateTime(?l_shipdate) <= xsd:dateTime("1996-12-31"^^xsd:date)) ) 
      } 
   } 
}
group by
  ?supp_nation
  ?cust_nation
  ?li_year
order by
  ?supp_nation
  ?cust_nation
  ?li_year
            

Query 8


select
  ?o_year
  ((?sum1 / ?sum2) as ?mkt_share)
where {
  { select
    ?o_year
    (sum (?volume * bif:equ (?nation, "%NATION%")) as ?sum1)
    (sum (?volume) as ?sum2)
    where {
      { select
           ((YEAR(xsd:dateTime(?o_orderdate))) as ?o_year)
           ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
           ?nation
         where {
           ?li qb:dataSet ltpch:lineitemCube ;
               ltpch:l_has_partsupplier ?ps ;
               ltpch:l_has_order ?ord ;
               ltpch:l_has_partsupplier ?ps ;
               ltpch:l_lineextendedprice ?l_lineextendedprice ;
               ltpch:l_linediscount ?l_linediscount .
           ?ps ltpch:nation_name ?nation ;
               ltpch:part_type ?type .
           ?ord ltpch:order_orderdate ?o_orderdate ;
                ltpch:region_name ?region .
           filter ((xsd:dateTime(?o_orderdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
             (xsd:dateTime(?o_orderdate) <= xsd:dateTime("1996-12-31"^^xsd:date) &&
              ?region = "%REGION%" &&
              ?type = "%TYPE%") 
           ) 
        } 
      } 
    }
    group by
      ?o_year 
  } 
}
order by
  ?o_year

            

Query 9


select
  ?nation
  ?o_year
  (sum(?amount) as ?sum_profit)
where {
  { select
      ?nation
      ?o_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_has_order ?ord ;
          ltpch:l_has_partsupplier ?ps ;
          ltpch:l_linequantity ?l_linequantity ;
          ltpch:l_lineextendedprice ?l_lineextendedprice ;
          ltpch:l_linediscount ?l_linediscount .
      ?ps ltpch:nation_name ?nation ;
          ltpch:partsupplier_supplycost ?ps_supplycost ;
          ltpch:part_name ?p_name .
      ?ord ltpch:order_orderdate ?o_orderdate .
      filter (REGEX (?p_name, "%COLOR%"))
      BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
    } 
  } 
}
group by
  ?nation
  ?o_year
order by
  ?nation
  desc (?o_year)

            

Query 10


select
  ?c_custkey
  ?c_companyName
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
      ltpch:l_returnflag ?l_returnflag ;
      ltpch:l_has_order ?ord ;
      ltpch:l_lineextendedprice ?l_lineextendedprice ;
      ltpch:l_linediscount ?l_linediscount .
  ?ord ltpch:order_orderdate ?o_orderdate ;
      ltpch:customer_address ?c_address ;
      ltpch:customer_phone ?c_phone ;
      ltpch:customer_comment ?c_comment ;
      ltpch:customer_acctbal ?c_acctbal ;
      ltpch:customer_custkey ?c_custkey ;
      ltpch:customer_name ?c_companyName ;
      ltpch:nation_name ?nation .
   filter ((xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date)) &&
      (xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date))) &&
      (?l_returnflag = "R") 
   ) 
}
group by
  ?c_custkey
  ?c_companyName
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20

            

Query 11


select
  ?bigpspart,
  ?bigpsvalue
where {
      { select
          ?bigpspart,
          sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue
        where
          {
            ?bigps ltpch:part_partkey ?bigpspart ;
                  ltpch:partsupplier_supplycost ?b_supplycost ;
                  ltpch:partsupplier_availqty ?b_availqty ;
                  ltpch:nation_name "%NATION%" .
          }
      }
    filter (?bigpsvalue > (
        select
          (sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty)) * %FRACTION%) as ?threshold
        where
          {
            ?thr_ps ltpch:partsupplier_supplycost ?t_supplycost ;
                    ltpch:partsupplier_availqty ?t_availqty ;
                    ltpch:nation_name "%NATION%" .
          }
    ))
  }
order by
  desc (?bigpsvalue)
          

Query 12


select
  ?l_shipmode
  (sum (
    bif:__or (
      bif:equ (?o_orderpriority, "1-URGENT"),
      bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
  (sum (1 -
    bif:__or (
      bif:equ (?o_orderpriority, "1-URGENT"),
      bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where  {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_has_order ?ord ;
       ltpch:l_commitdate ?l_commitdate ;
       ltpch:l_receiptdate ?l_receiptdate ;
       ltpch:l_shipmode ?l_shipmode ;
       ltpch:l_shipdate ?l_shipdate .
    ?ord ltpch:order_orderpriority ?o_orderpriority .
    filter (xsd:boolean(?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%")) &&
      (xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
      (xsd:boolean(xsd:dateTime(?l_shipdate) < xsd:dateTime(?l_commitdate))) &&
      (xsd:boolean(xsd:dateTime(?l_receiptdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date))) &&
      (xsd:boolean(xsd:dateTime(?l_receiptdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))) )
  }
group by
  ?l_shipmode
order by
  ?l_shipmode

          

Query 13


select
  ?c_count
  (count(1) as ?custdist)
where {
    { select
        ?c_custkey
        (count (?o_comment) as ?c_count)
      where
        {
          ?ord ltpch:customer_custkey ?c_custkey .
           optional {
             ?ord ltpch:order_comment ?o_comment .
              filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) . 
          }
        }
      group by 
        ?c_custkey
    }
  }
group by
  ?c_count
order by
  desc (count(1))
  desc (?c_count)

          

Query 14


select
  ((100 * ?sum1 / ?sum2 ) as ?promo_revenue)
where
{
  select 
    (sum (
          bif:equ(SUBSTR(?p_type, 1, 5), "PROMO") *
          xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) ) as ?sum1)
    (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) ) as ?sum2)
  where {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_lineextendedprice ?l_lineextendedprice ;
          ltpch:l_linediscount ?l_linediscount ;
          ltpch:l_shipdate ?l_shipdate ;
          ltpch:l_has_partsupplier ?part .
      ?part ltpch:part_type ?p_type .
      filter ((xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date)) &&
        (xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd("month", 1, "%MONTH%-01"^^xsd:date))) )
  }
}
          

Query 15


select distinct
  ?s_suppkey
  ?s_name
  ?s_address
  ?s_phone
  ?total_revenue
where  
{
  ?partsupp ltpch:supplier_suppkey ?s_suppkey ;
            ltpch:supplier_name ?s_name ;
            ltpch:supplier_address ?s_address ;
            ltpch:supplier_phone ?s_phone .
  { 
    select
        ?s_suppkey
        ((sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount)))) as ?total_revenue)
    where 
    {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_shipdate ?l_shipdate ;
          ltpch:l_lineextendedprice ?l_extendedprice ;
          ltpch:l_linediscount ?l_discount ;
          ltpch:l_has_partsupplier ?ps .
      ?ps ltpch:supplier_suppkey ?s_suppkey .
      filter (
          xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
          xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
    }
    group by
      ?s_suppkey
  } .
  { 
    select 
      (max (?l2_total_revenue) as ?maxtotal)
    where 
    {
      { 
        select
          ((sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount)))) as ?l2_total_revenue)
        where 
        {
          ?li2 qb:dataSet ltpch:lineitemCube ;
              ltpch:l_shipdate ?l2_shipdate ;
              ltpch:l_lineextendedprice ?l2_extendedprice ;
              ltpch:l_linediscount ?l2_discount ;
              ltpch:l_has_partsupplier ?ps2 .
          ?ps2 ltpch:supplier_suppkey ?s_suppkey2 .
          filter (
              xsd:dateTime(?l2_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
              xsd:dateTime(?l2_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
        }
        group by
          ?s_suppkey2
      }
    }
  }
  filter (?total_revenue = ?maxtotal)
}
order by
  ?s_suppkey

          

Query 16


select
  ?p_brand,
  ?p_type,
  ?p_size,
  (count(distinct ?supp)) as ?supplier_cnt
where {
    ?ps ltpch:part_brand ?p_brand ;
        ltpch:part_type ?p_type ;
        ltpch:part_size ?p_size ;   
        ltpch:supplier_suppkey ?supp .    
    filter (
      (?p_brand != "%BRAND%") &&
      !(?p_type like "%TYPE%%") &&
      (xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
    )
    filter NOT EXISTS {
       ?ps ltpch:supplier_comment ?badcomment .
       filter (?badcomment like "%Customer%Complaints%") 
    }
  }
group by
  ?p_brand
  ?p_type
  ?p_size
order by
  desc ((count(distinct ?supp)))
  ?p_brand
  ?p_type
  ?p_size

          

Query 17


select
  ((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_has_partsupplier ?ps .
    ?ps ltpch:part_partkey ?p_partkey.
    {
      select 
        ?p_partkey
        ((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
      where { 
        ?li2  a ltpch:lineitem ;
              ltpch:l_linequantity ?l2_linequantity ; 
              ltpch:l_has_partsupplier ?ps2 .
        ?ps2 ltpch:part_partkey ?p_partkey ;
              ltpch:part_container ?p_container ;
              ltpch:part_brand ?p_brand  .
        filter (REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%" ) 
      }
      group by
        ?p_partkey
    }
    filter (xsd:decimal(?l_linequantity) < xsd:decimal(?threshold)) 
}

          

Query 18


select
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
   (sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_has_order ?ord .
    ?ord ltpch:order_orderkey ?o_orderkey ;
        ltpch:order_orderdate ?o_orderdate ;
        ltpch:order_ordertotalprice ?o_ordertotalprice ;
        ltpch:customer_custkey ?c_custkey ;
        ltpch:customer_name ?c_name .
    { 
      select 
        ?ord 
        (sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
      where 
      {
        ?li2 a ltpch:lineitem ;
             ltpch:l_linequantity ?l2_linequantity ;
             ltpch:l_has_order ?ord .
      }
      group by
        ?ord
    } .
    filter (?sum_q > %QUANTITY%)
}
group by
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
order by
  desc (?o_ordertotalprice)
  ?o_orderdate
limit 100

          

Query 19


select
  ((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_has_partsupplier ?ps ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_shipmode ?l_shipmode ;
        ltpch:l_shipinstruct ?l_shipinstruct .
     ?ps ltpch:part_brand ?p_brand ;
          ltpch:part_size ?p_size ;
          ltpch:part_container ?p_container .
     

     filter (?l_shipmode in ("AIR", "AIR REG") &&
      ?l_shipinstruct = "DELIVER IN PERSON" &&
      ( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
          (?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
          (xsd:integer(?l_linequantity) <= %QUANTITY1% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
        ( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
          (?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY2%) && 
          (xsd:integer(?l_linequantity) <= %QUANTITY2% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
        ( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
          (?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
          (xsd:integer(?l_linequantity) <= %QUANTITY3% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
  }

          

Query 20


select distinct
  ?s_name
  ?s_address
where
{
  ?supp ltpch:supplier_name ?s_name ;
        ltpch:supplier_suppkey ?suppkey ;
        ltpch:supplier_address ?s_address .
  { 
    select 
      distinct ?suppkey 
    where 
    {
      ?big_ps ltpch:partsupplier_availqty ?big_ps_availqty ;
              ltpch:supplier_suppkey ?suppkey ;
              ltpch:nation_name ?n_name ;
        ltpch:supplier_suppkey ?suppkey ;
        ltpch:part_partkey ?partkey ;
              ltpch:part_name ?p_name . 
      FILTER(REGEX (?p_name , "^%COLOR%") && ?n_name = "%NATION%") .
      {
        select 
          ?partkey ?suppkey
          ((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
        where
        {
          ?li qb:dataSet ltpch:lineitemCube ;
              ltpch:l_shipdate ?l_shipdate ;
              ltpch:l_linequantity ?l_linequantity ;
              ltpch:l_has_partsupplier ?big_ps .
          ?big_ps ltpch:part_partkey ?partkey ;
              ltpch:supplier_suppkey ?suppkey .
          FILTER ((xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
            (xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))
          )
        } 
        group by
          ?partkey ?suppkey
      } .
      FILTER(xsd:decimal(?big_ps_availqty) > ?qty_threshold) .
    } 
  }
}
order by ?s_name

          

Query 21


select
    ?s_name
    ((count(1)) as ?numwait)
where {
         ?li1 qb:dataSet ltpch:lineitemCube ;
              ltpch:l_receiptdate ?l1_receiptdate ;
              ltpch:l_commitdate ?l1_commitdate ;
              ltpch:l_has_partsupplier ?ps ;
              ltpch:l_has_order ?ord .
         ?ps ltpch:supplier_name ?s_name ;
             ltpch:supplier_suppkey ?suppkey ;
             ltpch:nation_name ?n_name .
         ?ord ltpch:order_orderstatus ?o_orderstatus .
         filter ( xsd:boolean(xsd:dateTime(?l1_receiptdate) > xsd:dateTime(?l1_commitdate)) && ?n_name = "%NATION%" && ?o_orderstatus = "F")
         filter exists {
              ?li2 ltpch:l_has_order ?ord ;
                   ltpch:l_has_partsupplier ?ps2 .
              ?ps2 ltpch:supplier_suppkey ?suppkey2 .
              filter (?suppkey != ?suppkey2)
         }
         filter not exists {
              ?li3 ltpch:l_has_order ?ord ;
                   ltpch:l_receiptdate ?l3_receiptdate ;
                   ltpch:l_commitdate ?l3_commitdate ;
                   ltpch:l_has_partsupplier ?ps3 .
              ?ps3 ltpch:supplier_suppkey ?suppkey3 .
              filter (
                 xsd:boolean(xsd:dateTime(?l3_receiptdate) > xsd:dateTime(?l3_commitdate)) &&
                 ?suppkey3 != ?suppkey
              )
         }
       }
group by
   ?s_name
order by
    desc (count(1))
    ?s_name
limit 100
          

Query 22


select
  (bif:LEFT (?c_phone, 2)) as ?cntrycode,
  (count (1)) as ?numcust,
  sum (xsd:decimal(?c_acctbal)) as ?totacctbal
where {
    ?cust ltpch:customer_acctbal ?c_acctbal ;
      ltpch:customer_phone ?c_phone .
      {
select (avg (?acctbal2)) as ?acctbal_threshold
where {      
select (avg (xsd:decimal(?c_acctbal2))) as ?acctbal2
          where
            {
              ?cust2 ltpch:customer_acctbal ?c_acctbal2 ;
                     ltpch:customer_custkey ?custkey2 ;
                 ltpch:customer_phone ?c_phone2 .
              filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
                bif:LEFT (?c_phone2, 2) in (%COUNTRY_CODE_SET%) )
            }
          group by ?custkey2
      }
}
    filter (
      bif:LEFT (?c_phone, 2) in (%COUNTRY_CODE_SET%) &&
      (xsd:decimal(?c_acctbal) > ?acctbal_threshold )
    ) 
    filter not exists { ?cust ltpch:order_orderkey ?orderkey }
  }
group by (bif:LEFT (?c_phone, 2))
order by (bif:LEFT (?c_phone, 2))


          
Virtuoso queries denormalized pattern

Prefix


prefix xsd: <http://www.w3.org/2001/XMLSchema#> 
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
          

Query 1


select
  ?l_returnflag 
  ?l_linestatus 
  (sum(xsd:decimal(?l_linequantity)) as ?sum_qty) 
  (ROUND(sum(xsd:decimal(?l_lineextendedprice))*100)/100 as ?sum_base_price)
  (ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount)))*100)/100 as ?sum_disc_price)
  (ROUND(sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax)))*100)/100 as ?sum_charge)
  (ROUND(avg(xsd:decimal(?l_linequantity))*100)/100 as ?avg_qty)
  (ROUND(avg(xsd:decimal(?l_lineextendedprice))*100)/100 as ?avg_price)
  (ROUND(avg(xsd:decimal(?l_linediscount))*100)/100 as ?avg_disc) 
  (count(1) as ?count_order)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_returnflag ?l_returnflag ;
       ltpch:l_linestatus ?l_linestatus ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linetax ?l_linetax ;
       ltpch:l_shipdate ?l_shipdate ;
       ltpch:l_linediscount ?l_linediscount .
   filter (xsd:boolean(xsd:dateTime(?l_shipdate) <= xsd:dateTime(bif:dateadd ("day", -%DELTA%, "1998-12-01"^^xsd:date))))
} 
group by
  ?l_returnflag
  ?l_linestatus
order by
  ?l_returnflag
  ?l_linestatus

            

Query 2


select distinct
  ?s_acctbal,
  ?s_name,
  ?nation_name,
  ?p_partkey,
  ?p_mfgr,
  ?s_address,
  ?s_phone,
  ?s_comment
where {
  ?ps ltpch:partsupplier_supplier_acctbal ?s_acctbal ;
    ltpch:partsupplier_supplier_name ?s_name ;
    ltpch:partsupplier_partsupplier_supplycost ?minsc ;    
    ltpch:partsupplier_supplier_address ?s_address ;
    ltpch:partsupplier_supplier_phone ?s_phone ;
    ltpch:partsupplier_supplier_comment ?s_comment ;
    ltpch:partsupplier_nation_name ?nation_name ;
    ltpch:partsupplier_region_name "%REGION%" ;
    ltpch:partsupplier_part_partkey ?p_partkey ;
    ltpch:partsupplier_part_mfgr ?p_mfgr ;
    ltpch:partsupplier_part_size ?size ;
    ltpch:partsupplier_part_type ?p_type .
FILTER (?size = str(%SIZE%) && contains(?p_type, "%TYPE%"))
{ select ?p_partkey  min(?s_cost) as ?minsc
    where {
        ?ps ltpch:partsupplier_part_partkey ?p_partkey;
            ltpch:partsupplier_partsupplier_supplycost ?s_cost ;
            ltpch:partsupplier_region_name ?region2 .
            filter (?region2 = "%REGION%")
      } 
    }
     
  }
order by
  desc (?s_acctbal)
  ?nation_name
  ?s_name
  ?p_partkey
limit 100


            

Query 3


select
  ?o_orderkey
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?o_orderdate
  ?o_shippriority
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
    ltpch:l_lineextendedprice ?l_lineextendedprice ;
    ltpch:l_linediscount ?l_linediscount ;
    ltpch:l_shipdate ?l_shipdate ;
    ltpch:order_order_orderdate ?o_orderdate ;
    ltpch:order_order_shippriority ?o_shippriority ;
    ltpch:order_order_orderkey ?o_orderkey ;
    ltpch:order_customer_mktsegment ?c_mktsegment .
  filter ((xsd:dateTime(?o_orderdate) < xsd:dateTime("%DATE%"^^xsd:date)) &&
    (xsd:dateTime(?l_shipdate) > xsd:dateTime("%DATE%"^^xsd:date)) &&
    (?c_mktsegment = "%SEGMENT%") ) 
}
group by
  ?o_orderkey
  ?o_orderdate
  ?o_shippriority
order by
  desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
  ?o_orderdate
limit 10
            

Query 4


select
  ?o_orderpriority
  (count(*) as ?order_count)
where  
{
  {
    select distinct
      ?o_orderpriority
      ?ordkey
    where 
    {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_commitdate ?l_commitdate ;
          ltpch:l_receiptdate ?l_receiptdate ;
          ltpch:order_order_orderpriority ?o_orderpriority ;
          ltpch:order_order_orderkey ?ordkey ;
          ltpch:order_order_orderdate ?o_orderdate .
      filter (
        (xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
        (xsd:boolean(xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date))) &&
        (xsd:boolean(xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date))))
      )
    }
  }
}
group by
  ?o_orderpriority
order by
  ?o_orderpriority
            

Query 5


select
  ?nation
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where  {
   ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount ;
       ltpch:order_order_orderdate ?o_orderdate ;
       ltpch:order_nation_name ?c_nation ;
       ltpch:partsupplier_nation_name ?nation ;
       ltpch:partsupplier_region_name ?r_name .
    
    filter ((?c_nation = ?nation) &&
      (xsd:dateTime(?o_orderdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
      (xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("year", 1,"%YEAR%-01-01" ^^xsd:date))) &&
      (?r_name = "%REGION%") ) 
  }
group by
  ?nation
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))

            

Query 6


select
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_shipdate ?l_shipdate .
    filter ( (xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
      (xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date))) &&
      (xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
      (xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
      (xsd:decimal(?l_linequantity) < %QUANTITY%) ) 
}

            

Query 7


select 
  ?supp_nation 
  ?cust_nation 
  ?li_year
  (sum (xsd:decimal(?volume)) as ?revenue)
where {
  {
    select
      ?supp_nation
      ?cust_nation
      ?li_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_shipdate ?l_shipdate ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount ;
        ltpch:order_nation_name ?cust_nation ;
        ltpch:partsupplier_nation_name ?supp_nation .
      BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
      filter ((
        (?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
        (?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
        (xsd:dateTime(?l_shipdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
        (xsd:dateTime(?l_shipdate) <= xsd:dateTime("1996-12-31"^^xsd:date)) ) 
      } 
   } 
}
group by
  ?supp_nation
  ?cust_nation
  ?li_year
order by
  ?supp_nation
  ?cust_nation
  ?li_year

            

Query 8


select
  ?o_year
  ((?sum1 / ?sum2) as ?mkt_share)
where {
  { select
    ?o_year
    (sum (?volume * bif:equ (?nation, "%NATION%")) as ?sum1)
    (sum (?volume) as ?sum2)
    where {
      { select
           ((YEAR(xsd:dateTime(?o_orderdate))) as ?o_year)
           ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
           ?nation
         where {
           ?li qb:dataSet ltpch:lineitemCube ;
               ltpch:l_has_partsupplier ?ps ;
               ltpch:l_lineextendedprice ?l_lineextendedprice ;
               ltpch:l_linediscount ?l_linediscount ;
               ltpch:partsupplier_nation_name ?nation ;
               ltpch:partsupplier_part_type ?type ;
               ltpch:order_order_orderdate ?o_orderdate ;
               ltpch:order_region_name ?region .
           filter ((xsd:dateTime(?o_orderdate) >= xsd:dateTime("1995-01-01"^^xsd:date)) &&
             (xsd:dateTime(?o_orderdate) <= xsd:dateTime("1996-12-31"^^xsd:date) &&
              ?region = "%REGION%" &&
              ?type = "%TYPE%") 
           ) 
        } 
      } 
    }
    group by
      ?o_year 
  } 
}
order by
  ?o_year

            

Query 9


select
  ?nation
  ?o_year
  (sum(?amount) as ?sum_profit)
where {
  { select
      ?nation
      ?o_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_linequantity ?l_linequantity ;
          ltpch:l_lineextendedprice ?l_lineextendedprice ;
          ltpch:l_linediscount ?l_linediscount ;
          ltpch:partsupplier_nation_name ?nation ;
          ltpch:partsupplier_partsupplier_supplycost ?ps_supplycost ;
          ltpch:partsupplier_part_name ?p_name ;
          ltpch:order_order_orderdate ?o_orderdate .
      filter (REGEX (?p_name, "%COLOR%"))
      BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
    } 
  } 
}
group by
  ?nation
  ?o_year
order by
  ?nation
  desc (?o_year)

            

Query 10


select
  ?c_custkey
  ?c_companyName
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
      ltpch:l_returnflag ?l_returnflag ;
      ltpch:l_lineextendedprice ?l_lineextendedprice ;
      ltpch:l_linediscount ?l_linediscount ;
      ltpch:order_order_orderdate ?o_orderdate ;
      ltpch:order_customer_address ?c_address ;
      ltpch:order_customer_phone ?c_phone ;
      ltpch:order_customer_comment ?c_comment ;
      ltpch:order_customer_acctbal ?c_acctbal ;
      ltpch:order_customer_custkey ?c_custkey ;
      ltpch:order_customer_name ?c_companyName ;
      ltpch:order_nation_name ?nation .
   filter ((xsd:dateTime(?o_orderdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date)) &&
      (xsd:dateTime(?o_orderdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date))) &&
      (?l_returnflag = "R") 
   ) 
}
group by
  ?c_custkey
  ?c_companyName
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20

            

Query 11


select
  ?bigpspart,
  ?bigpsvalue
where {
      { 
        select
          ?bigpspart,
          sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue
        where
          {
            {
              select
                ?bigpspart,
                ?b_supplycost,
                ?b_availqty
              where
                {
                  ?bigps ltpch:partsupplier_part_partkey ?bigpspart ;
                        ltpch:partsupplier_partsupplier_supplycost ?b_supplycost ;
                        ltpch:partsupplier_supplier_suppkey ?b_suppkey ;
                        ltpch:partsupplier_partsupplier_availqty ?b_availqty ;
                        ltpch:partsupplier_nation_name "%NATION%" .
                }
              group by ?bigpspart ?b_suppkey 
            }
          }
      }
    filter (?bigpsvalue > (
        select
          (sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty)) * %FRACTION%) as ?threshold
        where
          {
             {
              select
                ?t_partkey,
                ?t_supplycost,
                ?t_availqty
              where
                {
                  ?thr_ps ltpch:partsupplier_partsupplier_supplycost ?t_supplycost ;
                    ltpch:partsupplier_partsupplier_availqty ?t_availqty ;
                    ltpch:partsupplier_nation_name "%NATION%" ;
                    ltpch:partsupplier_supplier_suppkey ?t_suppkey ;
                    ltpch:partsupplier_part_partkey ?t_partkey .
                }
              group by ?t_partkey ?t_suppkey 
            }
          }
    ))
  }
order by
  desc (?bigpsvalue)

          

Query 12


select
  ?l_shipmode
  (sum (
      bif:__or (
        bif:equ (?o_orderpriority, "1-URGENT"),
        bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
  (sum (1 -
      bif:__or (
        bif:equ (?o_orderpriority, "1-URGENT"),
        bif:equ (?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where  {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_commitdate ?l_commitdate ;
       ltpch:l_receiptdate ?l_receiptdate ;
       ltpch:l_shipmode ?l_shipmode ;
       ltpch:l_shipdate ?l_shipdate ;
       ltpch:order_order_orderpriority ?o_orderpriority .
    filter (xsd:boolean(?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%")) &&
      (xsd:boolean(xsd:dateTime(?l_commitdate) < xsd:dateTime(?l_receiptdate))) &&
      (xsd:boolean(xsd:dateTime(?l_shipdate) < xsd:dateTime(?l_commitdate))) &&
      (xsd:boolean(xsd:dateTime(?l_receiptdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date))) &&
      (xsd:boolean(xsd:dateTime(?l_receiptdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))) )
  }
group by
  ?l_shipmode
order by
  ?l_shipmode

          

Query 13


select
  ?c_count
  (count(1) as ?custdist)
where {
    { select
        ?c_custkey
        (count (distinct ?o_comment) as ?c_count)
      where
        {
          ?li2 ltpch:order_customer_custkey ?c_custkey .
           optional {
             ?li2 ltpch:order_order_comment ?o_comment .
              filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) . 
          }
        }
      group by 
        ?c_custkey
      order by 
        ?c_custkey
    }
  }
group by
  ?c_count
order by
  desc (count(1))
  desc (?c_count)


          

Query 14


select
  ((100 * ?sum1 / ?sum2 ) as ?promo_revenue)
where
{
  select 
    (sum (
          bif:equ(SUBSTR(?p_type, 1, 5), "PROMO") *
          xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) ) as ?sum1)
    (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) ) as ?sum2)
  where {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_lineextendedprice ?l_lineextendedprice ;
          ltpch:l_linediscount ?l_linediscount ;
          ltpch:l_shipdate ?l_shipdate ;
          ltpch:partsupplier_part_type ?p_type .
      filter ((xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date)) &&
        (xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd("month", 1, "%MONTH%-01"^^xsd:date))) )
  }
}
          

Query 15


select distinct
  ?s_suppkey
  ?s_name
  ?s_address
  ?s_phone
  ?total_revenue
where  
{
  ?partsupp ltpch:partsupplier_supplier_suppkey ?s_suppkey ;
            ltpch:partsupplier_supplier_name ?s_name ;
            ltpch:partsupplier_supplier_address ?s_address ;
            ltpch:partsupplier_supplier_phone ?s_phone .
  { 
    select
        ?s_suppkey
        ((sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount)))) as ?total_revenue)
    where 
    {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_shipdate ?l_shipdate ;
          ltpch:l_lineextendedprice ?l_extendedprice ;
          ltpch:l_linediscount ?l_discount ;
          ltpch:l_has_partsupplier ?ps ;
          ltpch:partsupplier_supplier_suppkey ?s_suppkey .
      filter (
          xsd:dateTime(?l_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
          xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
    }
    group by
      ?s_suppkey
  } .
  { 
    select 
      (max (?l2_total_revenue) as ?maxtotal)
    where 
    {
      { 
        select
          ((sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount)))) as ?l2_total_revenue)
        where 
        {
          ?li2 qb:dataSet ltpch:lineitemCube ;
              ltpch:l_shipdate ?l2_shipdate ;
              ltpch:l_lineextendedprice ?l2_extendedprice ;
              ltpch:l_linediscount ?l2_discount ;
              ltpch:l_has_partsupplier ?ps2 ;
              ltpch:partsupplier_supplier_suppkey ?s_suppkey2 .
          filter (
              xsd:dateTime(?l2_shipdate) >= xsd:dateTime("%MONTH%-01"^^xsd:date) &&
              xsd:dateTime(?l2_shipdate) < xsd:dateTime(bif:dateadd ("month", 3, "%MONTH%-01"^^xsd:date)) )
        }
        group by
          ?s_suppkey2
      }
    }
  }
  filter (?total_revenue = ?maxtotal)
}
order by
  ?s_suppkey

          

Query 16


select
  ?p_brand,
  ?p_type,
  ?p_size,
  (count(distinct ?supp)) as ?supplier_cnt
where {
    ?ps ltpch:partsupplier_part_brand ?p_brand ;
        ltpch:partsupplier_part_type ?p_type ;
        ltpch:partsupplier_part_size ?p_size ;   
        ltpch:partsupplier_supplier_suppkey ?supp .    
    filter (
      (?p_brand != "%BRAND%") &&
      !(?p_type like "%TYPE%%") &&
      (xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
    )
    filter NOT EXISTS {
       ?ps ltpch:partsupplier_supplier_comment ?badcomment .
       filter (?badcomment like "%Customer%Complaints%") 
    }
  }
group by
  ?p_brand
  ?p_type
  ?p_size
order by
  desc ((count(distinct ?supp)))
  ?p_brand
  ?p_type
  ?p_size

          

Query 17


select
  ((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_has_partsupplier ?ps ;
        ltpch:partsupplier_part_partkey ?p_partkey.
    {
      select 
        ?p_partkey
        ((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
      where { 
        ?li2  a ltpch:lineitem ;
              ltpch:l_linequantity ?l2_linequantity ; 
              ltpch:partsupplier_part_partkey ?p_partkey ;
              ltpch:partsupplier_part_container ?p_container ;
              ltpch:partsupplier_part_brand ?p_brand  .
        filter (REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%" ) 
      }
      group by
        ?p_partkey
    }
    filter (xsd:decimal(?l_linequantity) < xsd:decimal(?threshold)) 
}

          

Query 18


select
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
   (sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:order_order_orderkey ?o_orderkey ;
        ltpch:order_order_orderdate ?o_orderdate ;
        ltpch:order_order_ordertotalprice ?o_ordertotalprice ;
        ltpch:order_customer_custkey ?c_custkey ;
        ltpch:order_customer_name ?c_name .
    { 
      select 
        ?o_orderkey 
        (sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
      where 
      {
        ?li2 a ltpch:lineitem ;
             ltpch:l_linequantity ?l2_linequantity ;
             ltpch:order_order_orderkey ?o_orderkey .
      }
      group by
        ?o_orderkey
    } .
    filter (?sum_q > %QUANTITY%)
}
group by
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
order by
  desc (?o_ordertotalprice)
  ?o_orderdate
limit 100

          

Query 19


select
  ((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_shipmode ?l_shipmode ;
        ltpch:l_shipinstruct ?l_shipinstruct ;
        ltpch:partsupplier_part_brand ?p_brand ;
        ltpch:partsupplier_part_size ?p_size ;
        ltpch:partsupplier_part_container ?p_container .
     filter (?l_shipmode in ("AIR", "AIR REG") &&
      ?l_shipinstruct = "DELIVER IN PERSON" &&
      ( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
          (?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
          (xsd:integer(?l_linequantity) <= %QUANTITY1% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
        ( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
          (?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY2%) && 
          (xsd:integer(?l_linequantity) <= %QUANTITY2% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
        ( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
          (?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
          (xsd:integer(?l_linequantity) <= %QUANTITY3% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
  }

          

Query 20


select distinct
  ?s_name
  ?s_address
where
{
  ?supp ltpch:partsupplier_supplier_name ?s_name ;
        ltpch:partsupplier_supplier_suppkey ?suppkey ;
        ltpch:partsupplier_supplier_address ?s_address .
  { 
    select 
      distinct ?suppkey 
    where 
    {
      ?li ltpch:partsupplier_partsupplier_availqty ?big_ps_availqty ;
              ltpch:partsupplier_supplier_suppkey ?suppkey ;
        ltpch:partsupplier_part_partkey ?partkey ;
              ltpch:partsupplier_nation_name ?n_name ;
              ltpch:partsupplier_part_name ?p_name . 
      FILTER(REGEX (?p_name , "^%COLOR%") && ?n_name = "%NATION%") .
      {
        select 
          ?suppkey ?partkey
          ((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
        where
        {
          ?li qb:dataSet ltpch:lineitemCube ;
              ltpch:l_shipdate ?l_shipdate ;
        ltpch:partsupplier_supplier_suppkey ?suppkey ;
        ltpch:partsupplier_part_partkey ?partkey ;
              ltpch:l_linequantity ?l_linequantity .
          FILTER ((xsd:dateTime(?l_shipdate) >= xsd:dateTime("%YEAR%-01-01"^^xsd:date)) &&
            (xsd:dateTime(?l_shipdate) < xsd:dateTime(bif:dateadd ("year", 1, "%YEAR%-01-01"^^xsd:date)))
          )
        } 
        group by
          ?suppkey ?partkey
      } .
      FILTER(xsd:decimal(?big_ps_availqty) > ?qty_threshold) .
    } 
  }
}
order by ?s_name


          

Query 21


select
    ?s_name
    ((count(1)) as ?numwait)
where {
         ?li1 qb:dataSet ltpch:lineitemCube ;
              ltpch:l_receiptdate ?l1_receiptdate ;
              ltpch:l_commitdate ?l1_commitdate ;
              ltpch:partsupplier_supplier_name ?s_name ;
              ltpch:partsupplier_supplier_suppkey ?suppkey ;
              ltpch:partsupplier_nation_name ?n_name ;
              ltpch:order_order_orderkey ?orderkey ;
              ltpch:order_order_orderstatus ?o_orderstatus .
         filter ( 
          xsd:boolean(xsd:dateTime(?l1_receiptdate) > xsd:dateTime(?l1_commitdate)) && 
          ?n_name = "%NATION%" && 
          ?o_orderstatus = "F"
          )
         filter exists {
              ?li2 ltpch:order_order_orderkey ?orderkey ;
                   ltpch:partsupplier_supplier_suppkey ?suppkey2 .
              filter (?suppkey != ?suppkey2)
         }
         filter not exists {
              ?li3 ltpch:order_order_orderkey ?orderkey ;
                   ltpch:l_receiptdate ?l3_receiptdate ;
                   ltpch:l_commitdate ?l3_commitdate ;
                   ltpch:partsupplier_supplier_suppkey ?suppkey3 .
              filter (
                 xsd:boolean(xsd:dateTime(?l3_receiptdate) > xsd:dateTime(?l3_commitdate)) &&
                 ?suppkey3 != ?suppkey
              )
         }
       }
group by
   ?s_name
order by
    desc (count(1))
    ?s_name
limit 100

          

Query 22


select
  (bif:LEFT (?c_phone, 2)) as ?cntrycode,
  (count (1)) as ?numcust,
  sum (xsd:decimal(?c_acctbal)) as ?totacctbal
where {
    ?cust ltpch:order_customer_acctbal ?c_acctbal ;
      ltpch:order_customer_phone ?c_phone .
    {
select (avg(?acctbal2)) as ?acctbal_threshold
where
{
    select (avg (xsd:decimal(?c_acctbal2))) as ?acctbal2
          where
            {
              ?li ltpch:order_customer_acctbal ?c_acctbal2 ;
                 ltpch:order_customer_custkey ?custkey2 ;
                 ltpch:order_customer_phone ?c_phone2 .
              filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
                bif:LEFT (?c_phone2, 2) in (%COUNTRY_CODE_SET%) )
            }
          group by ?custkey2
} 
   }
    filter (
      bif:LEFT (?c_phone, 2) in (%COUNTRY_CODE_SET%) &&
      (xsd:decimal(?c_acctbal) > ?acctbal_threshold )
    ) 
    filter not exists { ?cust ltpch:order_order_orderkey ?orderkey }
  }
group by (bif:LEFT (?c_phone, 2))
order by (bif:LEFT (?c_phone, 2))


          
Jena queries snowflake pattern

Prefix


prefix xsd: <http://www.w3.org/2001/XMLSchema#> 
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
          

Query 1


select
  ?l_returnflag 
  ?l_linestatus 
  (sum(xsd:decimal(?l_linequantity)) as ?sum_qty) 
  (sum(xsd:decimal(?l_lineextendedprice)) as ?sum_base_price) 
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?sum_disc_price) 
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax))) as ?sum_charge) 
  (avg(xsd:decimal(?l_linequantity)) as ?avg_qty) 
  (avg(xsd:decimal(?l_lineextendedprice)) as ?avg_price) 
  (avg(xsd:decimal(?l_linediscount)) as ?avg_disc) 
  (count(1) as ?count_order)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_returnflag ?l_returnflag ;
       ltpch:l_linestatus ?l_linestatus ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linetax ?l_linetax ;
       ltpch:l_shipdate ?l_shipdate ;
       ltpch:l_linediscount ?l_linediscount .
    filter (xsd:date(?l_shipdate) <= ("1998-12-01"^^xsd:date + "-P%DELTA%D"^^xsd:duration))
} 
group by
  ?l_returnflag
  ?l_linestatus
order by
  ?l_returnflag
  ?l_linestatus
            

Query 2


select
  ?s_acctbal
  ?s_name
  ?nation_name
  ?p_partkey
  ?p_mfgr
  ?s_address
  ?s_phone
  ?s_comment
where {
  ?ps a ltpch:partsupp;
      ltpch:ps_has_supplier ?supp;
      ltpch:ps_has_part ?part ;
      ltpch:ps_supplycost ?minsc .
  ?supp a ltpch:supplier ;
     ltpch:s_acctbal ?s_acctbal ;
   ltpch:s_name ?s_name ;
     ltpch:s_has_nation ?s_has_nation ;
     ltpch:s_address ?s_address ;
     ltpch:s_phone ?s_phone ;
     ltpch:s_comment ?s_comment .
  ?s_has_nation ltpch:n_name ?nation_name ;
     ltpch:n_has_region ?s_has_region .
  ?s_has_region ltpch:r_name "%REGION%" .
  ?part a ltpch:part ;
      ltpch:p_partkey ?p_partkey ;
      ltpch:p_mfgr ?p_mfgr ;
      ltpch:p_size ?size ;
      ltpch:p_type ?p_type .
  FILTER (?size = str(%SIZE%) && fn:contains(?p_type, "%TYPE%"))
  { select ?part (min(?s_cost) as ?minsc)
    where {
        ?ps a ltpch:partsupp;
            ltpch:ps_has_part ?part;
            ltpch:ps_has_supplier ?ms;
            ltpch:ps_supplycost ?s_cost .
        ?ms ltpch:s_has_nation ?m_has_nation .
        ?m_has_nation ltpch:n_has_region ?m_has_region .
        ?m_has_region ltpch:r_name "%REGION%" .
      } 
      group by ?part 
    }
    
  }
order by
  desc (?s_acctbal)
  ?nation_name
  ?s_name
  ?p_partkey
limit 100


            

Query 3


select
  ?o_orderkey
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?o_orderdate
  ?o_shippriority
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
    ltpch:l_lineextendedprice ?l_lineextendedprice ;
    ltpch:l_linediscount ?l_linediscount ;
    ltpch:l_has_order ?ord ;
    ltpch:l_shipdate ?l_shipdate .
  ?ord ltpch:o_orderdate ?o_orderdate ;
    ltpch:o_shippriority ?o_shippriority ;
    ltpch:o_orderkey ?o_orderkey ;
    ltpch:o_has_customer ?cust .
  ?cust ltpch:c_mktsegment ?c_mktsegment .
  filter ((xsd:date(?o_orderdate) < "%DATE%"^^xsd:date) &&
    (xsd:date(?l_shipdate) > "%DATE%"^^xsd:date) &&
    (?c_mktsegment = "%SEGMENT%") ) 
}
group by
  ?o_orderkey
  ?o_orderdate
  ?o_shippriority
order by
  desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
  ?o_orderdate
limit 10

            

Query 4


select
  ?o_orderpriority
  (count(*) as ?order_count)
where  
{
  {
    select distinct
      ?o_orderpriority
      ?ord
    where 
    {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_has_order ?ord ;
          ltpch:l_commitdate ?l_commitdate ;
          ltpch:l_receiptdate ?l_receiptdate .
      ?ord ltpch:o_orderpriority ?o_orderpriority ;
           ltpch:o_orderdate ?o_orderdate .
      filter (
        (xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
        (xsd:date(?o_orderdate) >= "%MONTH%-01"^^xsd:date) &&
        (xsd:date(?o_orderdate) < ("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration))
      )
    }
  }
}
group by
  ?o_orderpriority
order by
  ?o_orderpriority
            

Query 5


select
  ?nation
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where  {
   ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_has_order ?ord ;
       ltpch:l_has_partsupplier ?ps ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount .
    ?ord ltpch:o_has_customer ?cust ;
         ltpch:o_orderdate ?o_orderdate .
    ?ps ltpch:ps_has_supplier ?supp .
    ?supp ltpch:s_has_nation ?s_nation .
    ?s_nation ltpch:n_has_region ?s_region ;
              ltpch:n_name ?nation .
    ?s_region ltpch:r_name ?r_name .
    ?cust ltpch:c_has_nation ?c_nation.
    filter ((?c_nation = ?s_nation) &&
      (xsd:date(?o_orderdate) >= "%YEAR%-01-01"^^xsd:date) &&
      (xsd:date(?o_orderdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) &&
      (?r_name = "%REGION%") ) 
  }
group by
  ?nation
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))

            

Query 6


select
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_shipdate ?l_shipdate .
    filter ( (xsd:date(?l_shipdate) >= xsd:date("%YEAR%-01-01"^^xsd:date)) &&
      (xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration )) &&
      (xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
      (xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
      (xsd:decimal(?l_linequantity) < %QUANTITY%) ) 
}

            

Query 7


select 
  ?supp_nation 
  ?cust_nation 
  ?li_year
  (sum (?volume) as ?revenue)
where {
  {
    select
      ?supp_nation
      ?cust_nation
      ?li_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_has_order ?ord ;
        ltpch:l_has_partsupplier ?ps ;
        ltpch:l_shipdate ?l_shipdate ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount .
      ?ord ltpch:o_has_customer ?cust .
      ?cust ltpch:c_has_nation ?custn .
      ?custn ltpch:n_name ?cust_nation .
      ?ps ltpch:ps_has_supplier ?supp .
      ?supp ltpch:s_has_nation ?suppn .
      ?suppn ltpch:n_name ?supp_nation .
      BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
      filter (xsd:boolean(
        (?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
        (?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
        xsd:boolean(xsd:date(?l_shipdate) >= xsd:date("1995-01-01"^^xsd:date)) &&
        xsd:boolean(xsd:date(?l_shipdate) <= xsd:date("1996-12-31"^^xsd:date)) ) 
      } 
   } 
}
group by
  ?supp_nation
  ?cust_nation
  ?li_year
order by
  ?supp_nation
  ?cust_nation
  ?li_year

            

Query 8


select
  ?o_year
  ((?sum1 / ?sum2) as ?mkt_share)
where {
  { select
    ?o_year
    (sum (?volume * xsd:integer(fn:starts-with(?nation, "%NATION%"))) as ?sum1)
    (sum (?volume) as ?sum2)
    where {
      { select
           ((YEAR (xsd:date(?o_orderdate))) as ?o_year)
           ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
           ?nation
         where {
           ?li qb:dataSet ltpch:lineitemCube ;
               ltpch:l_has_partsupplier ?ps ;
               ltpch:l_has_order ?ord ;
               ltpch:l_has_partsupplier ?ps ;
               ltpch:l_lineextendedprice ?l_lineextendedprice ;
               ltpch:l_linediscount ?l_linediscount .
           ?ps ltpch:ps_has_supplier ?s_supplier .
           ?s_supplier ltpch:s_has_nation ?n2 .
           ?n2 ltpch:n_name ?nation .
           ?ps ltpch:ps_has_part ?part .
           ?part ltpch:p_type ?type .
           ?ord ltpch:o_orderdate ?o_orderdate ;
             ltpch:o_has_customer ?c_customer .
           ?c_customer ltpch:c_has_nation ?n_nation .
           ?n_nation ltpch:n_has_region ?r_region .
           ?r_region ltpch:r_name ?region.
           filter ((xsd:date(?o_orderdate) >= xsd:date("1995-01-01"^^xsd:date)) &&
             (xsd:date(?o_orderdate) <= xsd:date("1996-12-31"^^xsd:date)) &&
              (?region = "%REGION%") &&
              (?type = "%TYPE%") 
           ) 
        } 
      } 
    }
    group by
      ?o_year 
  } 
}
order by
  ?o_year

            

Query 9


select
  ?nation
  ?o_year
  (sum(?amount) as ?sum_profit)
where {
  { select
      ?nation
      ?o_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_has_order ?ord ;
          ltpch:l_has_partsupplier ?ps ;
          ltpch:l_linequantity ?l_linequantity ;
          ltpch:l_lineextendedprice ?l_lineextendedprice ;
          ltpch:l_linediscount ?l_linediscount .
      ?ps ltpch:ps_has_part ?part ;
          ltpch:ps_has_supplier ?supp .
      ?supp ltpch:s_has_nation ?s_nation .
      ?s_nation ltpch:n_name ?nation .
      ?ord ltpch:o_orderdate ?o_orderdate .
      ?ps ltpch:ps_supplycost ?ps_supplycost .
      ?part ltpch:p_name ?p_name .
      filter (REGEX (?p_name, "%COLOR%"))
      BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
    } 
  } 
}
group by
  ?nation
  ?o_year
order by
  ?nation
  desc (?o_year)

            

Query 10


select
  ?c_custkey
  ?c_companyName
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
      ltpch:l_returnflag ?l_returnflag ;
      ltpch:l_has_order ?ord ;
      ltpch:l_lineextendedprice ?l_lineextendedprice ;
      ltpch:l_linediscount ?l_linediscount .
  ?ord ltpch:o_has_customer ?cust ;
       ltpch:o_orderdate ?o_orderdate .
  ?cust ltpch:c_address ?c_address ;
      ltpch:c_phone ?c_phone ;
      ltpch:c_comment ?c_comment ;
      ltpch:c_acctbal ?c_acctbal ;
      ltpch:c_custkey ?c_custkey ;
      ltpch:c_has_nation ?c_nation ;
      ltpch:c_name ?c_companyName .
   ?c_nation ltpch:n_name ?nation .
   filter ((xsd:date(?o_orderdate) >= xsd:date("%MONTH%-01"^^xsd:date)) &&
      (xsd:date(?o_orderdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration)) &&
      (?l_returnflag = "R") 
   ) 
}
group by
  ?c_custkey
  ?c_companyName
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20

            

Query 11


select
  ?bigpspart
  ?bigpsvalue
where {
      { select
          ?bigpspart
          (sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue)
        where
          {
            ?bigps a ltpch:partsupp ;
                   ltpch:ps_has_part ?bigpspart ;
                   ltpch:ps_supplycost ?b_supplycost ;
                   ltpch:ps_availqty ?b_availqty ;
                   ltpch:ps_has_supplier ?b_supplier .
            ?b_supplier ltpch:s_has_nation ?b_nation .
            ?b_nation ltpch:n_name "%NATION%" .
          }
          group by ?bigpspart
      }
      {
       select
          (sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty)) * %FRACTION% as ?threshold)
        where
          {
            ?thr_ps a ltpch:partsupp ;
                    ltpch:ps_supplycost ?t_supplycost ;
                    ltpch:ps_availqty ?t_availqty ;
                    ltpch:ps_has_supplier ?t_supplier .
            ?t_supplier ltpch:s_has_nation ?t_nation .
            ?t_nation ltpch:n_name "%NATION%" .
          }
      }
      filter (?bigpsvalue > ?threshold )
  }
order by
  desc (?bigpsvalue)

          

Query 12


select
  ?l_shipmode
  (sum (
      xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
      fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
  (sum (1 -
      xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
      fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where  {
    
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_has_order ?ord ;
       ltpch:l_commitdate ?l_commitdate ;
       ltpch:l_receiptdate ?l_receiptdate ;
       ltpch:l_shipmode ?l_shipmode ;
       ltpch:l_shipdate ?l_shipdate .
    ?ord ltpch:o_orderpriority ?o_orderpriority .
    filter (?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%") &&
      (xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
      (xsd:date(?l_shipdate) < xsd:date(?l_commitdate)) &&
      (xsd:date(?l_receiptdate) >= "%YEAR%-01-01"^^xsd:date) &&
      (xsd:date(?l_receiptdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) )
  }
group by
  ?l_shipmode
order by
  ?l_shipmode

          

Query 13


select
  ?c_count
  (count(1) as ?custdist)
where {
    { select
        ?c_custkey
        (count (?ord) as ?c_count)
      where
        {
          ?cust ltpch:c_custkey ?c_custkey .
           optional {
             ?ord a ltpch:orders ;
                  ltpch:o_has_customer ?cust ;
                  ltpch:o_comment ?o_comment .
              filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) . 
          }
        }
      group by 
        ?c_custkey
    }
  }
group by
  ?c_count
order by
  desc (count(1))
  desc (?c_count)

          

Query 14


select
       ((100 * sum(xsd:integer(fn:starts-with(?p_type, "PROMO")) * xsd:decimal(?l_lineextendedprice) *  (xsd:decimal(1) - xsd:decimal(?l_linediscount)))  / sum(xsd:decimal(?l_lineextendedprice) *  (xsd:decimal(1) - xsd:decimal(?l_linediscount)))) as ?promo_revenue)
where
{

    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount ;
        ltpch:l_shipdate ?l_shipdate ;
        ltpch:l_has_partsupplier ?ps .
    ?ps ltpch:ps_has_part ?part .
    ?part ltpch:p_type ?p_type .
    filter (xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
      (xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P1M"^^xsd:duration)) )

}

          

Query 15


select
  ?s_suppkey
  ?s_name
  ?s_address
  ?s_phone
  ?total_revenue
where  {
    ?supplier a ltpch:supplier ;
        ltpch:s_suppkey ?s_suppkey ;
        ltpch:s_name ?s_name ;
        ltpch:s_address ?s_address ;
        ltpch:s_phone ?s_phone .
    { select
          ?supplier
          (sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount))) as ?total_revenue)
       where {
            ?li1 qb:dataSet ltpch:lineitemCube ;
                 ltpch:l_shipdate ?l_shipdate ;
                 ltpch:l_lineextendedprice ?l_extendedprice ;
                 ltpch:l_linediscount ?l_discount ;
                 ltpch:l_has_partsupplier ?ps1 .
            ?ps1 ltpch:ps_has_supplier ?supplier .
            filter (
                xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
                xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
        }
      group by
        ?supplier
      }
      { select (max (?l2_total_revenue) as ?maxtotal)
        where {
            { select
                  ?supplier2
                  (sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount))) as ?l2_total_revenue)
               where {
                    ?li2 qb:dataSet ltpch:lineitemCube ;
                      ltpch:l_shipdate ?l2_shipdate ;
                      ltpch:l_lineextendedprice ?l2_extendedprice ;
                      ltpch:l_linediscount ?l2_discount ;
                       ltpch:l_has_partsupplier ?ps2 .
                  ?ps2 ltpch:ps_has_supplier ?supplier2 .
                    filter (
                        xsd:date(?l2_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
                        xsd:date(?l2_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
               }
               group by 
                ?supplier2
            }
        }
    }
    filter (?total_revenue = ?maxtotal)
}
order by
  ?supplier

          

Query 16


select
  ?p_brand
  ?p_type
  ?p_size
  (count(distinct ?supp) as ?supplier_cnt)
where {
    ?ps a ltpch:partsupp ;
        ltpch:ps_has_part ?part ;
        ltpch:ps_has_supplier ?supp .
    ?part ltpch:p_brand ?p_brand ;
        ltpch:p_type ?p_type ;
        ltpch:p_size ?p_size .    
    filter (
      (?p_brand != "%BRAND%") &&
      !(fn:starts-with(?p_type,"%TYPE%")) &&
      (xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
    )
    filter NOT EXISTS {
       ?supp a ltpch:supplier;
             ltpch:s_comment ?badcomment .
       filter ( fn:matches (?badcomment ,"Customer.*Complaints") )
    }
  }
group by
  ?p_brand
  ?p_type
  ?p_size
order by
  desc ((count(distinct ?supp)))
  ?p_brand
  ?p_type
  ?p_size

          

Query 17


select
  ((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_has_partsupplier ?ps .
    ?ps ltpch:ps_has_part ?part .
    ?part ltpch:p_brand ?p_brand ;
          ltpch:p_container ?p_container .
          {
            select 
              ?part
              ((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
            where { 
              ?li2  a ltpch:lineitem ;
                    ltpch:l_linequantity ?l2_linequantity ; 
                    ltpch:l_has_partsupplier ?ps2 .
              ?ps2  ltpch:ps_has_part ?part .
          } 
          group by
            ?part
        }
    filter (xsd:decimal(?l_linequantity) < ?threshold && REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%") 
}
          

Query 18


select
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
   (sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
    ?li qb:dataSet ltpch:lineitemCube  ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_has_order ?ord .
    ?ord ltpch:o_orderkey ?o_orderkey ;
         ltpch:o_orderdate ?o_orderdate ;
         ltpch:o_ordertotalprice ?o_ordertotalprice ;
         ltpch:o_has_customer ?cust .
    ?cust ltpch:c_custkey ?c_custkey ;
          ltpch:c_name ?c_name .  
    { select 
         ?ord 
         (sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
       where {
           ?li2 qb:dataSet ltpch:lineitemCube ;
                ltpch:l_linequantity ?l2_linequantity ;
                ltpch:l_has_order ?ord .
       }
       group by ?ord 
    } .
    filter (xsd:decimal(?sum_q) > xsd:decimal(%QUANTITY%))
}
group by
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
order by
  desc (?o_ordertotalprice)
  ?o_orderdate
limit 100

          

Query 19


select
  ((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_shipmode ?l_shipmode ;
        ltpch:l_shipinstruct ?l_shipinstruct ;
        ltpch:l_has_partsupplier ?ps .
    ?ps ltpch:ps_has_part ?part .
     ?part ltpch:p_brand ?p_brand ;
          ltpch:p_size ?p_size ;
          ltpch:p_container ?p_container .
     
     filter (?l_shipmode in ("AIR", "AIR REG") &&
      ?l_shipinstruct = "DELIVER IN PERSON" &&
      ( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
          (?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
          (xsd:integer(?l_linequantity) <= (%QUANTITY1% + 10)) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
        ( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
          (?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY2%) && 
          (xsd:integer(?l_linequantity) <= (%QUANTITY2% + 10)) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
        ( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
          (?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
          (xsd:integer(?l_linequantity) <= (%QUANTITY3% + 10)) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
  }

          

Query 20


select
  ?s_name
  ?s_address
where
{
  ?supp ltpch:s_name ?s_name ;
        ltpch:s_address ?s_address .
  { 
    select distinct 
      ?supp 
    where 
    {
      ?big_ps ltpch:ps_has_part ?part ;
              ltpch:ps_availqty ?big_ps_availqty ;
              ltpch:ps_has_supplier ?supp .
      ?supp ltpch:s_has_nation ?s_nation .
      ?s_nation ltpch:n_name ?n_name .
      ?part ltpch:p_name ?p_name . 
      filter (REGEX (?p_name , "^%COLOR%") && 
              ?n_name = "%NATION%" && 
              xsd:decimal(?big_ps_availqty) > ?qty_threshold)
      {
        select 
          ((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
          ?big_ps
        where
        {
          ?li qb:dataSet ltpch:lineitemCube ;
              ltpch:l_shipdate ?l_shipdate ;
              ltpch:l_linequantity ?l_linequantity ;
              ltpch:l_has_partsupplier ?big_ps .
          filter ((xsd:date(?l_shipdate) >= xsd:date("%YEAR%-01-01"^^xsd:date)) &&
            (xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration))
          )
        }
        group by 
          ?big_ps
      }
    } 
  }
}
order by ?s_name

          

Query 21


select
    ?s_name
    (count(1) as ?numwait)
where {
          ?li1 qb:dataSet ltpch:lineitemCube;
              ltpch:l_receiptdate ?l1_receiptdate ;
              ltpch:l_commitdate ?l1_commitdate ;
              ltpch:l_has_partsupplier ?ps ;
              ltpch:l_has_order ?ord .
          ?ps ltpch:ps_has_supplier ?supp .
          ?supp ltpch:s_name ?s_name ;
               ltpch:s_has_nation ?s_nation .
          ?ord ltpch:o_orderstatus ?orderstatus .
          ?s_nation ltpch:n_name ?name
          filter ( 
            xsd:date(?l1_receiptdate) > xsd:date(?l1_commitdate) && 
            ?name = "%NATION%" && 
            ?orderstatus = "F"
            ) 
          filter exists {
            ?li2 ltpch:l_has_order ?ord ;
                 ltpch:l_has_partsupplier ?ps2 .
            ?ps2 ltpch:ps_has_supplier ?supp2 .
            filter (?supp != ?supp2)
          }
          filter not exists {
              ?li3 ltpch:l_has_order ?ord ;
                   ltpch:l_receiptdate ?l3_receiptdate ;
                   ltpch:l_commitdate ?l3_commitdate ;
                   ltpch:l_has_partsupplier ?ps3 .
              ?ps3 ltpch:ps_has_supplier ?supp3 .
              filter (
                 xsd:date(?l3_receiptdate) > xsd:date(?l3_commitdate) &&
                 ?supp3 != ?supp
              )
         }
       }
group by
   ?s_name
order by
    desc (count(1))
    ?s_name
limit 100

          

Query 22


select
  ?cntrycode
  (count (1) as ?numcust)
  (sum (xsd:decimal(?c_acctbal)) as ?totacctbal)
where {
    ?cust a ltpch:customer ;
      ltpch:c_acctbal ?c_acctbal ;
      ltpch:c_phone ?c_phone .
      BIND (fn:substring(?c_phone,0, 3) as ?cntrycode)
    {
      select (avg (xsd:decimal(?c_acctbal2)) as ?acctbal_threshold)
          where
            {
              ?cust2 a ltpch:customer ;
                 ltpch:c_acctbal ?c_acctbal2 ;
                 ltpch:c_phone ?c_phone2 .
              filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
                fn:substring(?c_phone2,0, 3) in (%COUNTRY_CODE_SET%) )
            }
    }
    filter (
      ?cntrycode in (%COUNTRY_CODE_SET%) &&
      (xsd:decimal(?c_acctbal) > ?acctbal_threshold ) )
    filter not exists { ?ord ltpch:o_has_customer ?cust }
  }
group by ?cntrycode
order by ?cntrycode

          
Jena queries star pattern

Prefix


prefix xsd: <http://www.w3.org/2001/XMLSchema#> 
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
          

Query 1


              select
  ?l_returnflag 
  ?l_linestatus 
  (sum(xsd:decimal(?l_linequantity)) as ?sum_qty) 
  (sum(xsd:decimal(?l_lineextendedprice)) as ?sum_base_price) 
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?sum_disc_price) 
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax))) as ?sum_charge) 
  (avg(xsd:decimal(?l_linequantity)) as ?avg_qty) 
  (avg(xsd:decimal(?l_lineextendedprice)) as ?avg_price) 
  (avg(xsd:decimal(?l_linediscount)) as ?avg_disc) 
  (count(1) as ?count_order)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_returnflag ?l_returnflag ;
       ltpch:l_linestatus ?l_linestatus ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linetax ?l_linetax ;
       ltpch:l_shipdate ?l_shipdate ;
       ltpch:l_linediscount ?l_linediscount .
    filter (xsd:date(?l_shipdate) <= ("1998-12-01"^^xsd:date + "-P%DELTA%D"^^xsd:duration))
} 
group by
  ?l_returnflag
  ?l_linestatus
order by
  ?l_returnflag
  ?l_linestatus

            

Query 2


select
  ?s_acctbal
  ?s_name
  ?nation_name
  ?p_partkey
  ?p_mfgr
  ?s_address
  ?s_phone
  ?s_comment
where {
  ?ps ltpch:supplier_acctbal ?s_acctbal ;
    ltpch:supplier_name ?s_name ;
    ltpch:supplier_address ?s_address ;
    ltpch:supplier_phone ?s_phone ;
    ltpch:supplier_comment ?s_comment ;
    ltpch:nation_name ?nation_name ;
    ltpch:region_name "%REGION%" ;
    ltpch:part_partkey ?p_partkey ;
    ltpch:part_mfgr ?p_mfgr ;
    ltpch:part_size ?size ;
    ltpch:part_type ?p_type .
  FILTER (?size = str(%SIZE%) && fn:contains(?p_type, "%TYPE%"))
  { select ?p_partkey  (min(?s_cost) as ?minsc)
    where {
        ?ps ltpch:part_partkey ?p_partkey;
            ltpch:partsupplier_supplycost ?s_cost ;
            ltpch:region_name ?region2 .
            filter (?region2 = "%REGION%")
      } 
      group by ?p_partkey 
    }
     
  }
order by
  desc (?s_acctbal)
  ?nation_name
  ?s_name
  ?p_partkey
limit 100

            

Query 3


select
  ?o_orderkey
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?o_orderdate
  ?o_shippriority
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
    ltpch:l_lineextendedprice ?l_lineextendedprice ;
    ltpch:l_linediscount ?l_linediscount ;
    ltpch:l_has_order ?ord ;
    ltpch:l_shipdate ?l_shipdate .
  ?ord ltpch:order_orderdate ?o_orderdate ;
    ltpch:order_shippriority ?o_shippriority ;
    ltpch:order_orderkey ?o_orderkey ;
    ltpch:customer_mktsegment ?c_mktsegment .
  filter ((xsd:date(?o_orderdate) < "%DATE%"^^xsd:date) &&
    (xsd:date(?l_shipdate) > "%DATE%"^^xsd:date) &&
    (?c_mktsegment = "%SEGMENT%") ) 
}
group by
  ?o_orderkey
  ?o_orderdate
  ?o_shippriority
order by
  desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
  ?o_orderdate
limit 10

            

Query 4


select
  ?o_orderpriority
  (count(*) as ?order_count)
where  
{
  {
    select distinct
      ?o_orderpriority
      ?ordkey
    where 
    {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_has_order ?ord ;
          ltpch:l_commitdate ?l_commitdate ;
          ltpch:l_receiptdate ?l_receiptdate .
      ?ord ltpch:order_orderpriority ?o_orderpriority ;
           ltpch:order_orderkey ?ordkey ;
           ltpch:order_orderdate ?o_orderdate .
      filter (
        (xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
        (xsd:date(?o_orderdate) >= "%MONTH%-01"^^xsd:date) &&
        (xsd:date(?o_orderdate) < ("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration))
      )
    }
  }
}
group by
  ?o_orderpriority
order by
  ?o_orderpriority

            

Query 5


select
  ?nation
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where  {
   ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_has_order ?ord ;
       ltpch:l_has_partsupplier ?ps ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount .
    ?ord ltpch:order_orderdate ?o_orderdate ;
         ltpch:nation_name ?c_nation .
    ?ps ltpch:nation_name ?nation ;
        ltpch:region_name ?r_name .
    
    filter ((?c_nation = ?nation) &&
      (xsd:date(?o_orderdate) >= "%YEAR%-01-01"^^xsd:date) &&
      (xsd:date(?o_orderdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) &&
      (?r_name = "%REGION%") ) 
  }
group by
  ?nation
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))

            

Query 6


select
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_shipdate ?l_shipdate .
    filter ( (xsd:date(?l_shipdate) >= ("%YEAR%-01-01"^^xsd:date)) &&
      (xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) &&
      (xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
      (xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
      (xsd:decimal(?l_linequantity) < %QUANTITY%) ) 
}

            

Query 7


select 
  ?supp_nation 
  ?cust_nation 
  ?li_year
  (sum (xsd:decimal(?volume)) as ?revenue)
where {
  {
    select
      ?supp_nation
      ?cust_nation
      ?li_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_has_order ?ord ;
        ltpch:l_has_partsupplier ?ps ;
        ltpch:l_shipdate ?l_shipdate ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount .
      ?ord ltpch:nation_name ?cust_nation .
      ?ps ltpch:nation_name ?supp_nation .
      BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
filter (xsd:boolean(
        (?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
        (?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
        xsd:boolean(xsd:date(?l_shipdate) >= xsd:date("1995-01-01"^^xsd:date)) &&
        xsd:boolean(xsd:date(?l_shipdate) <= xsd:date("1996-12-31"^^xsd:date)) )  
      } 
   } 
}
group by
  ?supp_nation
  ?cust_nation
  ?li_year
order by
  ?supp_nation
  ?cust_nation
  ?li_year

            

Query 8


select
  ?o_year
  ((?sum1 / ?sum2) as ?mkt_share)
where {
  { select
    ?o_year
    (sum (?volume * xsd:integer(fn:starts-with(?nation, "%NATION%"))) as ?sum1)
    (sum (?volume) as ?sum2)
    where {
      { select
           ((YEAR(xsd:date(?o_orderdate))) as ?o_year)
           ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
           ?nation
         where {
           ?li qb:dataSet ltpch:lineitemCube ;
               ltpch:l_has_partsupplier ?ps ;
               ltpch:l_has_order ?ord ;
               ltpch:l_has_partsupplier ?ps ;
               ltpch:l_lineextendedprice ?l_lineextendedprice ;
               ltpch:l_linediscount ?l_linediscount .
           ?ps ltpch:nation_name ?nation ;
               ltpch:part_type ?type .
           ?ord ltpch:order_orderdate ?o_orderdate ;
                ltpch:region_name ?region .
           filter ((xsd:date(?o_orderdate) >= "1995-01-01"^^xsd:date) &&
             (xsd:date(?o_orderdate) <= "1996-12-31"^^xsd:date) &&
              (?region = "%REGION%") &&
              (?type = "%TYPE%") 
           ) 
        } 
      } 
    }
    group by
      ?o_year 
  } 
}
order by
  ?o_year

            

Query 9


select
  ?nation
  ?o_year
  (sum(?amount) as ?sum_profit)
where {
  { select
      ?nation
      ?o_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_has_order ?ord ;
          ltpch:l_has_partsupplier ?ps ;
          ltpch:l_linequantity ?l_linequantity ;
          ltpch:l_lineextendedprice ?l_lineextendedprice ;
          ltpch:l_linediscount ?l_linediscount .
      ?ps ltpch:nation_name ?nation ;
          ltpch:partsupplier_supplycost ?ps_supplycost ;
          ltpch:part_name ?p_name .
      ?ord ltpch:order_orderdate ?o_orderdate .
      filter (REGEX (?p_name, "%COLOR%"))
      BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
    } 
  } 
}
group by
  ?nation
  ?o_year
order by
  ?nation
  desc (?o_year)
            

Query 10


select
  ?c_custkey
  ?c_companyName
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
      ltpch:l_returnflag ?l_returnflag ;
      ltpch:l_has_order ?ord ;
      ltpch:l_lineextendedprice ?l_lineextendedprice ;
      ltpch:l_linediscount ?l_linediscount .
  ?ord ltpch:order_orderdate ?o_orderdate ;
      ltpch:customer_address ?c_address ;
      ltpch:customer_phone ?c_phone ;
      ltpch:customer_comment ?c_comment ;
      ltpch:customer_acctbal ?c_acctbal ;
      ltpch:customer_custkey ?c_custkey ;
      ltpch:customer_name ?c_companyName ;
      ltpch:nation_name ?nation .
   filter ((xsd:date(?o_orderdate) >= xsd:date("%MONTH%-01"^^xsd:date)) &&
      (xsd:date(?o_orderdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration)) &&
      (?l_returnflag = "R") 
   ) }
group by
  ?c_custkey
  ?c_companyName
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20

            

Query 11


select
  ?bigpspart
  ?bigpsvalue
where {
      { select
          ?bigpspart
          (sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue)
        where
          {
            ?bigps ltpch:part_partkey ?bigpspart ;
                  ltpch:partsupplier_supplycost ?b_supplycost ;
                  ltpch:partsupplier_availqty ?b_availqty ;
                  ltpch:nation_name "%NATION%" .
          }
          group by ?bigpspart
      }
      {
        select
          (sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty)) * %FRACTION% as ?threshold)
        where
          {
            ?thr_ps ltpch:partsupplier_supplycost ?t_supplycost ;
                    ltpch:partsupplier_availqty ?t_availqty ;
                    ltpch:nation_name "%NATION%" .
          }
      }
      filter (?bigpsvalue > ?threshold )
  }
order by
  desc (?bigpsvalue)

          

Query 12


select
  ?l_shipmode
  (sum (
      xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
      fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
  (sum (1 -
      xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
      fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where  {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_has_order ?ord ;
       ltpch:l_commitdate ?l_commitdate ;
       ltpch:l_receiptdate ?l_receiptdate ;
       ltpch:l_shipmode ?l_shipmode ;
       ltpch:l_shipdate ?l_shipdate .
    ?ord ltpch:order_orderpriority ?o_orderpriority .
    filter (?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%") &&
      (xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
      (xsd:date(?l_shipdate) < xsd:date(?l_commitdate)) &&
      (xsd:date(?l_receiptdate) >= "%YEAR%-01-01"^^xsd:date) &&
      (xsd:date(?l_receiptdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) )
  }
group by
  ?l_shipmode
order by
  ?l_shipmode

          

Query 13


select
  ?c_count
  (count(1) as ?custdist)
where {
    { select
        ?c_custkey
        (count (?o_comment) as ?c_count)
      where
        {
          ?ord ltpch:customer_custkey ?c_custkey .
           optional {
             ?ord ltpch:order_comment ?o_comment .
              filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) . 
          }
        }
      group by 
        ?c_custkey
    }
  }
group by
  ?c_count
order by
  desc (count(1))
  desc (?c_count)

          

Query 14


  select 
    ((100 * sum(xsd:integer(fn:starts-with(?p_type, "PROMO")) * xsd:decimal(?l_lineextendedprice) *  (xsd:decimal(1) - xsd:decimal(?l_linediscount)))  / sum(xsd:decimal(?l_lineextendedprice) *  (xsd:decimal(1) - xsd:decimal(?l_linediscount)))) as ?promo_revenue)
  where {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_lineextendedprice ?l_lineextendedprice ;
          ltpch:l_linediscount ?l_linediscount ;
          ltpch:l_shipdate ?l_shipdate ;
          ltpch:l_has_partsupplier ?part .
      ?part ltpch:part_type ?p_type .
      filter (xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
      (xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P1M"^^xsd:duration)) )
  }

          

Query 15


select distinct
  ?s_suppkey
  ?s_name
  ?s_address
  ?s_phone
  ?total_revenue
where  
{
  ?partsupp ltpch:supplier_suppkey ?s_suppkey ;
            ltpch:supplier_name ?s_name ;
            ltpch:supplier_address ?s_address ;
            ltpch:supplier_phone ?s_phone .
  { 
    select
        ?s_suppkey
        ((sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount)))) as ?total_revenue)
    where 
    {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_shipdate ?l_shipdate ;
          ltpch:l_lineextendedprice ?l_extendedprice ;
          ltpch:l_linediscount ?l_discount ;
          ltpch:l_has_partsupplier ?ps .
      ?ps ltpch:supplier_suppkey ?s_suppkey .
      filter (
                xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
                xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
    }
    group by
      ?s_suppkey
  } .
  { 
    select 
      (max (?l2_total_revenue) as ?maxtotal)
    where 
    {
      { 
        select
          ((sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount)))) as ?l2_total_revenue)
        where 
        {
          ?li2 qb:dataSet ltpch:lineitemCube ;
              ltpch:l_shipdate ?l2_shipdate ;
              ltpch:l_lineextendedprice ?l2_extendedprice ;
              ltpch:l_linediscount ?l2_discount ;
              ltpch:l_has_partsupplier ?ps2 .
          ?ps2 ltpch:supplier_suppkey ?s_suppkey2 .
          filter (
                        xsd:date(?l2_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
                        xsd:date(?l2_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
        }
        group by
          ?s_suppkey2
      }
    }
  }
  filter (?total_revenue = ?maxtotal)
}
order by
  ?s_suppkey

          

Query 16


select
  ?p_brand
  ?p_type
  ?p_size
  (count(distinct ?supp) as ?supplier_cnt)
where {
    ?ps ltpch:part_brand ?p_brand ;
        ltpch:part_type ?p_type ;
        ltpch:part_size ?p_size ;   
        ltpch:supplier_suppkey ?supp .    
    filter (
      (?p_brand != "%BRAND%") &&
      !(fn:starts-with(?p_type,"%TYPE%")) &&
      (xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
    )
    filter NOT EXISTS {
       ?supp a ltpch:supplier;
             ltpch:s_comment ?badcomment .
       filter ( fn:matches (?badcomment ,"Customer.*Complaints") )
    }
  }
group by
  ?p_brand
  ?p_type
  ?p_size
order by
  desc ((count(distinct ?supp)))
  ?p_brand
  ?p_type
  ?p_size

          

Query 17


select
  ((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_has_partsupplier ?ps .
    ?ps ltpch:part_partkey ?p_partkey.
    {
      select 
        ?p_partkey
        ((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
      where { 
        ?li2  a ltpch:lineitem ;
              ltpch:l_linequantity ?l2_linequantity ; 
              ltpch:l_has_partsupplier ?ps2 .
        ?ps2 ltpch:part_partkey ?p_partkey ;
              ltpch:part_container ?p_container ;
              ltpch:part_brand ?p_brand  .
      }
      group by
        ?p_partkey
    }
    filter (xsd:decimal(?l_linequantity) < ?threshold && REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%") 
}
          

Query 18


select
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
   (sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_has_order ?ord .
    ?ord ltpch:order_orderkey ?o_orderkey ;
        ltpch:order_orderdate ?o_orderdate ;
        ltpch:order_ordertotalprice ?o_ordertotalprice ;
        ltpch:customer_custkey ?c_custkey ;
        ltpch:customer_name ?c_name .
    { 
      select 
        ?ord 
        (sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
      where 
      {
        ?li2 a ltpch:lineitem ;
             ltpch:l_linequantity ?l2_linequantity ;
             ltpch:l_has_order ?ord .
      }
      group by
        ?ord
    } .
    filter (xsd:decimal(?sum_q) > xsd:decimal(%QUANTITY%))
}
group by
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
order by
  desc (?o_ordertotalprice)
  ?o_orderdate
limit 100

          

Query 19


select
  ((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_has_partsupplier ?ps ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_shipmode ?l_shipmode ;
        ltpch:l_shipinstruct ?l_shipinstruct .
     ?ps ltpch:part_brand ?p_brand ;
          ltpch:part_size ?p_size ;
          ltpch:part_container ?p_container .
     

     filter (?l_shipmode in ("AIR", "AIR REG") &&
      ?l_shipinstruct = "DELIVER IN PERSON" &&
      ( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
          (?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
          (xsd:integer(?l_linequantity) <= %QUANTITY1% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
        ( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
          (?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY2%) && 
          (xsd:integer(?l_linequantity) <= %QUANTITY2% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
        ( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
          (?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
          (xsd:integer(?l_linequantity) <= %QUANTITY3% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
  }

          

Query 20


select distinct
  ?s_name
  ?s_address
where
{
  ?supp ltpch:supplier_name ?s_name ;
        ltpch:supplier_suppkey ?suppkey ;
        ltpch:supplier_address ?s_address .
  { 
    select 
      distinct ?suppkey 
    where 
    {
      ?big_ps ltpch:partsupplier_availqty ?big_ps_availqty ;
              ltpch:supplier_suppkey ?suppkey ;
              ltpch:nation_name ?n_name ;
              ltpch:part_name ?p_name . 
      filter (REGEX (?p_name , "^%COLOR%") && 
              ?n_name = "%NATION%" && 
              xsd:decimal(?big_ps_availqty) > ?qty_threshold)
      {
        select 
          ?big_ps
          ((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
        where
        {
          ?li qb:dataSet ltpch:lineitemCube ;
              ltpch:l_shipdate ?l_shipdate ;
              ltpch:l_linequantity ?l_linequantity ;
              ltpch:l_has_partsupplier ?big_ps .
          filter ((xsd:date(?l_shipdate) >= xsd:date("%YEAR%-01-01"^^xsd:date)) &&
            (xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration))
          )
        } 
        group by
          ?big_ps
      } .
    } 
  }
}
order by ?s_name

          

Query 21


select
    ?s_name
    ((count(1)) as ?numwait)
where {
         ?li1 qb:dataSet ltpch:lineitemCube ;
              ltpch:l_receiptdate ?l1_receiptdate ;
              ltpch:l_commitdate ?l1_commitdate ;
              ltpch:l_has_partsupplier ?ps ;
              ltpch:l_has_order ?ord .
         ?ps ltpch:supplier_name ?s_name ;
             ltpch:supplier_suppkey ?suppkey ;
             ltpch:nation_name ?n_name .
         ?ord ltpch:order_orderstatus ?o_orderstatus .
         filter ( 
            xsd:date(?l1_receiptdate) > xsd:date(?l1_commitdate) && 
            ?n_name = "%NATION%" && 
            ?o_orderstatus = "F"
            ) 
         filter exists {
              ?li2 ltpch:l_has_order ?ord ;
                   ltpch:l_has_partsupplier ?ps2 .
              ?ps2 ltpch:supplier_suppkey ?suppkey2 .
              filter (?suppkey != ?suppkey2)
         }
         filter not exists {
              ?li3 ltpch:l_has_order ?ord ;
                   ltpch:l_receiptdate ?l3_receiptdate ;
                   ltpch:l_commitdate ?l3_commitdate ;
                   ltpch:l_has_partsupplier ?ps3 .
              ?ps3 ltpch:supplier_suppkey ?suppkey3 .
              filter (
                 xsd:date(?l3_receiptdate) > xsd:date(?l3_commitdate) &&
                 ?suppkey3 != ?suppkey
              )
         }
       }
group by
   ?s_name
order by
    desc (count(1))
    ?s_name
limit 100

          

Query 22


select
  ?cntrycode
  (count (1) as ?numcust)
  (sum (xsd:decimal(?c_acctbal)) as ?totacctbal)
where {
    ?cust ltpch:customer_acctbal ?c_acctbal ;
      ltpch:customer_phone ?c_phone .
      BIND (fn:substring(?c_phone,0, 3) as ?cntrycode)
    {
        select (avg (xsd:decimal(?c_acctbal2)) as ?acctbal_threshold)
          where
            {
              ?cust2 ltpch:customer_acctbal ?c_acctbal2 ;
                 ltpch:customer_phone ?c_phone2 .
              filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
                fn:substring(?c_phone2,0, 3) in (%COUNTRY_CODE_SET%)  )
            }
          }
    filter (
      ?cntrycode in (%COUNTRY_CODE_SET%) &&
      (xsd:decimal(?c_acctbal) > ?acctbal_threshold ) )
    filter not exists { ?cust ltpch:order_orderkey ?orderkey }
  }
group by ?cntrycode
order by ?cntrycode
          
Jena queries denormalized pattern

Prefix


prefix qb4o: <http://publishing-multidimensional-data.googlecode.com/git/index.html#ref_qbplus_>
prefix qb:   <http://purl.org/linked-data/cube#>
prefix ltpch: <http://extbi.lab.aau.dk/ontology/ltpch/>
prefix xsd: <http://www.w3.org/2001/XMLSchema#> 
prefix fn: <http://www.w3.org/2005/xpath-functions#>

          

Query 1


            select
  ?l_returnflag 
  ?l_linestatus 
  (sum(xsd:decimal(?l_linequantity)) as ?sum_qty) 
  (sum(xsd:decimal(?l_lineextendedprice)) as ?sum_base_price) 
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?sum_disc_price) 
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))*(1 + xsd:decimal(?l_linetax))) as ?sum_charge) 
  (avg(xsd:decimal(?l_linequantity)) as ?avg_qty) 
  (avg(xsd:decimal(?l_lineextendedprice)) as ?avg_price) 
  (avg(xsd:decimal(?l_linediscount)) as ?avg_disc) 
  (count(1) as ?count_order)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_returnflag ?l_returnflag ;
       ltpch:l_linestatus ?l_linestatus ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linetax ?l_linetax ;
       ltpch:l_shipdate ?l_shipdate ;
       ltpch:l_linediscount ?l_linediscount .
    filter (xsd:date(?l_shipdate) <= ("1998-12-01"^^xsd:date + "-P%DELTA%D"^^xsd:duration))
} 
group by
  ?l_returnflag
  ?l_linestatus
order by
  ?l_returnflag
  ?l_linestatus

Query 2


select distinct
  ?s_acctbal
  ?s_name
  ?nation_name
  ?p_partkey
  ?p_mfgr
  ?s_address
  ?s_phone
  ?s_comment
where {
  ?li ltpch:partsupplier_partsupplier_supplycost ?minsc ;
    ltpch:partsupplier_supplier_acctbal ?s_acctbal ;
    ltpch:partsupplier_supplier_name ?s_name ;
    ltpch:partsupplier_supplier_address ?s_address ;
    ltpch:partsupplier_supplier_phone ?s_phone ;
    ltpch:partsupplier_supplier_comment ?s_comment ;
    ltpch:partsupplier_nation_name ?nation_name ;
    ltpch:partsupplier_region_name "%REGION%" ;
    ltpch:partsupplier_part_partkey ?p_partkey ;
    ltpch:partsupplier_part_mfgr ?p_mfgr ;
    ltpch:partsupplier_part_size ?size ;
    ltpch:partsupplier_part_type ?p_type .
  FILTER (?size = str(%SIZE%) && fn:contains(?p_type, "%TYPE%"))
  { select ?p_partkey  (min(?s_cost) as ?minsc)
    where {
        ?li ltpch:partsupplier_part_partkey ?p_partkey;
            ltpch:partsupplier_partsupplier_supplycost ?s_cost ;
            ltpch:partsupplier_region_name ?region2 .
            filter (?region2 = "%REGION%")
      } 
      group by ?p_partkey 
    }
     
  }
order by
  desc (?s_acctbal)
  ?nation_name
  ?s_name
  ?p_partk             
            

Query 3


select
  ?o_orderkey
  (sum(xsd:decimal(?l_lineextendedprice)*(1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?o_orderdate
  ?o_shippriority
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
    ltpch:l_lineextendedprice ?l_lineextendedprice ;
    ltpch:l_linediscount ?l_linediscount ;
    ltpch:l_shipdate ?l_shipdate ;
    ltpch:order_order_orderdate ?o_orderdate ;
    ltpch:order_order_shippriority ?o_shippriority ;
    ltpch:order_order_orderkey ?o_orderkey ;
    ltpch:order_customer_mktsegment ?c_mktsegment .
  filter ((xsd:date(?o_orderdate) < "%DATE%"^^xsd:date) &&
    (xsd:date(?l_shipdate) > "%DATE%"^^xsd:date) &&
    (?c_mktsegment = "%SEGMENT%") ) 
}
group by
  ?o_orderkey
  ?o_orderdate
  ?o_shippriority
order by
  desc (sum (xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
  ?o_orderdate
limit 10

            

Query 4


select
  ?o_orderpriority
  (count(*) as ?order_count)
where  
{
  {
    select distinct
      ?o_orderpriority
      ?ordkey
    where 
    {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_commitdate ?l_commitdate ;
          ltpch:l_receiptdate ?l_receiptdate ;
          ltpch:order_order_orderpriority ?o_orderpriority ;
          ltpch:order_order_orderkey ?ordkey ;
          ltpch:order_order_orderdate ?o_orderdate .
      filter (
        (xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
        (xsd:date(?o_orderdate) >= "%MONTH%-01"^^xsd:date) &&
        (xsd:date(?o_orderdate) < ("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration))
      )
    }
  }
}
group by
  ?o_orderpriority
order by
  ?o_orderpriority

            

Query 5


select
  ?nation
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where  {
   ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount ;
       ltpch:order_order_orderdate ?o_orderdate ;
       ltpch:order_nation_name ?c_nation ;
       ltpch:partsupplier_nation_name ?nation ;
       ltpch:partsupplier_region_name ?r_name .
    
    filter ((?c_nation = ?nation) &&
      (xsd:date(?o_orderdate) >= "%YEAR%-01-01"^^xsd:date) &&
      (xsd:date(?o_orderdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) &&
      (?r_name = "%REGION%") ) 
  }
group by
  ?nation
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))

            

Query 6


select
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_lineextendedprice ?l_lineextendedprice ;
       ltpch:l_linediscount ?l_linediscount ;
       ltpch:l_linequantity ?l_linequantity ;
       ltpch:l_shipdate ?l_shipdate .
    filter ( (xsd:date(?l_shipdate) >= "%YEAR%-01-01"^^xsd:date) &&
      (xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) &&
      (xsd:decimal(?l_linediscount) >= %DISCOUNT% - 0.01) &&
      (xsd:decimal(?l_linediscount) <= %DISCOUNT% + 0.01) &&
      (xsd:decimal(?l_linequantity) < %QUANTITY%) ) 
}
            

Query 7


select 
  ?supp_nation 
  ?cust_nation 
  ?li_year
  (sum (xsd:decimal(?volume)) as ?revenue)
where {
  {
    select
      ?supp_nation
      ?cust_nation
      ?li_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_shipdate ?l_shipdate ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount ;
        ltpch:order_nation_name ?cust_nation ;
        ltpch:partsupplier_nation_name ?supp_nation .
      BIND (SUBSTR(STR(?l_shipdate), 1,4) as ?li_year)
filter (xsd:boolean(
        (?cust_nation = "%NATION1%" && ?supp_nation = "%NATION2%") ||
        (?cust_nation = "%NATION2%" && ?supp_nation = "%NATION1%") ) &&
        xsd:boolean(xsd:date(?l_shipdate) >= xsd:date("1995-01-01"^^xsd:date)) &&
        xsd:boolean(xsd:date(?l_shipdate) <= xsd:date("1996-12-31"^^xsd:date)) ) 
      } 
   } 
}
group by
  ?supp_nation
  ?cust_nation
  ?li_year
order by
  ?supp_nation
  ?cust_nation
  ?li_year

            

Query 8


select
  ?o_year
  ((?sum1 / ?sum2) as ?mkt_share)
where {
  { select
    ?o_year
    (sum (?volume * xsd:integer(fn:starts-with(?nation, "%NATION%"))) as ?sum1)
    (sum (?volume) as ?sum2)
    where {
      { select
           ((YEAR(xsd:date(?o_orderdate))) as ?o_year)
           ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?volume)
           ?nation
         where {
           ?li qb:dataSet ltpch:lineitemCube ;
               ltpch:l_has_partsupplier ?ps ;
               ltpch:l_lineextendedprice ?l_lineextendedprice ;
               ltpch:l_linediscount ?l_linediscount ;
               ltpch:partsupplier_nation_name ?nation ;
               ltpch:partsupplier_part_type ?type ;
               ltpch:order_order_orderdate ?o_orderdate ;
               ltpch:order_region_name ?region .
           filter ((xsd:date(?o_orderdate) >= xsd:date("1995-01-01"^^xsd:date)) &&
             (xsd:date(?o_orderdate) <= xsd:date("1996-12-31"^^xsd:date)) &&
              (?region = "%REGION%") &&
              (?type = "%TYPE%") 
           ) 
        } 
      } 
    }
    group by
      ?o_year 
  } 
}
order by
  ?o_year

            

Query 9


select
  ?nation
  ?o_year
  (sum(?amount) as ?sum_profit)
where {
  { select
      ?nation
      ?o_year
      ((xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)) - xsd:decimal(?ps_supplycost) * xsd:decimal(?l_linequantity)) as ?amount)
    where {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_linequantity ?l_linequantity ;
          ltpch:l_lineextendedprice ?l_lineextendedprice ;
          ltpch:l_linediscount ?l_linediscount ;
          ltpch:partsupplier_nation_name ?nation ;
          ltpch:partsupplier_partsupplier_supplycost ?ps_supplycost ;
          ltpch:partsupplier_part_name ?p_name ;
          ltpch:order_order_orderdate ?o_orderdate .
      filter (REGEX (?p_name, "%COLOR%"))
      BIND (SUBSTR(STR(?o_orderdate), 1,4) as ?o_year)
    } 
  } 
}
group by
  ?nation
  ?o_year
order by
  ?nation
  desc (?o_year)

            

Query 10


select
  ?c_custkey
  ?c_companyName
  (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))) as ?revenue)
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
where  {
  ?li qb:dataSet ltpch:lineitemCube ;
      ltpch:l_returnflag ?l_returnflag ;
      ltpch:l_lineextendedprice ?l_lineextendedprice ;
      ltpch:l_linediscount ?l_linediscount ;
      ltpch:order_order_orderdate ?o_orderdate ;
      ltpch:order_customer_address ?c_address ;
      ltpch:order_customer_phone ?c_phone ;
      ltpch:order_customer_comment ?c_comment ;
      ltpch:order_customer_acctbal ?c_acctbal ;
      ltpch:order_customer_custkey ?c_custkey ;
      ltpch:order_customer_name ?c_companyName ;
      ltpch:order_nation_name ?nation .
   filter ((xsd:date(?o_orderdate) >= xsd:date("%MONTH%-01"^^xsd:date)) &&
      (xsd:date(?o_orderdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration)) &&
      (?l_returnflag = "R") 
   ) 
}
group by
  ?c_custkey
  ?c_companyName
  ?c_acctbal
  ?nation
  ?c_address
  ?c_phone
  ?c_comment
order by
  desc (sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount))))
limit 20

            

Query 11


select
  ?bigpspart
  ?bigpsvalue
where {
      { select
          ?bigpspart
          (sum(xsd:decimal(?b_supplycost) * xsd:decimal(?b_availqty)) as ?bigpsvalue)
        where
          {
            ?bigps ltpch:partsupplier_part_partkey ?bigpspart ;
                   ltpch:partsupplier_partsupplier_supplycost ?b_supplycost ;
                   ltpch:partsupplier_partsupplier_availqty ?b_availqty ;
                    ltpch:partsupplier_nation_name "%NATION%" .
          }
          group by ?bigpspart
      }
      {
       select
          (sum(xsd:decimal(?t_supplycost) * xsd:decimal(?t_availqty)) * %FRACTION% as ?threshold)
        where
          {
            ?thr_ps ltpch:partsupplier_partsupplier_supplycost ?t_supplycost ;
                    ltpch:partsupplier_partsupplier_availqty ?t_availqty ;
                    ltpch:partsupplier_nation_name "%NATION%" .
          }
      }
      filter (?bigpsvalue > ?threshold )
  }
order by
  desc (?bigpsvalue)

          

Query 12


select
  ?l_shipmode
  (sum (
      xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
      fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?high_line_count)
  (sum (1 -
      xsd:integer(fn:starts-with(?o_orderpriority, "1-URGENT") ||
      fn:starts-with(?o_orderpriority, "2-HIGH") ) ) as ?low_line_count)
where  {
    ?li qb:dataSet ltpch:lineitemCube ;
       ltpch:l_commitdate ?l_commitdate ;
       ltpch:l_receiptdate ?l_receiptdate ;
       ltpch:l_shipmode ?l_shipmode ;
       ltpch:l_shipdate ?l_shipdate ;
       ltpch:order_order_orderpriority ?o_orderpriority .
     filter (?l_shipmode in ("%SHIPMODE1%", "%SHIPMODE2%") &&
      (xsd:date(?l_commitdate) < xsd:date(?l_receiptdate)) &&
      (xsd:date(?l_shipdate) < xsd:date(?l_commitdate)) &&
      (xsd:date(?l_receiptdate) >= "%YEAR%-01-01"^^xsd:date) &&
      (xsd:date(?l_receiptdate) < ("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration)) )
  }
group by
  ?l_shipmode
order by
  ?l_shipmode
          

Query 13


select
  ?c_count
  (count(1) as ?custdist)
where {
    { select
        ?c_custkey
        (count (?orderkey) as ?c_count)
      where
        {
          {
            select distinct ?c_custkey 
            where
            {
              ?li ltpch:order_customer_custkey ?c_custkey .
            }
          }
           optional {
            {
              select ?orderkey
              where {
              ?li2 ltpch:order_customer_custkey ?c_custkey ;
                   ltpch:order_order_orderkey ?orderkey  ;
                   ltpch:order_order_comment ?o_comment .
              filter (!( REGEX (?o_comment , "%WORD1%.*%WORD2%" ) ) ) .
              }
              group by ?orderkey
            }
          }
        }
      group by 
        ?c_custkey
    }
  }
group by
  ?c_count
order by
  desc (count(1))
  desc (?c_count)

          

Query 14


select
((100 * sum(xsd:integer(fn:starts-with(?p_type, "PROMO")) * xsd:decimal(?l_lineextendedprice) *  (xsd:decimal(1) - xsd:decimal(?l_linediscount)))  / sum(xsd:decimal(?l_lineextendedprice) *  (xsd:decimal(1) - xsd:decimal(?l_linediscount)))) as ?promo_revenue)
  where {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_lineextendedprice ?l_lineextendedprice ;
          ltpch:l_linediscount ?l_linediscount ;
          ltpch:l_shipdate ?l_shipdate ;
          ltpch:partsupplier_part_type ?p_type .
      filter (xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
      (xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P1M"^^xsd:duration)) )
  
}
          

Query 15


select distinct
  ?s_suppkey
  ?s_name
  ?s_address
  ?s_phone
  ?total_revenue
where  
{
  ?partsupp ltpch:partsupplier_supplier_suppkey ?s_suppkey ;
            ltpch:partsupplier_supplier_name ?s_name ;
            ltpch:partsupplier_supplier_address ?s_address ;
            ltpch:partsupplier_supplier_phone ?s_phone .
  { 
    select
        ?s_suppkey
        ((sum(xsd:decimal(?l_extendedprice) * (1 - xsd:decimal(?l_discount)))) as ?total_revenue)
    where 
    {
      ?li qb:dataSet ltpch:lineitemCube ;
          ltpch:l_shipdate ?l_shipdate ;
          ltpch:l_lineextendedprice ?l_extendedprice ;
          ltpch:l_linediscount ?l_discount ;
          ltpch:l_has_partsupplier ?ps ;
          ltpch:partsupplier_supplier_suppkey ?s_suppkey .
      filter (
                xsd:date(?l_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
                xsd:date(?l_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
    }
    group by
      ?s_suppkey
  } .
  { 
    select 
      (max (?l2_total_revenue) as ?maxtotal)
    where 
    {
      { 
        select
          ((sum(xsd:decimal(?l2_extendedprice) * (1 - xsd:decimal(?l2_discount)))) as ?l2_total_revenue)
        where 
        {
          ?li2 qb:dataSet ltpch:lineitemCube ;
              ltpch:l_shipdate ?l2_shipdate ;
              ltpch:l_lineextendedprice ?l2_extendedprice ;
              ltpch:l_linediscount ?l2_discount ;
              ltpch:l_has_partsupplier ?ps2 ;
              ltpch:partsupplier_supplier_suppkey ?s_suppkey2 .
          filter (
                        xsd:date(?l2_shipdate) >= xsd:date("%MONTH%-01"^^xsd:date) &&
                        xsd:date(?l2_shipdate) < xsd:date("%MONTH%-01"^^xsd:date + "P3M"^^xsd:duration) )
        }
        group by
          ?s_suppkey2
      }
    }
  }
  filter (?total_revenue = ?maxtotal)
}
order by
  ?s_suppkey

          

Query 16


select
  ?p_brand
  ?p_type
  ?p_size
  (count(distinct ?supp) as ?supplier_cnt)
where {
    ?ps ltpch:partsupplier_part_brand ?p_brand ;
        ltpch:partsupplier_part_type ?p_type ;
        ltpch:partsupplier_part_size ?p_size ;   
        ltpch:partsupplier_supplier_suppkey ?supp .    
    filter (
      (?p_brand != "%BRAND%") &&
      !(fn:starts-with(?p_type,"%TYPE%")) &&
      (xsd:integer(?p_size) in (%SIZE1%, %SIZE2%, %SIZE3%, %SIZE4%, %SIZE5%, %SIZE6%, %SIZE7%, %SIZE8%))
    )
    filter NOT EXISTS {
       ?supp a ltpch:supplier;
             ltpch:s_comment ?badcomment .
       filter ( fn:matches (?badcomment ,"Customer.*Complaints") )
    }
  }
group by
  ?p_brand
  ?p_type
  ?p_size
order by
  desc ((count(distinct ?supp)))
  ?p_brand
  ?p_type
  ?p_size

          

Query 17


select
  ((sum(xsd:decimal(?l_lineextendedprice)) / 7.0) as ?avg_yearly)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_has_partsupplier ?ps ;
        ltpch:partsupplier_part_partkey ?p_partkey.
    {
      select 
        ?p_partkey
        ((0.2 * avg(xsd:decimal(?l2_linequantity))) as ?threshold)
      where { 
        ?li2  a ltpch:lineitem ;
              ltpch:l_linequantity ?l2_linequantity ; 
              ltpch:partsupplier_part_partkey ?p_partkey ;
              ltpch:partsupplier_part_container ?p_container ;
              ltpch:partsupplier_part_brand ?p_brand  .
      }
      group by
        ?p_partkey
    }
    filter (xsd:decimal(?l_linequantity) < ?threshold && REGEX(?p_brand,"%BRAND%","i") && ?p_container = "%CONTAINER%") 
}
          

Query 18


select
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
   (sum(xsd:decimal(?l_linequantity)) as ?l_quantity)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:order_order_orderkey ?o_orderkey ;
        ltpch:order_order_orderdate ?o_orderdate ;
        ltpch:order_order_ordertotalprice ?o_ordertotalprice ;
        ltpch:order_customer_custkey ?c_custkey ;
        ltpch:order_customer_name ?c_name .
    { 
      select 
        ?o_orderkey 
        (sum (xsd:decimal(?l2_linequantity)) as ?sum_q)
      where 
      {
        ?li2 a ltpch:lineitem ;
             ltpch:l_linequantity ?l2_linequantity ;
             ltpch:order_order_orderkey ?o_orderkey .
      }
      group by
        ?o_orderkey
    } .
    filter (xsd:decimal(?sum_q) > xsd:decimal(%QUANTITY%))
}
group by
   ?c_name
   ?c_custkey
   ?o_orderkey
   ?o_orderdate
   ?o_ordertotalprice
order by
  desc (?o_ordertotalprice)
  ?o_orderdate
limit 100

          

Query 19


select
  ((sum(xsd:decimal(?l_lineextendedprice) * (1 - xsd:decimal(?l_linediscount)))) as ?revenue)
where {
    ?li qb:dataSet ltpch:lineitemCube ;
        ltpch:l_lineextendedprice ?l_lineextendedprice ;
        ltpch:l_linediscount ?l_linediscount ;
        ltpch:l_linequantity ?l_linequantity ;
        ltpch:l_shipmode ?l_shipmode ;
        ltpch:l_shipinstruct ?l_shipinstruct ;
        ltpch:partsupplier_part_brand ?p_brand ;
        ltpch:partsupplier_part_size ?p_size ;
        ltpch:partsupplier_part_container ?p_container .
     filter (?l_shipmode in ("AIR", "AIR REG") &&
      ?l_shipinstruct = "DELIVER IN PERSON" &&
      ( ( (REGEX(?p_brand,"^%BRAND1%$","i")) &&
          (?p_container in ("SM CASE", "SM BOX", "SM PACK", "SM PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY1%) &&
          (xsd:integer(?l_linequantity) <= %QUANTITY1% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 5) ) ||
        ( (REGEX(?p_brand,"^%BRAND2%$","i")) &&
          (?p_container in ("MED BAG", "MED BOX", "MED PKG", "MED PACK")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY2%) && 
          (xsd:integer(?l_linequantity) <= %QUANTITY2% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 10) ) ||
        ( (REGEX(?p_brand,"^%BRAND3%$","i")) &&
          (?p_container in ("LG CASE", "LG BOX", "LG PACK", "LG PKG")) &&
          (xsd:integer(?l_linequantity) >= %QUANTITY3%) &&
          (xsd:integer(?l_linequantity) <= %QUANTITY3% + 10) &&
          (xsd:integer(?p_size) >= 1) && (xsd:integer(?p_size) <= 15) ) ) )
  }

          

Query 20


select distinct
  ?s_name
  ?s_address
where
{
  ?supp ltpch:partsupplier_supplier_name ?s_name ;
        ltpch:partsupplier_supplier_suppkey ?suppkey ;
        ltpch:partsupplier_supplier_address ?s_address .
  { 
    select 
      distinct ?suppkey 
    where 
    {
      ?li ltpch:partsupplier_partsupplier_availqty ?big_ps_availqty ;
              ltpch:partsupplier_supplier_suppkey ?suppkey ;
              ltpch:partsupplier_nation_name ?n_name ;
              ltpch:partsupplier_part_name ?p_name . 
      filter (REGEX (?p_name , "^%COLOR%") && 
              ?n_name = "%NATION%" && 
              xsd:decimal(?big_ps_availqty) > ?qty_threshold)
      {
        select 
          ?li
          ((0.5 * sum(xsd:decimal(?l_linequantity))) as ?qty_threshold)
        where
        {
          ?li qb:dataSet ltpch:lineitemCube ;
              ltpch:l_shipdate ?l_shipdate ;
              ltpch:l_linequantity ?l_linequantity .
          filter ((xsd:date(?l_shipdate) >= xsd:date("%YEAR%-01-01"^^xsd:date)) &&
            (xsd:date(?l_shipdate) < xsd:date("%YEAR%-01-01"^^xsd:date + "P1Y"^^xsd:duration))
          )
        } 
        group by
          ?li
      } .
    } 
  }
}
order by ?s_name

          

Query 21


select
    ?s_name
    ((count(1)) as ?numwait)
where {
         ?li1 qb:dataSet ltpch:lineitemCube ;
              ltpch:l_receiptdate ?l1_receiptdate ;
              ltpch:l_commitdate ?l1_commitdate ;
              ltpch:partsupplier_supplier_name ?s_name ;
              ltpch:partsupplier_supplier_suppkey ?suppkey ;
              ltpch:partsupplier_nation_name ?n_name ;
              ltpch:order_order_orderkey ?orderkey ;
              ltpch:order_order_orderstatus ?o_orderstatus .
         filter ( 
            xsd:date(?l1_receiptdate) > xsd:date(?l1_commitdate) && 
            ?n_name = "%NATION%" && 
            ?o_orderstatus = "F"
            ) 
         filter exists {
              ?li2 ltpch:order_order_orderkey ?orderkey ;
                   ltpch:partsupplier_supplier_suppkey ?suppkey2 .
              filter (?suppkey != ?suppkey2)
         }
         filter not exists {
              ?li3 ltpch:order_order_orderkey ?orderkey ;
                   ltpch:l_receiptdate ?l3_receiptdate ;
                   ltpch:l_commitdate ?l3_commitdate ;
                   ltpch:partsupplier_supplier_suppkey ?suppkey3 .
              filter (
                 xsd:date(?l3_receiptdate) > xsd:date(?l3_commitdate) &&
                 ?suppkey3 != ?suppkey
              )
         }
       }
group by
   ?s_name
order by
    desc (count(1))
    ?s_name
limit 100

          

Query 22


select
  ?cntrycode
  (count (1) as ?numcust)
  (sum (xsd:decimal(?c_acctbal)) as ?totacctbal)
where {
    ?cust ltpch:order_customer_acctbal ?c_acctbal ;
      ltpch:order_customer_phone ?c_phone .
      BIND (fn:substring(?c_phone,0, 3) as ?cntrycode)
    { select (avg (xsd:decimal(?c_acctbal2)) as ?acctbal_threshold)
          where
            {
              ?li ltpch:order_customer_acctbal ?c_acctbal2 ;
                 ltpch:order_customer_phone ?c_phone2 .
              filter ((xsd:decimal(?c_acctbal2) > 0.00) &&
                fn:substring(?c_phone2,0, 3) in (%COUNTRY_CODE_SET%)  )
            } 
    }
    filter (
      ?cntrycode in (%COUNTRY_CODE_SET%) &&
      (xsd:decimal(?c_acctbal) > ?acctbal_threshold ) )
    filter not exists { ?cust ltpch:order_order_orderkey ?orderkey }
  }
group by ?cntrycode
order by ?cntrycode