Find possible customer churn

Suppose a telecommunications company stores the customer phone charge data in a binary file TelBill. Please find the 50 customers who are the most likely to lose out for retaining them. To achieve this, compare the phone charge in March of 2014 with the average monthly phone charge in the recent 1 year. If the charge drops sharply, then this customer can be taken as the one who are very likely to lose out.

Solution:

A B C
1 =file("TelBill") =A1.cursor@b()
2 =B1.select(Date>201303 && Date<=201403)  
3 =A2.new(ID,Date,Name,Amount,if(Date==201403,Amount,0):Last)  
4 =A3.groupx(ID,Name;sum(Amount):Total,max(Last):Last;100)  
5 =A4.new(ID,Name,Last,round(Total/12,2):Avg,round(Last/Avg,4):LastRatio)  
6 =A5.sortx(LastRatio)  
7 =A6.fetch(50)  
8 >A6.close()  

In A7, find the 50 customers whose proportions of phone charges in the last month are among the lowest. Their data is shown below: