TPC-H Benchmark

The TPC-H is a decision support benchmark. It consists of a suite of business oriented ad-hoc queries and concurrent data modifications. The queries and the data populating the database have been chosen to have broad industry-wide relevance. This benchmark illustrates decision support systems that examine large volumes of data, execute queries with a high degree of complexity, and give answers to critical business questions.

Timbr TPC-H Benchmark for Query Complexity

Timbr’s semantic graph model maps databases to provide unified meaning and relationships to data. By querying the model’s concepts instead of directly querying the data, SQL queries become simpler to create, understand and maintain. We use the TPC-H benchmark to compare the complexity of SQL queries of the Timbr model with SQL queries of the data (without Timbr), where complexity is measured by the number of Join clauses and the total number of lines used in each query.  Using the TPC-H data (see ERD Model below), we created a Timbr model (see Timbr model of the TPC-H ERD further below) and mapped the model to the corresponding data tables.

TPC-H ERD Model

Timbr model of the TPC-H ERD

Timbr TPC Data Model

Results of query complexity comparison

Q1 Timbr Without
SQL Lines 14 14
SQL JOINS 0 0
Q3 Timbr Without
SQL Lines 10 12
SQL JOINS 0 2
Q5 Timbr Without
SQL Lines 7 13
SQL JOINS 0 5
Q7 Timbr Without
SQL Lines 10 20
SQL JOINS 0 5
Q9 Timbr Without
SQL Lines 7 16
SQL JOINS 0 5
Q11 Timbr Without
SQL Lines 8 17
SQL JOINS 0 2
Q13 Timbr Without
SQL Lines 11 11
SQL JOINS 0 1
Q15 Timbr Without
SQL Lines 15 25
SQL JOINS 0 1
Q17 Timbr Without
SQL Lines 4 10
SQL JOINS 0 1
Q19 Timbr Without
SQL Lines 11 21
SQL JOINS 0 1
Q21 Timbr Without
SQL Lines 13 24
SQL JOINS 0 3
Q2 Timbr Without
SQL Lines 12 26
SQL JOINS 0 7
Q4 Timbr Without
SQL Lines 7 11
SQL JOINS 0 0
Q6 Timbr Without
SQL Lines 4 6
SQL JOINS 0 0
Q8 Timbr Without
SQL Lines 8 19
SQL JOINS 0 7
Q10 Timbr Without
SQL Lines 12 17
SQL JOINS 0 3
Q12 Timbr Without
SQL Lines 9 12
SQL JOINS 0 1
Q14 Timbr Without
SQL Lines 4 5
SQL JOINS 0 1
Q16 Timbr Without
SQL Lines 11 11
SQL JOINS 0 1
Q18 Timbr Without
SQL Lines 10 12
SQL JOINS 0 2
Q20 Timbr Without
SQL Lines 10 20
SQL JOINS 0 1
Q22 Timbr Without
SQL Lines 8 20
SQL JOINS 0 0

Results Summary

Total Timbr Without
SQL Lines 205 342
SQL JOINS 0 49

Detailed TPC-H Benchmark Results

Select a query to view the results

This query reports the amount of business that was billed, shipped, and returned.

Q1-Timbr

            SELECT `return_flag`,
`line_status`,
SUM(`quantity`) AS sum_qty,
SUM(`extended_price`) AS sum_base_price,
SUM(`discount_price`) AS sum_disc_price,
SUM(`charge`) AS sum_charge,
AVG(`quantity`) AS avg_qty,
AVG(`extended_price`) AS avg_price,
AVG(`discount`) AS avg_disc,
COUNT(*) AS count_order
FROM `timbr`.`line_item`
WHERE `ship_date` <= date '1998-12-01' - INTERVAL '90' day
GROUP BY `return_flag`, `line_status`
ORDER BY `return_flag`, `line_status`
        

Q1-TPC

            SELECT l_returnflag,   
l_linestatus,   
SUM(l_quantity) AS sum_qty,   
SUM(l_extendedprice) AS sum_base_price,   
SUM(l_extendedprice * (1 - l_discount)) AS sum_disc_price,   
SUM(l_extendedprice * (1 - l_discount) * (1 + l_tax)) AS sum_charge,   
AVG(l_quantity) AS avg_qty, 
AVG(l_extendedprice) AS avg_price,   
AVG(l_discount) AS avg_disc,   
COUNT(*) AS count_order  
FROM tpc.lineitem 
WHERE l_shipdate <= date '1998-12-01' - INTERVAL '90' day  
GROUP BY l_returnflag, l_linestatus  
ORDER BY l_returnflag, l_linestatus
        

In this query, we created calculated properties in Timbr so that we can query them more conveniently. For example, instead of writing a long calculation like l_extendedprice * (1 – l_discount) * (1 + l_tax), we created a charge property in the line_item concept’s mapping.

This query finds which supplier should be selected to place an order for a given part in a given region.

Q2-Timbr

            SELECT DISTINCT `supplier_account_balance`, 
`supplier_name`, 
`has_nation[nation].nation_name` AS nation_name, 
`from_supplier[brass_product].part_key` AS part_key, 
`from_supplier[brass_product].manufacturer` AS manufacturer, 
`supplier_address`, 
`supplier_phone`, 
`supplier_comment`
FROM `dtimbr`.`european_supplier`
WHERE `from_supplier[brass_product].size` = 15 
AND ps_supplycost = `from_supplier[brass_product].min_supplycost_europe`
ORDER BY `supplier_account_balance` DESC, `nation_name`, `supplier_name`, `part_key`
        

Q2-TPC

            SELECT s_acctbal,  
s_name,  
n_name,  
p_partkey,  
p_mfgr,  
s_address,  
s_phone,  
s_comment  
FROM tpc.part
INNER JOIN tpc.partsupp ON p_partkey = ps_partkey
INNER JOIN tpc.supplier ON s_suppkey = ps_suppkey
INNER JOIN tpc.nation ON s_nationkey = n_nationkey
INNER JOIN tpc.region ON n_regionkey = r_regionkey
WHERE p_size = 15  
AND p_type like '%BRASS'  
AND r_name = 'EUROPE'  
AND ps_supplycost = (
  SELECT  min(ps_supplycost)  
  FROM tpc.partsupp
  INNER JOIN tpc.supplier ON s_suppkey = ps_suppkey
  INNER JOIN tpc.nation ON s_nationkey = n_nationkey
  INNER JOIN tpc.region ON n_regionkey = r_regionkey 
  WHERE p_partkey = ps_partkey  
  AND r_name = 'EUROPE'  
)  
ORDER BY s_acctbal DESC, n_name, s_name, p_partkey
        

In this example, we use relationships between concepts instead of using JOINS and as a result, the query can be significantly simplified. The query starts from the line_item concept and uses several relationships: from_supplier [product], has_nation [nation], has_region [region]. Some of them are related to the line_item concept and some of them are threading of a relationship on top of another relationship.

This query retrieves the 10 unshipped orders with the highest value.

Q3-Timbr

            SELECT `order_key`,
SUM(`volume`) AS revenue,
`of_order[order].order_date` AS order_date,
`of_order[order].ship_priority` AS ship_priority
FROM `dtimbr`.`line_item`
WHERE `of_order[order].ordered_by[building_customer].customer_market_segment` IS NOT NULL
AND `of_order[order].order_date` < '1995-03-15'
AND `ship_date` > '1995-03-15'
GROUP BY `order_key`, `order_date`, `ship_priority`
ORDER BY `revenue` DESC, `order_date`
        

Q3-TPC

            SELECT l_orderkey,  
SUM(l_extendedprice*(1-l_discount)) AS revenue,  
o_orderdate, 
o_shippriority  
FROM tpc.customer 
INNER JOIN tpc.`order` ON c_custkey = o_custkey 
INNER JOIN tpc.lineitem ON l_orderkey = o_orderkey 
WHERE c_mktsegment = 'BUILDING'  
AND o_orderdate < date '1995-03-15'  
AND l_shipdate > date '1995-03-15'  
GROUP BY l_orderkey, o_orderdate, o_shippriority  
ORDER BY revenue DESC, o_orderdate 
        

In this query, we use a calculated property, a relationship between the line_item concept and the order concept, and a classification of concepts in the ontology. In this example, we created a child concept of the customer concept called building_customer, and it contains only data where customer_mktsegment = BUILDING.

This query determines how well the order priority system is working and gives an assessment of customer satisfaction.

Q4-Timbr

            SELECT `order_priority`, 
COUNT(DISTINCT `order_key`) AS order_count
FROM `dtimbr`.`order` o
WHERE `order_date_quarter` = 3 AND `order_date_year` = 1993
AND `has_line_item[late_line_item].order_key` IS NOT NULL
GROUP BY `order_priority`
ORDER BY `order_priority`
        

Q4-TPC

            SELECT o_orderpriority,  
COUNT(*) as order_count  
FROM tpc.`order`  
WHERE o_orderdate >= date '1993-07-01'  
AND o_orderdate < date '1993-07-01' + interval '3' month  
AND exists (select * 
from tpc.lineitem 
WHERE l_orderkey = o_orderkey 
AND l_commitdate < l_receiptdate)  
GROUP BY o_orderpriority  
ORDER BY o_orderpriority
        

In this query we use calculated properties, such as order_date_year and order_date_quarter.

This query lists the revenue volume done through local suppliers.

Q5-Timbr

            SELECT `of_supplier[asian_supplier].has_nation[nation].nation_name` AS nation_name, 
SUM(`volume`) AS revenue
FROM `dtimbr`.`line_item`
WHERE `of_supplier[asian_supplier].has_nation[nation].nation_name`=`of_order[order].ordered_by[customer].in_nation[nation].nation_name`
AND `of_order[order].order_date_year` = 1994
GROUP BY `of_supplier[asian_supplier].has_nation[nation].nation_name`
ORDER BY `revenue` DESC
        

Q5-TPC

            SELECT n_name,  
