The objective is to correctly match the information in Input to the applicant from Input and thus accurately identify the applicant’s credit score. Assume a default threshold of 0.5. A higher threshold means that the matching accuracy is higher. Look for exact matches (NominalMatchColumns) to the email address and allow approximate matches (FuzzyMatchColumns) for lastname, firstname, zipcode, city and department columns, with different match metrics and match weights.
SELECT * FROM IdentityMatch (
ON applicant_reference AS a PARTITION BY ANY
ON applicant_external AS b DIMENSION
IDColumn ('a.id: b.id')
NominalMatchColumns ('a.email: b.email')
FuzzyMatchColumns ('a.lastname: b.lastname, JARO-WINKLER, 3',
'a.firstname: b.firstname, JARO-WINKLER, 2',
'a.zipcode: b.zipcode, JD, 2',
'a.city: b.city, LD, 2',
'a.department: b.department, COSINE, 1')
Accumulate ('a.firstname','a.lastname' ,'b.lastname', 'a.email',
'b.email','a.zipcode', 'b.zipcode', 'a.department',
'b.department','b.creditscore')
Threshold (0.5)
) ORDER BY "a.id", score DESC;