text: set ansi_nulls on
  go
  
  set quoted_identifier on
  go
  
  DROP PROCEDURE IF EXISTS PredictChurnR
  GO
  
  /* 
   Description: This file creates the procedure to predict churn outcome based on the open source R model previously built.
  */
  
  create procedure PredictChurnR @inquery nvarchar(max)
  as
  begin
    declare @modelt varbinary(max) = (select top 1 model from ChurnModelR);
    insert into ChurnPredictR
    exec sp_execute_external_script @language = N'R',
                                    @script = N'
  mod <- unserialize(as.raw(model));
  Scores <- data.frame(predict(mod, newdata = InputDataSet, type = "response"))
  colnames(Scores) <- c("Score");
  Scores$TagId <- as.numeric(as.character(InputDataSet$TagId))
  predictROC <- rxRoc(actualVarName = c("TagId"), predVarNames = c("Score"), data = Scores, numBreaks = 100)
  Auc = rxAuc(predictROC)
  OutputDataSet <- data.frame(InputDataSet$UserId,InputDataSet$Tag,InputDataSet$TagId,Scores$Score, Auc)
  '
  ,@input_data_1 = @inquery
  ,@output_data_1_name = N'OutputDataSet'
  ,@params = N'@model varbinary(max)'
  ,@model = @modelt;
  end
  go
  
  declare @query_string nvarchar(max)
  set @query_string='
  select F.*, Tags.Tag, Tags.TagId from
  (select a.* from Features a
  left outer join
  (
  select * from Features
  tablesample (70 percent) repeatable (98052)
  )b
  on a.UserId=b.UserId
  where b.UserId is null) F join Tags on F.UserId=Tags.UserId 
  '
  execute PredictChurnR @inquery = @query_string;
  go
  