SUM(l_extendedprice * (1 - l_discount)) AS revenue  
FROM tpc.customer 
INNER JOIN tpc.`order` ON c_custkey = o_custkey
INNER JOIN tpc.lineitem ON l_orderkey = o_orderkey
INNER JOIN tpc.supplier ON l_suppkey = s_suppkey AND c_nationkey = s_nationkey
INNER JOIN tpc.nation ON s_nationkey = n_nationkey
INNER JOIN tpc.region ON n_regionkey = r_regionkey
WHERE r_name = 'ASIA'  
AND o_orderdate >= date '1994-01-01'  
AND o_orderdate < date '1994-01-01' + INTERVAL '1' year  
GROUP BY n_name 
ORDER BY revenue DESC
        

In this query we use calculated properties such as volume and order_date_year, and several relationships such as: of_supplier[supplier], of_order[order], has_nation[nation], has_region[region], instead of using JOINS.

This query quantifies the amount of revenue increase that would have resulted from eliminating certain companywide discounts in a given percentage range in a given year.

Q6-Timbr

            SELECT SUM(`revenue`) AS revenue
FROM `timbr`.`line_time_with_average_discount`
WHERE `ship_date_year` = 1994
AND `quantity` <  24
        

Q6-TPC

            SELECT SUM(l_extendedprice*l_discount) AS revenue  
FROM tpc.lineitem  
WHERE l_shipdate >= date '1994-01-01'  
AND l_shipdate < date '1994-01-01' + INTERVAL '1' year  
AND l_discount BETWEEN 0.06 - 0.01 AND 0.06 + 0.01  
AND l_quantity < 24
        

In this query, we use calculated properties such as revenue and ship_date_year, and a classification of concepts in the ontology. In this example, we created a child concept of the line_item concept called line_time_with_average_discount, containing only data where discount >= 0.05 and discount <= 0.07.

This query determines the value of goods shipped between certain nations to help in the re-negotiation of shipping contracts.

Q7-Timbr

            SELECT `of_supplier[supplier].has_nation[nation].nation_name` AS supplier_nation,
`of_order[order].ordered_by[customer].in_nation[nation].nation_name` AS customer_nation,
`ship_date_year`,
SUM(`volume`) AS revenue
FROM `dtimbr`.`line_item`
WHERE ((`of_supplier[supplier].has_nation[nation].nation_name` = 'FRANCE' AND `of_order[order].ordered_by[customer].in_nation[nation].nation_name` = 'GERMANY') 
OR (`of_supplier[supplier].has_nation[nation].nation_name` = 'GERMANY' AND `of_order[order].ordered_by[customer].in_nation[nation].nation_name` = 'FRANCE'))
AND `ship_date` BETWEEN '1995-01-01' AND '1996-12-31'
GROUP BY `supplier_nation`, `customer_nation`, `ship_date_year`
ORDER BY `supplier_nation`, `customer_nation`, `ship_date_year`
        

Q7-TPC

            SELECT supp_nation,  
cust_nation,  
l_year, 
SUM(volume) AS revenue  
FROM (  
SELECT n1.n_name AS supp_nation,  
n2.n_name AS cust_nation,  
extract(year FROM l_shipdate) AS l_year,  
l_extendedprice * (1 - l_discount) as volume  
FROM tpc.supplier  
INNER JOIN tpc.lineitem ON s_suppkey = l_suppkey
INNER JOIN tpc.`order` ON o_orderkey = l_orderkey
INNER JOIN tpc.customer ON c_custkey = o_custkey
INNER JOIN tpc.nation n1 ON  s_nationkey = n1.n_nationkey
INNER JOIN tpc.nation n2 ON  c_nationkey = n2.n_nationkey
WHERE ((n1.n_name = 'FRANCE' AND n2.n_name = 'GERMANY')  
OR (n1.n_name = 'GERMANY' AND n2.n_name = 'FRANCE'))  
AND l_shipdate between date '1995-01-01' AND date '1996-12-31') AS shipping  
GROUP BY supp_nation, cust_nation, l_year  
ORDER BY supp_nation, cust_nation, l_year
        

In this query we use calculated properties such as volume and ship_date_year. Also, we use several relationships such as: of_supplier[supplier], has_nation[nation], of_order[order], ordered_by[customer], in_nation[nation], instead of using JOINS.

This query determines how the market share of a given nation within a given region has changed over two years for a given part type.

Q8-Timbr

            SELECT `of_order[order].order_date_year` AS order_date_year,
SUM(CASE WHEN `of_supplier[supplier].has_nation[nation].nation_name` = 'BRAZIL' THEN `volume` ELSE 0 END) / SUM(`volume`) AS market_share
FROM `dtimbr`.`line_item`
WHERE `of_order[order].ordered_by[customer].in_nation[nation].has_region[region].region_name` = 'AMERICA'
AND `of_order[order].order_date` BETWEEN '1995-01-01' AND '1996-12-31'
AND `has_product[product].product_type` = 'ECONOMY ANODIZED STEEL'
GROUP BY `order_date_year`
ORDER BY `order_date_year`
        

Q8-TPC

            SELECT o_year,  
