Case Study Examples - Analytics Database - Teradata Vantage

These examples represent actual cases from a Vantage customer site. The examples are changed slightly to avoid disclosing any business critical information, but the basic syntax and counts remain accurate.

The example EXPLAIN reports are altered slightly for clarity, replacing site-specific aliases with generic database names. The EXPLAIN reports do not reflect the current EXPLAIN text features. This does not detract from their value as an instructional tool.

Defining the Business Question

Before writing a complex query, you must understand the business question it is supposed to answer. The following text is a simple explanation of the business question being asked in the case study.

• What are the customer numbers for all the customers in the customer table (which contains over 18 million rows)

  AND

  • Who resides in district K

    AND

  • Who has a service_type of ABC

    OR

  • Who has a service_type of XYZ

    AND

• What is their monthly_revenue for July 1997 (using the revenue table, which contains over 234 million rows)

  AND

• If the monthly_revenue for a customer is unknown (no revenue rows are found), then report the customer number (from customer.cust_num) with a null (represented by a QUESTION MARK (?) character) to represent its monthly_revenue.

This sounds simple, but when you analyze your answer sets carefully, you might find them to be incorrect. As the following topic demonstrates, the results are correct, but the outer join queries that produce them are not asking the proper questions. This becomes more apparent as the examples are developed and the results analyzed.

The study presents two example queries for determining the reasonableness of the answers of this study, three missteps along the path to getting the correct answer, and a properly coded query that returns the correct answer for the original question that was asked.