how to compute the kpi using sql

Analytics
Enthusiast

how to compute the kpi using sql

Hi,I need to compute a kpi named "Low-grade Silent Customers"
which means customers that haven't use our specific service for
3 months to 1 year.In our data warehouse,we store specific service
usage records in separate tables by month,and each table has more
than one hundred million recodes,who can give me a smart sql?
9 REPLIES
Enthusiast

Re: how to compute the kpi using sql

hi,
Make use of union..

Regds,
sachin
Enthusiast

Re: how to compute the kpi using sql


thanks for your reply, sachin,
if i want to get the kpi of 200707,
do you mean i should do it like this :

select serv_id from serv_t
minus
(select serv_id from cdr_t where stat_month = 200707
union
select serv_id from cdr_t where stat_month = 200706
union
select serv_id from cdr_t where stat_month = 200705)
minus
(select serv_id from serv_t
minus
(select serv_id from cdr_t where stat_month = 200707
union
select serv_id from cdr_t where stat_month = 200706
......
union
select serv_id from cdr_t where stat_month = 200608)
)
Enthusiast

Re: how to compute the kpi using sql

I believe Teradata Window Based function can address your problem.
Look up for OLAP functions in SQL reference manuals.

Thanks,
Vinay Bagare
Enthusiast

Re: how to compute the kpi using sql

Thank you,Vinay Bagare,I will find it right now.

Re: how to compute the kpi using sql

Hi

I read some opinions in this topic. I do not agree above ideal. We can find out some articles at about.com by using Google search.

Re: how to compute the kpi using sql

If you want to get more materials that related to this topic, you can visit: http://keyperformanceindicators.info/

Best regards.
Enthusiast

Re: how to compute the kpi using sql

Lets say you are doing this for a current_date. So first get the set of customers who are not there in last 3 months for a specific service. Then you can apply filter between last 1 year and current_date - (3 months) and use the set of players. This way you can get the result quicker while limiting the serach criteria to few customers and to the time frame using partitions.

Or to give a different solution... You can try last_active_date for each customer using the below syntax

select max(service_date) over( partition by customer_id) from table
where service_date between Date - 365 to Date;

Re: how to compute the kpi using sql

Hi

I found that a member asked same question in this forum some months ago.

Pls use search box to find this questions with comments

Re: how to compute the kpi using sql

If you want to get more materials that related to this topic, you can visit:

http://interviewquestionsandanswers.biz/case-manager-interview-questions/

Best regards.