Database Reference
In-Depth Information
Creating a Cumulative Amount Report
Another common analytics request is creating a Cumulative Amount Report. If you take the case of a
billing system, for example, the revenue generated from each customer is usually stored at the billing
transaction level. However, in many cases, analytics requirements may force you to turn that amount
into a running cumulative amount over the lifetime of the customer. Take the example of the reoccur-
ring payment table shown in Figure 10-14.
Figure 10-14: Reoccurring Payment table.
Going from PaymentAmount to CumulativePaymentAmount over the PaymentPeriodKey requires
making several passes at the table to aggregate PaymentAmount over every PaymentPeriodKey in
sequential order.
The query for solving this problem is a Self Join back into the ReoccurringPayment on the keys
of the table; see Listing 10-8.
Listing 10-8:  A Cumulative Amount Report
Select r1.ProductKey,
r1.CustomerKey,
r2.PaymentPeriodKey,
Sum(r1.PaymentAmount) CumulativePaymentAmount
From ReoccurringPayment r1
Join ReoccurringPayment r2
On r1.ProductKey = r2.ProductKey
And r1.CustomerKey = r2.CustomerKey
Where r1.PaymentPeriodKey<=r2.PaymentPeriodKey
Group By r1.ProductKey, r1.CustomerKey, r2.PaymentPeriodKey
Order By r1.ProductKey, r1.CustomerKey, r2.PaymentPeriodKey
The query in Listing 10-8 is a less than or equal to query that simulates running through every combi-
nation of ProductKey and CustomerKey, and carries the amount from one PaymentPeriodKey to the
next to create the cumulative report.
Creating a Top Performers Report
You may need to create a Top Performers Report related to customers or salespeople. For example,
who are your top 10 customers in terms of revenue? Answering this question is relatively easy with
SQL. A Group By query works.
 
Search WWH ::




Custom Search