Sample Query Definition
2.3 Forecasting Revenue Query (Q6) This query quantifies the amount of revenue increase that would have resulted from eliminating company-wide discounts in a given percentage range in a given year. Asking this type of what if query can be used to look for ways to increase revenues.
2.3.1 Business Question The Forecasting Revenue Change Query considers all the lineitems shipped in a given year with discounts between DISCOUNT+0.01 and DISCOUNT-0.01. The query list the amount by which the total revenues would have decreased if these discounts had been eliminated for lineitems with item quantities less than QUANTITY. Note that the potential revenue increase is equal to the sum of (L_EXTENDEDPRICE * L_DISCOUNT) for all lineitems with quantities and discounts in the qualifying range.
2.3.2 Functional Query Definition SELECT SUM(L_EXTENDEDPRICE*L_DISCOUNT) AS REVENUE FROM LINEITEM WHERE L_SHIPDATE >= DATE [DATE]] AND L_SHIPDATE < DATE [DATE] + INTERVAL 1 YEAR AND L_DISCOUNTBETWEEN [DISCOUNT] - 0.01 AND [DISCOUNT] + 0.01 AND L_QUANTITY < [QUANTITY]
2.8.3 Substitution Parameters Values for the following substitution parameters must be generated and used to build the executable query text.
1. DATE is the first of January of a randomly selected year within [1993-1997]
2. DISCOUNT is randomly selected within [0.02 .. 0.09]
3. QUANTITY is randomly selected within [24 .. 25]