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 QuestionThe 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 DefinitionSELECT 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 ParametersValues 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]