Case Study Examples - Teradata VantageCloud Lake

These examples are from a Vantage customer site, changed enough to avoid disclosing business critical information.

The EXPLAIN reports are altered 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, understand the business question the query must 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 carefully, you may find your answer sets to be incorrect. As the following topic demonstrates, the results are correct, but the outer join queries that produce the results 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.