SUM(CASE WHEN nation = 'BRAZIL' THEN volume ELSE 0 END) / SUM(volume) AS mkt_share 
FROM (
  SELECT extract(year FROM o_orderdate) AS o_year,  
  l_extendedprice * (1-l_discount) AS volume,  
  n2.n_name AS nation  
  FROM tpc.part
  INNER JOIN tpc.lineitem ON p_partkey = l_partkey
  INNER JOIN tpc.supplier ON s_suppkey = l_suppkey
  INNER JOIN tpc.`order` ON l_orderkey = o_orderkey
  INNER JOIN tpc.customer ON o_custkey = c_custkey
  INNER JOIN tpc.nation n1 ON c_nationkey = n1.n_nationkey
  INNER JOIN tpc.nation n2 ON s_nationkey = n2.n_nationkey
  INNER JOIN tpc.region ON n1.n_regionkey = r_regionkey 
  WHERE r_name = 'AMERICA'  
  AND o_orderdate between date '1995-01-01' AND date '1996-12-31' 
  AND p_type = 'ECONOMY ANODIZED STEEL') AS all_nations  
GROUP BY o_year  
ORDER BY o_year
        

In this query we use calculated properties such as volume and order_date_year. Also, we use several relationships such as: of_order[order], of_supplier[supplier], has_nation[nation], ordered_by[customer], in_nation[nation], has_region[region], has_product[product], instead of using JOINS. This query pulls data from all the concepts in the ontology without using JOINS.

This query determines how much profit is made on a given line of parts, broken out by supplier nation and year.

Q9-Timbr

            SELECT `has_nation[nation].nation_name` AS nation,
`has_line_item[line_item].of_order[order].order_date_year` AS order_year,
SUM(`has_line_item[line_item].volume` - `from_supplier[green_product]_supply_cost` * `has_line_item[line_item].quantity`) AS sum_profit
FROM `dtimbr`.`supplier`
WHERE `from_supplier[green_product].part_key`=`has_line_item[line_item].part_key`
GROUP BY `has_nation[nation].nation_name`, `has_line_item[line_item].of_order[order].order_date_year`
ORDER BY `has_nation[nation].nation_name`, `has_line_item[line_item].of_order[order].order_date_year` DESC
        

Q9-TPC

            SELECT nation,  
o_year,  
SUM(amount) AS sum_profit  
FROM (  
SELECT n_name AS nation,  
extract(year FROM o_orderdate) AS o_year,  
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount  
FROM tpc.part 
INNER JOIN tpc.lineitem ON p_partkey = l_partkey
INNER JOIN tpc.supplier ON s_suppkey = l_suppkey 
INNER JOIN tpc.partsupp ON ps_suppkey = l_suppkey and ps_partkey = l_partkey
INNER JOIN tpc.`order` ON o_orderkey = l_orderkey
INNER JOIN tpc.nation ON s_nationkey = n_nationkey 
WHERE p_name like '%green%') AS profit  
GROUP BY nation,  o_year  
ORDER BY nation,  o_year DESC
        

In this query we use a calculated property, several relationships between the supplier concept and the other concepts, and a classification of concepts in the ontology. In this example, we created a child concept of the product concept called green_product, and it contains only data where product_name like %green%. Also, we use a relationship’s property named from_supplier[green_product]_ps_supplycost, originating from the table tpc.partsupp.

The query identifies customers who might be having problems with the parts that are shipped to them.

Q10-Timbr

            SELECT `customer_key`,
`customer_name`,
SUM(`has_ordered[order].has_line_item[returned_line_item].volume`) AS revenue,
`customer_account_balance`,
`in_nation[nation].nation_name` AS n_name,
`customer_address`,
`customer_phone`,
`customer_comment`
FROM `dtimbr`.`customer`
WHERE `has_ordered[order].order_date_quarter` = 4 AND `has_ordered[order].order_date_year` = 1993
GROUP BY `customer_key`, `customer_name`, `customer_account_balance`, `customer_phone`, `in_nation[nation].nation_name`, `customer_address`, `customer_comment`
ORDER BY revenue DESC
        

Q10-TPC

            SELECT c_custkey,  
c_name,  
SUM(l_extendedprice * (1 - l_discount)) AS revenue,  
c_acctbal,  
n_name,  
c_address,  
c_phone,  
c_comment  
FROM tpc.customer  
INNER JOIN tpc.`order` ON c_custkey = o_custkey
INNER JOIN tpc.lineitem ON l_orderkey = o_orderkey
INNER JOIN tpc.nation ON c_nationkey = n_nationkey
WHERE o_orderdate >= date '1993-10-01'  
AND o_orderdate < date '1993-10-01' + INTERVAL '3' month  
AND l_returnflag = 'R'  
GROUP BY c_custkey, c_name, c_acctbal, c_phone, n_name, c_address, c_comment  
ORDER BY revenue DESC 
        

In this query we use calculated properties such as volume, order_date_year, and order_date_quarter, and several relationships such as: has_ordered[order], has_line_item[returned_line_item], in_nation[nation], instead of using JOINS. Also, we created a child concept of the line_item concept called returned_line_item, and it contains only data where `return_flag` = ‘R’.

This query finds the most important subset of suppliers’ stock in a given nation.

Q11-Timbr

            SELECT `from_supplier[product].part_key` AS part_key,
