Run Queries on Data

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.

SQL

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 5 unshipped orders with the highest value.

SQL

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 5 customers that may be experiencing issues with the items shipped to them.

SQL

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.

SQL

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.

In SQL Editor, select Visual Explain and select Profile.
The Visual Explain window opens. You can now explore the query and navigate around different operations.

Run Queries on Data

On this page

Query 1: Pricing Summary Report

Query 2: Shipping Priority

Query 3: Returned Item Reporting

Query 4: Product Type Profit Measure

Analyze Query Performance

Was this article helpful?

On this page

Was this article helpful?