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

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

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.
Fan

## 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.
Fan

## 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;
Fan

## 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

Fan

## Re: how to compute the kpi using sql

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

Best regards.