SUM(`from_supplier[product]_supply_value`) AS supply_value
FROM `dtimbr`.`saudi_arabia_supplier`
GROUP BY `from_supplier[product].part_key`
HAVING supply_value > (
SELECT SUM(`from_supplier[product]_supply_value`) * 0.0000000333 AS sum_supply_value
FROM `dtimbr`.`saudi_arabia_supplier`)
ORDER BY `supply_value` DESC
        

Q11-TPC

            SELECT ps_partkey, 
SUM(ps_supplycost*ps_availqty) AS `value`  
FROM tpc.partsupp
INNER JOIN tpc.supplier ON ps_suppkey = s_suppkey
INNER JOIN tpc.nation ON s_nationkey = n_nationkey 
WHERE n_name  = 'SAUDI ARABIA'  
GROUP BY ps_partkey  
HAVING SUM(ps_supplycost*ps_availqty) > ( 
  SELECT SUM(ps_supplycost*ps_availqty) * 0.0000000333     
  FROM tpc.partsupp,
  tpc.supplier,
  tpc.nation
  WHERE ps_suppkey = s_suppkey
  AND s_nationkey = n_nationkey 
  AND n_name  = 'SAUDI ARABIA'
)  
ORDER BY `value` DESC
        

In this query, we use a calculated relationship property called supply_value. We also use several relationships instead of using JOINS, and a classification of concepts in the ontology. In this example, we created a child concept of the supplier concept called middle_east_supplier, and a child concept of middle_east supplier called saudi_arabia_supplier , and it contains only data where nation_key = ’20’.

This query determines whether selecting less expensive modes of shipping is negatively affecting the critical-priority orders by causing more parts to be received by customers after the committed date.

Q12-Timbr

            SELECT `ship_mode`,
COUNT(`of_order[high_priority_order].order_key`) AS high_line_count,
COUNT(`of_order[low_priority_order].order_key`) AS low_line_count
FROM `dtimbr`.`mail_and_ship_line_item`
WHERE `commit_date` < `receipt_date`
AND `ship_date` < `commit_date`
AND `receipt_date_year` = 1994
GROUP BY `ship_mode`
ORDER BY `ship_mode`
        

Q12-TPC

            SELECT l_shipmode,  
SUM(CASE WHEN o_orderpriority = '1-URGENT' OR o_orderpriority ='2-HIGH' THEN 1 ELSE 0 END) AS high_line_count,  
SUM(CASE WHEN o_orderpriority <> '1-URGENT' AND o_orderpriority <> '2-HIGH' THEN 1 ELSE 0 END) AS low_line_count  
FROM tpc.`order`  
INNER JOIN tpc.lineitem ON o_orderkey = l_orderkey
WHERE l_shipmode IN ('MAIL', 'SHIP')  
AND l_commitdate < l_receiptdate  
AND l_shipdate < l_commitdate  
AND l_receiptdate >= date '1994-01-01'  
AND l_receiptdate < date '1994-01-01' + INTERVAL '1' year  
GROUP BY l_shipmode  
ORDER BY l_shipmode
        

In this query, we use a calculated property called receipt_date_year, several relationships instead of using JOINS, and a classification of concepts in the ontology. In this example, we created a child concept of the line_item concept called mail_and_ship_line_item, and it contains only data where ship_mode IN (‘MAIL’, ‘SHIP’).

This query seeks relationships between customers and the size of their orders.

Q13-Timbr

            SELECT `order_count`, 
COUNT(*) AS customer_dist
FROM (
  SELECT `customer_key`,
  COUNT(`has_ordered[order].order_key`) AS order_count
  FROM `dtimbr`.`customer`
  WHERE `has_ordered[order].order_comment` NOT LIKE '%special%requests%'
  GROUP BY `customer_key`
) c_orders
GROUP BY `order_count`
ORDER BY `customer_dist` DESC, `order_count` DESC
        

Q13-TPC

            SELECT c_count, 
COUNT(*) as custdist  
FROM (  
  SELECT c_custkey,  
  COUNT(o_orderkey)  
  FROM tpc.customer 
  LEFT OUTER JOIN tpc.`order` ON c_custkey = o_custkey AND o_comment NOT LIKE '%special%requests%'  
  GROUP BY c_custkey  
) AS c_orders (c_custkey, c_count)  
GROUP BY c_count  
ORDER BY custdist DESC, c_count DESC
        

In this query, we use the relationship has_ordered[order] instead of using JOINS.

This query monitors the market response to a promotion such as TV advertisements or a special campaign.

Q14-Timbr

            SELECT 100.00 * SUM(CASE WHEN `has_product[promo_product].product_type` IS NOT NULL THEN `volume` ELSE 0 END) / SUM(`volume`) AS promo_revenue
FROM `dtimbr`.`line_item`
WHERE `ship_date` >= '1995-09-01'
AND `ship_date` < date '1995-09-01' + INTERVAL '1' month
        

Q14-TPC

            SELECT  100.00 * SUM(CASE WHEN p_type like 'PROMO%' THEN l_extendedprice*(1-l_discount) ELSE 0 END) / sum(l_extendedprice * (1 - l_discount)) AS promo_revenue  
