Running sample queries with the Presto engine

In this tutorial, you use the sample sales data that is included with watsonx.data to run queries on the Presto engine. The gosales schema in the sample_data catalog contains a sample B2B sales dataset with order, product, retailer, and regional information.

Objectives

  • Run SQL queries against sample datasets.
  • Analyze sales and customer data with aggregations and joins.
  • Interpret query results and investigate business trends.

Before you begin

Make sure that your Presto engine is provisioned and in the Running state. You can see the status of your Presto engine and start it, if necessary, in the Infrastructure manager.

Example 1: Querying line-item revenue and profit

  1. From the watsonx.data console, open the Query workspace.
  2. Select the Presto engine.

  3. Copy and paste the following SQL statement into the query editor.

    -- Join order_header and order_details to see revenue per line item
SELECT
  oh.order_number,
  oh.retailer_name,
  oh.order_date,
  oh.order_method_code,
  od.product_number,
  od.quantity,
  od.unit_price,
  od.unit_cost,
  od.unit_sale_price,
  ROUND(od.quantity * od.unit_sale_price, 2)                    AS line_revenue,
  ROUND(od.quantity * (od.unit_sale_price - od.unit_cost), 2)   AS line_profit,
  ROUND(
    (od.unit_sale_price - od.unit_cost)
    / NULLIF(od.unit_sale_price, 0) * 100
  , 1)                                                          AS margin_pct
FROM sample_data.gosales.order_header  oh
JOIN sample_data.gosales.order_details od
  ON oh.order_number = od.order_number
ORDER BY oh.order_date DESC
LIMIT 100;
  4. Click Run on Presto.

Review the results

After the query completes, review the following columns:

order_method_code
Identifies the sales channel for the order. The values correspond to entries in the order_method lookup table and represent channels such as online, phone, fax, or sales representative sales.
unit_price and unit_sale_price
Can be compared to understand discount levels applied to each order.
margin_pct
Shows the profit margin percentage for each line item. Rows with lower margin values might indicate heavily discounted orders or products with higher costs.

Example 2: Querying retailer revenue by year

  1. In the query editor, copy and paste the following SQL statement.

    SELECT
  oh.retailer_name,
  YEAR(oh.order_date)                                    AS order_year,
  COUNT(DISTINCT oh.order_number)                        AS orders,
  SUM(od.quantity)                                       AS units_sold,
  ROUND(SUM(od.quantity * od.unit_sale_price), 2)        AS total_revenue,
  ROUND(SUM(od.quantity * (od.unit_sale_price
            - od.unit_cost)), 2)                         AS total_profit
FROM sample_data.gosales.order_header  oh
JOIN sample_data.gosales.order_details od
  ON oh.order_number = od.order_number
GROUP BY oh.retailer_name, YEAR(oh.order_date)
ORDER BY total_revenue DESC
LIMIT 30;
  2. Click Run on Presto.

Review the results

Review the results to identify revenue trends across retailers and years.

Tip: Add a WHERE clause that filters on YEAR(oh.order_date) to compare retailer performance for specific years. The sample dataset includes sales data across multiple years, making it useful for time-based analysis and reporting.

Further exploration

Try modifying the queries to:

  • Filter results by retailer, region, or product line.
  • Compare revenue across different years.
  • Analyze the relationship between discounts and profit margins.
  • Join additional tables in the gosales schema to answer business questions about sales performance.

Example 3: Analyzing sales performance across multiple tables

In this example, you run a query that joins tables from the gosales and gosalesct schemas to generate a regional revenue and profitability summary. The query combines sales transactions, product information, branch data, regional data, and customer order information to provide a consolidated view of business performance.

  1. In the query editor, copy and paste the following SQL statement.

    SELECT
    sr.sales_region_en                              AS sales_region,
    c.country_en                                    AS country,
    COUNT(DISTINCT oh.order_number)                 AS total_orders,
    COUNT(od.order_detail_code)                     AS total_line_items,
    SUM(od.quantity)                                AS total_units_sold,
    SUM(od.quantity * od.unit_cost)                 AS total_cost,
    SUM(od.quantity * od.unit_sale_price)           AS total_revenue,
    SUM(od.quantity * od.unit_sale_price)
        - SUM(od.quantity * od.unit_cost)           AS gross_profit,
    ROUND(
        (SUM(od.quantity * od.unit_sale_price)
            - SUM(od.quantity * od.unit_cost))
        / NULLIF(SUM(od.quantity * od.unit_sale_price), 0) * 100, 2
    )                                               AS gross_margin_pct,
    AVG(od.unit_sale_price)                         AS avg_unit_sale_price
FROM order_header oh
JOIN order_details od
    ON oh.order_number = od.order_number
JOIN product p
    ON od.product_number = p.product_number
JOIN branch b
    ON oh.sales_branch_code = b.branch_code
JOIN country c
    ON b.country_code = c.country_code
JOIN sales_region sr
    ON c.sales_region_code = sr.sales_region_code
GROUP BY
    sr.sales_region_en,
    c.country_en
ORDER BY
    total_revenue DESC
  2. Click Run on Presto.

Review the results

The query returns sales performance metrics by region, including revenue and margin calculations. Use the results to:

  • Compare revenue across sales regions.
  • Identify regions with the highest and lowest profit margins.
  • Analyze how product margins contribute to overall regional performance.
  • Evaluate sales trends across different geographic areas.
Tip: Regions with high revenue but low gross margin percentages might indicate pricing or discounting issues. Compare the regional margin values with average product margin values to determine whether the results are driven by product mix or pricing strategy.

Example 4: Analyzing customer orders

The gosalesct schema contains customer order data that you can use to analyze purchasing trends and customer activity.

  1. In the query editor, copy and paste the following SQL statement.

    SELECT
    cc.country_en AS country,
    COUNT(DISTINCT oh.cust_order_number) AS total_orders,
    COUNT(DISTINCT oh.cust_code) AS unique_customers,
    SUM(oh.cust_total_quantity) AS total_items_ordered,
    CAST(SUM(oh.cust_subtotal) AS DECIMAL(19,2)) AS total_subtotal,
    CAST(SUM(oh.cust_total) AS DECIMAL(19,2)) AS total_revenue
FROM
    gosalesct.cust_order_header oh
    JOIN gosalesct.cust_crdt_card cd ON oh.cust_cc_id = cd.cust_cc_id
    JOIN gosalesct.go_sales_tax st ON oh.cust_sales_tax = st.sales_tax_rate
    JOIN gosalesct.cust_country cc ON st.cust_country_code = cc.country_code
GROUP BY cc.country_en
ORDER BY total_orders DESC
  2. Click Run on Presto.

Review the results

Review the customer order metrics to better understand purchasing behavior and order characteristics. Use the results to:

  • Identify customers with the highest order volume.
  • Compare purchasing activity across countries or regions.
  • Analyze order patterns over time.
  • Explore relationships between customer activity and sales performance.

Further exploration

Try modifying the queries to:

  • Filter results by region, country, or customer.
  • Add date filters to compare performance across time periods.
  • Rank regions based on revenue or profitability.
  • Join additional tables from the sample schemas to answer business-specific questions.