SQL-Server-R-Services-Samples/FraudDetection/SQLR/Step0_CreateTables.sql at master · latuji/SQL-Server-R-Services-Samples · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
use [OnlineFraudDetection]
go

set ansi_nulls on
go

set quoted_identifier on
go

DROP TABLE IF EXISTS OnlineFraudDetection.dbo.untaggedData
GO

create table OnlineFraudDetection.dbo.untaggedData
(
transactionID varchar(255),
accountID varchar(255),
transactionAmountUSD varchar(255),
transactionAmount varchar(255),
transactionCurrencyCode varchar(255),
transactionCurrencyConversionRate varchar(255),
transactionDate varchar(255),
transactionTime varchar(255),
localHour varchar(255),
transactionScenario varchar(255),
transactionType varchar(255),
transactionMethod varchar(255),
transactionDeviceType varchar(255),
transactionDeviceId varchar(255),
transactionIPaddress varchar(255),
ipState varchar(255),
ipPostcode varchar(255),
ipCountryCode varchar(255),
isProxyIP varchar(255),
browserType varchar(255),
browserLanguage varchar(255),
paymentInstrumentType varchar(255),
cardType varchar(255),
cardNumberInputMethod varchar(255),
paymentInstrumentID varchar(255),
paymentBillingAddress varchar(255),
paymentBillingPostalCode varchar(255),
paymentBillingState varchar(255),
paymentBillingCountryCode varchar(255),
paymentBillingName varchar(255),
shippingAddress varchar(255),
shippingPostalCode varchar(255),
shippingCity varchar(255),
shippingState varchar(255),
shippingCountry varchar(255),
cvvVerifyResult varchar(255),
responseCode varchar(255),
digitalItemCount varchar(255),
physicalItemCount varchar(255),
purchaseProductType varchar(255),
accountOwnerName varchar(255),
accountAddress varchar(255),
accountPostalCode varchar(255),
accountCity varchar(255),
accountState varchar(255),
accountCountry varchar(255),
accountOpenDate varchar(255),
accountAge varchar(255),
isUserRegistered varchar(255),
paymentInstrumentAgeInAccount varchar(255),
sumPurchaseAmount1dPerUser varchar(255),
sumPurchaseAmount30dPerUser varchar(255),
sumPurchaseCount1dPerUser varchar(255),
sumPurchaseCount30dPerUser varchar(255),
numPaymentRejects1dPerUser varchar(255)
);

DROP TABLE IF EXISTS OnlineFraudDetection.dbo.fraud
GO

create table OnlineFraudDetection.dbo.fraud
(
transactionID varchar(255),
accountID varchar(255),
transactionAmount varchar(255),
transactionCurrencyCode varchar(255),
transactionDate varchar(255),
transactionTime varchar(255),
localHour varchar(255),
transactionDeviceId varchar(255),
transactionIPaddress varchar(255)
);

DROP TABLE IF EXISTS OnlineFraudDetection.dbo.sql_predict_score
GO

create table OnlineFraudDetection.dbo.sql_predict_score
 (
   accountID varchar(255),
   transactionDate varchar(255),
   transactionTime varchar(255),
   transactionAmountUSD float,
   Label int,
   Score float
 );

DROP TABLE IF EXISTS OnlineFraudDetection.dbo.sql_performance
GO

create table OnlineFraudDetection.dbo.sql_performance
(
  ADR varchar(255),
  PCT_NF_Acct varchar(255),
  Dol_Frd varchar(255),
  Do_NF varchar(255),
  VDR varchar(255),
  Acct_FP varchar(255),
  PCT_Frd varchar(255),
  PCT_NF varchar(255),
  AFPR varchar(255),
  TFPR varchar(255)
);

DROP TABLE IF EXISTS OnlineFraudDetection.dbo.sql_performance_auc
GO

create table OnlineFraudDetection.dbo.sql_performance_auc
(
  AUC float
);

DROP TABLE IF EXISTS dbo.sql_trained_model
GO

create table OnlineFraudDetection.dbo.sql_trained_model
 (
   model varbinary(max) not null
 );