FROM tpc.lineitem  
INNER JOIN tpc.part ON l_partkey = p_partkey 
WHERE l_shipdate >= date '1995-09-01'  
AND l_shipdate < date '1995-09-01' + INTERVAL '1' month
        

In this query, we use a volume-calculated property and a relationship between the line_item concept and product concept instead of using JOINS.

This query determines the top supplier so it can be rewarded, given more business, or identified for special recognition.

Q15-Timbr

              SELECT DISTINCT `supplier_key`, 
`supplier_name`, 
`supplier_address`, 
`supplier_phone`, 
`has_line_item[line_item].total_revenue_by_quarter` total_revenue
FROM `dtimbr`.`supplier`
WHERE `has_line_item[line_item].ship_date_year` = 1996
AND `has_line_item[line_item].ship_date_quarter` = 1 
AND `has_line_item[line_item].total_revenue_by_quarter` IN (
SELECT MAX(`total_revenue_by_quarter`) 
FROM `dtimbr`.`line_item`
WHERE `ship_date_year` = 1996
AND `ship_date_quarter` = 1
)
ORDER BY `supplier_key`
        

Q15-TPC

            SELECT s_suppkey,  
s_name,  
s_address,  
s_phone,  
total_revenue  
FROM tpc.supplier  
INNER JOIN (
  SELECT l_suppkey as supplier_no,  sum(l_extendedprice * (1 - l_discount)) AS total_revenue  
  FROM tpc.lineitem  
  WHERE l_shipdate >= date '1996-01-01'  
  AND l_shipdate < date '1996-01-01' + INTERVAL '3' month  
  GROUP BY l_suppkey
) AS revenue ON s_suppkey = supplier_no  
WHERE total_revenue IN (
  SELECT  MAX(total_revenue)  
  FROM (
    SELECT l_suppkey as supplier_no,  
    SUM(l_extendedprice * (1 - l_discount)) AS total_revenue  
    FROM tpc.lineitem  
    WHERE l_shipdate >= date '1996-01-01'  
    AND l_shipdate < date '1996-01-01' + INTERVAL '3' month  
    GROUP BY l_suppkey
  ) AS revenue
)  
order by s_suppkey
        

In this query, we use calculated properties such as ship_date_year, ship_date_quarter, total_revenue_by_quarter, and several relationships instead of using JOINS.

This query finds out how many suppliers can supply parts with given attributes. It might be used, for example, to determine whether there is a sufficient number of suppliers for heavily ordered parts.

Q16-Timbr

            SELECT `brand`, 
`product_type`, 
`size`, 
COUNT(distinct `supplied_by[supplier].supplier_key`) AS supplier_cnt
FROM `dtimbr`.`product`
WHERE `brand` <> 'Brand#45'
AND `product_type` NOT LIKE 'MEDIUM POLISHED%'
AND `size` IN (49, 14, 23, 45, 19, 3, 36, 9)
AND `supplied_by[supplier].supplier_comment` NOT LIKE '%Customer%Complaints%'
GROUP BY `brand`, `product_type`, `size`
ORDER BY `supplier_cnt` DESC, `brand` ,`product_type` ,`size`
        

Q16-TPC

            SELECT p_brand,  
p_type,  
p_size,  
COUNT(distinct ps_suppkey) AS supplier_cnt  
FROM tpc.partsupp
INNER JOIN tpc.part ON p_partkey = ps_partkey
WHERE p_brand <> 'Brand#45' AND p_type NOT LIKE 'MEDIUM POLISHED%'  
AND p_size IN (49, 14, 23, 45, 19, 3, 36, 9)  
AND ps_suppkey NOT IN (SELECT s_suppkey FROM tpc.supplier WHERE s_comment like '%Customer%Complaints%')  
GROUP BY p_brand, p_type, p_size  
ORDER BY supplier_cnt desc, p_brand, p_type, p_size
        

In this query, we use a relationship between the product concept and supplier concept instead of using JOINS.

This query determines how much average yearly revenue would be lost if orders were no longer filled for small quantities of certain parts. This may reduce overhead expenses by concentrating sales on larger shipments.

Q17-Timbr

            SELECT SUM(`extended_price`) / 7.0 AS avg_yearly
FROM `dtimbr`.`line_item` AS `a`
WHERE `has_product[med_box_product].brand` = 'Brand#23'
AND `quantity` < `average_quantity`
        

Q17-TPC

            SELECT SUM(`l_extendedprice`) / 7.0 AS avg_yearly  
FROM tpc.lineitem
INNER JOIN tpc.part ON `p_partkey` = `l_partkey`
WHERE `p_brand` = 'Brand#23'  
AND `p_container` = 'MED BOX'  
AND `l_quantity` < (
  SELECT  0.2 * AVG(`l_quantity`)  
  FROM tpc.lineitem  
  WHERE `l_partkey` = `p_partkey`
)
        

In this query, we use a relationship between the line_item concept and product concept instead of using JOINS.

This query ranks customers based on their having placed a large quantity order. Large quantity orders are defined as those orders whose total quantity is above a certain level.

Q18-Timbr

            SELECT `ordered_by[customer].customer_name` customer_name,
