Sign inTry Free

Run Queries on Data

On this page

You can start running queries once the data is loaded into your SingleStore database. Here are some example queries to be used with the TPC-H dataset. For the complete workload of 22 queries that can be run against the TPC-H database, refer to TPC-H standard specification.

Query 1: Pricing Summary Report

This query reports the total number of items, their prices, and average quantities shipped within 90 days of the most recent ship date (specified date) in the database.

USE tpch;
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
    lineitem
WHERE
    l_shipdate <= date('1998-12-01' - interval '90' day)
GROUP BY
    l_returnflag,
    l_linestatus
ORDER BY
    l_returnflag,
    l_linestatus;
+--------------+--------------+--------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
| l_returnflag | l_linestatus | sum_qty      | sum_base_price  | sum_disc_price    | sum_charge          | avg_qty   | avg_price    | avg_disc | count_order |
+--------------+--------------+--------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
| A            | F            |  83289121.00 | 124912195605.43 | 118665316925.8745 | 123413447483.580151 | 25.491969 | 38231.377828 | 0.050007 |     3267269 |
| N            | F            |   2190539.00 |   3282604241.36 |   3118652485.5821 |   3243864574.415261 | 25.478790 | 38180.915863 | 0.049948 |       85975 |
| N            | O            | 164028717.00 | 245978182329.81 | 233676744963.8986 | 243022073269.855210 | 25.499480 | 38239.131169 | 0.050004 |     6432630 |
| R            | F            |  83339258.00 | 124970353864.21 | 118715983030.2238 | 123467940050.739462 | 25.517608 | 38264.613849 | 0.050047 |     3265951 |
+--------------+--------------+--------------+-----------------+-------------------+---------------------+-----------+--------------+----------+-------------+
4 rows in set (4.17 sec)

Note

Run the query again and observe the execution time difference. You will see a dramatic improvement in query execution time. This is due to SingleStore’s Code Generation technology.

Query 2: Shipping Priority

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

USE tpch;
SELECT
    l_orderkey,
    SUM(l_extendedprice * (1 - l_discount)) AS revenue,
    o_orderdate,
    o_shippriority
FROM
    customer,
    orders,
    lineitem
WHERE
    c_mktsegment = 'BUILDING'
    AND c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    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
LIMIT 5;
+------------+-------------+-------------+----------------+
| l_orderkey | revenue     | o_orderdate | o_shippriority |
+------------+-------------+-------------+----------------+
|   81316036 | 459781.3856 | 1995-02-24  |              0 |
|   54427397 | 391221.7016 | 1995-02-28  |              0 |
|   82073798 | 390074.7002 | 1995-03-12  |              0 |
|   81872546 | 386608.3992 | 1995-02-28  |              0 |
|   45138020 | 386056.2188 | 1995-01-30  |              0 |
+------------+-------------+-------------+----------------+
5 rows in set (46.47 sec)

Query 3: Returned Item Reporting

This query identifies the top 20 customers that may be experiencing issues with the items shipped to them.

USE tpch;
SELECT
    c_custkey,
    c_name,
    SUM(l_extendedprice * (1 - l_discount)) AS revenue,
    c_acctbal,
    n_name,
    c_address,
    c_phone,
    c_comment
FROM
    customer,
    orders,
    lineitem,
    nation
WHERE
    c_custkey = o_custkey
    AND l_orderkey = o_orderkey
    AND o_orderdate >= DATE('1993-10-01')
    AND o_orderdate < DATE('1993-10-01') + interval '3' month
    AND l_returnflag = 'R'
    AND c_nationkey = n_nationkey
GROUP BY
    c_custkey,
    c_name,
    c_acctbal,
    c_phone,
    n_name,
    c_address,
    c_comment
ORDER BY
    revenue desc
LIMIT 5;
+-----------+--------------------+-------------+-----------+-----------+---------------------------------------+-----------------+------------------------------------------------------------------------------------------------+
| c_custkey | c_name             | revenue     | c_acctbal | n_name    | c_address                             | c_phone         | c_comment                                                                                      |
+-----------+--------------------+-------------+-----------+-----------+---------------------------------------+-----------------+------------------------------------------------------------------------------------------------+
|   2490736 | Customer#002490736 | 473138.4854 |   4701.80 | RUSSIA    | rjaIP,XKH,v0R 0u7Qyb                  | 32-234-811-3202 | s sleep carefully against the ironic pinto beans. blithely unusual deposits haggle furiously a |
|   7658659 | Customer#007658659 | 440156.4690 |   1442.76 | MOROCCO   | ucA926oE6y1fOr,teHGthr6GXz6y qf1ySK3  | 25-418-150-7556 | nusual requests nag blithely according to the regular requests: pending, unusual               |
|  11381863 | Customer#011381863 | 437190.0882 |   4397.51 | JORDAN    | TFd7KvQQmf6XJjSQUBbvZ9rlxTUle1mwfqt9  | 23-407-202-8891 |  lose blithely along the furiously pending sentiments. quickly final theodo                    |
|   4804573 | Customer#004804573 | 432390.5681 |   4930.12 | INDONESIA | LgPei3cnZL19sW8lM,qM                  | 19-664-439-8190 | ial accounts. blithely final platelets breach. furious                                         |
|   2247622 | Customer#002247622 | 414271.9102 |   4292.76 | PERU      | c5eYVZ7PXycKnsgLmCtarE5aCcWYga5u0RIwa | 27-403-465-9280 | ly. sly packages haggle. even theodolites wa                                                   |
+-----------+--------------------+-------------+-----------+-----------+---------------------------------------+-----------------+------------------------------------------------------------------------------------------------+
5 rows in set (37.33 sec)

Query 4: Product Type Profit Measure

This query calculates the profit made on a given line of items, broken down by supplier nation and year.

USE tpch;
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
            part,
            supplier,
            lineitem,
            partsupp,
            orders,
            nation
        WHERE
            s_suppkey = l_suppkey
            AND ps_suppkey = l_suppkey
            AND ps_partkey = l_partkey
            AND p_partkey = l_partkey
            AND o_orderkey = l_orderkey
            AND s_nationkey = n_nationkey
            AND p_name LIKE '%green%'
    ) AS profit
GROUP BY
    nation,
    o_year
ORDER BY
    nation,
    o_year DESC
    LIMIT 5;
+---------+--------+---------------+
| nation  | o_year | sum_profit    |
+---------+--------+---------------+
| ALGERIA |   1998 | 20613196.5474 |
| ALGERIA |   1997 | 37498211.6758 |
| ALGERIA |   1996 | 35471708.2126 |
| ALGERIA |   1995 | 36720114.9923 |
| ALGERIA |   1994 | 34921048.6736 |
+---------+--------+---------------+
5 rows in set (49.48 sec)

Analyze Query Performance

The SQL Editor allows you to record and visualize workload profiles at either the query or node level.

  1. In SQL Editor, select Visual Explain and select Profile.

  2. The Visual Explain window opens. You can now explore the query and navigate around different operations.

Last modified: October 17, 2024

Was this article helpful?

On this page

Was this article helpful?