Skip to content

Instantly share code, notes, and snippets.

@cseidman

cseidman/predictionsp.SQL Secret

Last active Jan 24, 2018
Embed
What would you like to do?
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
You can’t perform that action at this time.