`ordered_by[customer].customer_key` customer_key, 
`order_key`, 
`order_date`,
`total_price`, 
SUM(`has_line_item[line_item].quantity`) quantity
FROM `dtimbr`.`order`
WHERE `has_line_item[line_item].total_quantity` > 300
GROUP BY `ordered_by[customer].customer_name`, `ordered_by[customer].customer_key`, `order_key`, `order_date`, `total_price`
ORDER BY `total_price` DESC, `order_date`
        

Q18-TPC

            SELECT c_name,  
c_custkey,  
o_orderkey,  
o_orderdate,  
o_totalprice,  
SUM(l_quantity) AS quantity 
FROM tpc.customer  
INNER JOIN tpc.`order` ON c_custkey = o_custkey
INNER JOIN tpc.lineitem ON o_orderkey = l_orderkey  
WHERE o_orderkey IN (SELECT l_orderkey FROM tpc.lineitem GROUP BY l_orderkey HAVING sum(l_quantity) > 300) 
GROUP BY c_name, c_custkey, o_orderkey, o_orderdate, o_totalprice  
ORDER BY o_totalprice DESC, o_orderdate
        

In this query, we use the relationships: ordered_by[customer], has_line_item[line_item], instead of using JOINS.

This query reports the gross discounted revenue attributed to the sale of selected parts handled in a particular manner. This query is an example of code such as might be produced programmatically by a data mining tool.

Q19-Timbr

            SELECT SUM(`volume`) AS revenue
FROM `dtimbr`.`in_person_air_delivery_line_time`
WHERE (`has_product[sm_product].brand` = 'Brand#12'
AND `quantity` >= 1 AND `quantity` <= 1 + 10
AND `has_product[sm_product].size` BETWEEN 1 AND 5)
OR  (`has_product[med_product].brand` = 'Brand#23'
AND `quantity` >= 10 AND `quantity` <= 10 + 10
AND `has_product[med_product].size` BETWEEN 1 AND 10)
OR  (`has_product[lg_product].brand` = 'Brand#34'
AND `quantity` >= 20 AND `quantity` <= 20 + 10
AND `has_product[lg_product].size` BETWEEN 1 AND 15)

        

Q19-TPC

            SELECT SUM(l_extendedprice * (1 - l_discount)) AS revenue  
FROM tpc.lineitem
INNER JOIN tpc.part ON p_partkey = l_partkey
WHERE (p_brand = 'Brand#12'  
AND p_container IN ( 'SM CASE', 'SM BOX', 'SM PACK', 'SM PKG')  
AND l_quantity >= 1 AND l_quantity <= 1 + 10  
AND p_size BETWEEN 1 AND 5  
AND l_shipmode IN ('AIR', 'AIR REG')  
AND l_shipinstruct = 'DELIVER IN PERSON')  
OR  (p_brand = 'Brand#23'  
AND p_container IN ('MED BAG', 'MED BOX', 'MED PKG', 'MED PACK')  
AND l_quantity >= 10 AND l_quantity <= 10 + 10  
AND p_size BETWEEN 1 AND 10  
AND l_shipmode IN ('AIR', 'AIR REG')  
AND l_shipinstruct = 'DELIVER IN PERSON')  
OR  (p_brand = 'Brand#34'  
AND p_container IN ( 'LG CASE', 'LG BOX', 'LG PACK', 'LG PKG')  
AND l_quantity >= 20 AND l_quantity <= 20 + 10  
AND p_size BETWEEN 1 AND 15  
AND l_shipmode IN ('AIR', 'AIR REG')  
AND l_shipinstruct = 'DELIVER IN PERSON')
        

In this query, we use two classifications of concepts in the ontology. First, we created a child concept of the line_item concept called in_person_air_delivery_line_time, and it contains only data where ship_instruct = ‘DELIVER IN PERSON’ AND `ship_mode` IN (‘AIR’, ‘AIR REG’). Then, we created three child concepts of the product concept called sm_product, med_product and lg_product, when each contains data filtered by a different container. For example, the sm_product concept contains only data where `container` IN (‘SM CASE’, ‘SM BOX’, ‘SM PACK’, ‘SM PKG’). Also, we use the has_product relationship instead of using JOINS.

This query identifies suppliers in a particular nation having selected parts that may be candidates for a promotional offer.

Q20-Timbr

            SELECT DISTINCT `supplier_name`, 
`supplier_address`
FROM `dtimbr`.`canadian_supplier` supplier
WHERE `from_supplier[forest_product]_available_quantity` > 
(SELECT 0.5 * sum(quantity) 
FROM dtimbr.line_item item
WHERE `ship_date_year` = 1994 
AND `has_product[forest_product].product_type` IS NOT NULL
AND item.`supplier_key` = supplier.supplier_key)
ORDER BY `supplier_name`
        

Q20-TPC

            SELECT s_name,  
s_address  
FROM tpc.supplier 
INNER JOIN tpc.nation ON s_nationkey = n_nationkey
WHERE s_suppkey in (
  SELECT ps_suppkey  
  FROM tpc.partsupp  
  WHERE ps_partkey in (
    SELECT p_partkey  
    FROM tpc.part  
    WHERE p_name like 'forest%')  
    AND ps_availqty > (
      SELECT 0.5 * sum(l_quantity)  
      FROM tpc.lineitem  
      WHERE l_partkey = ps_partkey  
      AND l_suppkey = ps_suppkey  
      AND l_shipdate >= date('1994-01-01')  
      AND l_shipdate < date('1994-01-01') + INTERVAL '1' year))  
