REQUIREMENTS

The following requirements must be met and submitted ot OneSure.

$4\mathrm{.}1$ ETL requirements

Write a SQL script that combines all customer information from the three difference

systems. $[$SecureInvest$,$ RetInvest and LifeInvest$]$

The data must be stored in a table that contains the below attributes.

Customer ID

First name

Last Name

Date of birth

Country of birth

Policy Type $["$Retirement Annuity", "Life Policy"," Investment"$]$

Policy Activation Date

Source System Name $["$Name of the system that provided this record"$]$

Master Record $["$is the record that is known as the master record due to business rules"$]$

Business Rules:

The most trusted source of data is "Lifelnvest" If a customer exists in Lifelnvest

make that the master record for that customer.

If the customer does not exist in Lifelnvest but exists in RetInvest and

Securelnvest, you need to compare the dates of "Policy Activation Date" and

mark the master record as the record that has the max$/$latest Policy Activation

Date.

$4\mathrm{.}2$ Report Requirements

Create a report that shows a unique list of potential politically exposed and sanctioned

customers as well as the number of times each customer record matched the

SanctionedList. Below are the report output column names:

Customer ID

First name

Last Name

Country of Origan

Source System Name

Master Record $[$Y$/$N$]--$ Hint: Should always be Y

Number of Source system Matches

The regulatory team is very happy with the report that they have received, however they need

the top $10\%$ of the customers with the most matches as they need to prioritize which customers

they need to look into first. Create a report that only shows the top $10\%$ of customers with the

most matches