PredictionSP
CREATE PROCEDURE [dbo].[usp_PredictedCustomers] | |
as | |
DECLARE @model varbinary(max) | |
set @model = (SELECT top 1 model from dbo.predictivemodeldetail); | |
declare @PredictiveModelDetail int | |
-- Find the latest model of the type we're using for this prediction | |
set @PredictiveModelDetail = ( | |
select top 1 p.[PredictiveModelDetailId] | |
from [dbo].[PredictiveModelDetail] p join [dbo].[PredictiveModel] m on m.PredictiveModelId = p.PredictiveModelId | |
where m.ModelName = 'CUSTTREE' | |
order by TrainingDate DESC) ; | |
with d | |
as | |
( | |
select C.[CustomerKey] | |
,C.[CustomerAlternateKey] | |
,C.[FirstName] | |
,C.[MiddleName] | |
,C.[LastName] | |
,C.[Title] | |
,AgeRanges = case | |
when datediff(year,C.[BirthDate],getdate()) <= 30 then '11-30' | |
when datediff(year,C.[BirthDate],getdate()) between 31 and 50 then '31-50' | |
when datediff(year,C.[BirthDate],getdate()) between 41 and 70 then '51-70' | |
when datediff(year,C.[BirthDate],getdate()) > 70 then '71+' | |
end | |
,[IncomeCategories] = case | |
when C.YearlyIncome <= 20000 then 'Low' | |
when C.YearlyIncome between 20001 and 50000 then 'Lower' | |
when C.YearlyIncome between 50001 and 70000 then 'Middle' | |
when C.YearlyIncome between 70001 and 100000 then 'Upper' | |
when C.YearlyIncome > 100000 then 'Wealthy' | |
end | |
,C.[MaritalStatus] | |
,C.[Gender] | |
,C.[TotalChildren] | |
,C.[NumberChildrenAtHome] | |
,[Education] = C.[EnglishEducation] | |
,[Occupation] = C.[EnglishOccupation] | |
,[IsHomeOwner] = convert(bit,C.[HouseOwnerFlag]) | |
,C.[NumberCarsOwned] | |
,[YearsSinceFirstPurchase] = Datediff(YEAR,C.DateFirstPurchase,getdate()) | |
,C.[CommuteDistance] | |
,[CountryRegionCode] = ISNULL(G.[CountryRegionCode],'US') | |
,[PredictiveModelDetailId] = @PredictiveModelDetail | |
from [dbo].[DimCustomer] C | |
left join [dbo].[DimGeography] G on c.GeographyKey = g.GeographyKey | |
) | |
SELECT CustomerKey,FALSE_Pred,TRUE_Pred, IsCardUser = convert(bit,iif(TRUE_pred>=.5,1,0)),PredictiveModelDetailId | |
FROM | |
PREDICT( | |
MODEL = @model | |
,DATA = d | |
) WITH (FALSE_Pred float, TRUE_Pred float) as P | |
order by [CustomerKey] |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment