Run Queries on Data
On this page
Now that the data is all loaded into your instance, you can start running queries on the database.
First, open up your SQL Editor to begin querying the data.
This query reports the amount of business that was billed, shipped, and returned within 60 - 120 days of the greatest ship data contained in the database.
use tpch;selectl_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_orderfromlineitemwherel_shipdate <= date('1998-12-01' - interval '90' day)group byl_returnflag,l_linestatusorder byl_returnflag,l_linestatus;
Run the query again and observe the execution time difference.
This query retrieves the 10 unshipped orders with the highest value.
use tpch;selectl_orderkey,sum(l_extendedprice * (1 - l_discount)) as revenue,o_orderdate,o_shippriorityfromcustomer,orders,lineitemwherec_mktsegment = 'BUILDING'and c_custkey = o_custkeyand l_orderkey = o_orderkeyand o_orderdate < date('1995-03-15')and l_shipdate > date('1995-03-15')group byl_orderkey,o_orderdate,o_shippriorityorder byrevenue desc,o_orderdatelimit 10;
This query identifies top 20 customers who might be having problems with the parts that are shipped to them.
use tpch;selectc_custkey,c_name,sum(l_extendedprice * (1 - l_discount)) as revenue,c_acctbal,n_name,c_address,c_phone,c_commentfromcustomer,orders,lineitem,nationwherec_custkey = o_custkeyand l_orderkey = o_orderkeyand o_orderdate >= date('1993-10-01')and o_orderdate < date('1993-10-01') + interval '3' monthand l_returnflag = 'R'and c_nationkey = n_nationkeygroup byc_custkey,c_name,c_acctbal,c_phone,n_name,c_address,c_commentorder byrevenue desclimit 20;
This query determines how much profit is made on a given line of parts, broken out by supplier nation and year.
use tpch;selectnation,o_year,sum(amount) as sum_profitfrom(selectn_name as nation,extract(year from o_orderdate) as o_year,l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amountfrompart,supplier,lineitem,partsupp,orders,nationwheres_suppkey = l_suppkeyand ps_suppkey = l_suppkeyand ps_partkey = l_partkeyand p_partkey = l_partkeyand o_orderkey = l_orderkeyand s_nationkey = n_nationkeyand p_name like '%green%') as profitgroup bynation,o_yearorder bynation,o_year desc;
Want more queries? Click here to get a full set of the 22 queries for a complete TPC-H workload.
The SQL Editor has a convenient feature that allows you to record and visually analyze workload profiles at either the query or node level.
In SQL Editor, open the Query Plan icon and click Profile.
The Visual Explain window will open up.
You can now explore the query and navigate around different operations.
Last modified: April 3, 2023