Skip to content

Instantly share code, notes, and snippets.


cseidman/predictionsp.SQL Secret

Last active Jan 24, 2018
What would you like to do?
CREATE PROCEDURE [dbo].[usp_PredictedCustomers]
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
select C.[CustomerKey]
,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+'
,[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'
,[Education] = C.[EnglishEducation]
,[Occupation] = C.[EnglishOccupation]
,[IsHomeOwner] = convert(bit,C.[HouseOwnerFlag])
,[YearsSinceFirstPurchase] = Datediff(YEAR,C.DateFirstPurchase,getdate())
,[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
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.