AND n_name = 'CANADA'  
ORDER BY s_name
        

In this query, we created a child concept for the concept product called forest_product, and it contains only data where product_name like ‘forest%’. We also use the relationships: from_supplier[forest_product], has_nation[nation], instead of using JOINS.

This query identifies certain suppliers who were not able to ship required parts in a timely manner.

Q21-Timbr

            SELECT `supplier_name`, 
COUNT(distinct `has_line_item[late_line_item].order_key`) AS num_wait
FROM `dtimbr`.`saudi_arabia_supplier`
WHERE `has_line_item[late_line_item].of_order[order].order_status` = 'F' 
AND `has_line_item[late_line_item].supplier_key` != `has_line_item[late_line_item].in_same_order[line_item].supplier_key`
AND NOT EXISTS (
  SELECT *  
  FROM timbr.late_line_item
  WHERE order_key = `has_line_item[late_line_item].order_key`
  AND supplier_key <> `has_line_item[late_line_item].supplier_key` 
)  
GROUP BY `supplier_name`
ORDER BY `num_wait` DESC, `supplier_name`
        

Q21-TPC

            SELECT s_name,  
COUNT(*) AS numwait  
FROM tpc.supplier  
INNER JOIN tpc.lineitem l1 ON s_suppkey = l1.l_suppkey
INNER JOIN tpc.`order` ON o_orderkey = l1.l_orderkey
INNER JOIN tpc.nation ON s_nationkey = n_nationkey
WHERE o_orderstatus = 'F'  
AND l1.l_receiptdate > l1.l_commitdate  
AND EXISTS (
  SELECT  *  
  FROM tpc.lineitem l2  
  WHERE l2.l_orderkey = l1.l_orderkey  
  AND l2.l_suppkey <> l1.l_suppkey
)  
AND NOT EXISTS (
  SELECT *  
  FROM tpc.lineitem l3  
  WHERE l3.l_orderkey = l1.l_orderkey  
  AND l3.l_suppkey <> l1.l_suppkey  
  AND l3.l_receiptdate > l3.l_commitdate
)  
AND n_name = 'SAUDI ARABIA'  
GROUP BY s_name  
ORDER BY numwait DESC, s_name
        

In this query, we use several relationships instead of using JOINS, and a classification of concepts in the ontology. In this example, we created a child concept of the supplier concept called middle_east_supplier, and a child concept of middle_east supplier called saudi_arabia_supplier, and it contains only data where nation_key = ’20’. We also created a child concept of the line_item concept called late_line_item, and it contains only data where receipt_date > commit_date.

This query identifies geographies where there are customers who may be likely to make a purchase.

Q22-Timbr

            SELECT `phone_country_code`,
COUNT(*) AS `customer_number`,
SUM(`customer_account_balance`) total_account_balance
FROM dtimbr.`global_customer`
WHERE `customer_account_balance` > average_global_account_balance
AND `has_ordered[order].customer_key` IS NULL
GROUP BY `phone_country_code`
ORDER BY `phone_country_code`
        

Q22-TPC

            SELECT cntrycode,  
COUNT(*) AS numcust,  
SUM(c_acctbal) AS totacctbal  
FROM (
  SELECT SUBSTRING(c_phone FROM 1 for 2) AS cntrycode,  
  c_acctbal  
  FROM tpc.customer  
  WHERE SUBSTRING(c_phone FROM 1 for 2) IN ('13','31','23','29','30','18','17')  
  AND c_acctbal > (  
    SELECT AVG(c_acctbal)  
    FROM tpc.customer  
    WHERE c_acctbal > 0.00  
    AND SUBSTRING (c_phone FROM 1 for 2) IN ('13','31','23','29','30','18','17'))  
  AND NOT exists (
    SELECT * 
    FROM tpc.`order` 
    WHERE o_custkey = c_custkey)
) AS custsale  
GROUP BY cntrycode  
ORDER BY cntrycode
        

In this query, we use a classification of concepts in the ontology, and several relationships instead of using an Exists statement. In this example, we created a child concept of the customer concept called global_customer, and it contains only data where `phone_country_code` IN (’13’, ’31’, ’23’, ’29’, ’30’, ’18’, ’17’).

Thank You!

Our team has received your inquiry and will follow up with you shortly.

In the meantime, we invite you to watch demo and presentation videos of Timbr in our Youtube channel:

Partner programs enquiry

The information you provide will be used in accordance with the terms of our

privacy policy.

Schedule Meeting

Model a Timbr SQL Knowledge Graph in just a few minutes and learn how easy it is to explore and query your data with the semantic graph

Model a Timbr SQL Knowledge Graph in just a few minutes and learn how easy it is to explore and query your data with the semantic graph

Graph Exploration

Register to try for free

The information you provide will be used in accordance with the terms of our privacy policy.

Talk to an Expert

The information you provide will be used in accordance with the terms of our

privacy policy.