the first table contain two columns the idclient and the amount. and the seconde table contain the amount and the score so the relation between them maybe the amount . by joining with a condition on the amount
here is all topic :
INTRO A company X has implemented a new CRM module to build customer loyalty.
INPUT On the one hand, X has an e-commerce site that manages all purchase transactions. attached is an example of an extract file from their e-commerce site.
LOOKUP In addition, attached is the scoring table that allows you to assign a score to each transaction rounded down to the nearest unit. For example: 80 MAD equals 0 points 100 MAD equals 2 points 140 MAD equals 2 points 200 MAD equals 6 points... BUSINESS RULES The score is calculated for each separate transaction, and then cumulated per user: Example Transaction 1 of client A: 99 MAD -> Score 0 Transaction 2 of client A: 99 MAD -> Score 0 Transaction 3 of client A: 120 MAD -> Score 2 ---> Total score of client A: 2 points.
The score grid is provided just as an example, only the format of the score file is important. The job should therefore be generic and treat the score file as input.
OUTPUT The expected output is a list of the different customers with their cumulative scores.
it's not just a cumulative score, but also it's about the condition on the join with row1.amount=row2.amount to get the right score for each client before i cumul.
i did a unique match to get the last row with the condition : row1.amount>=row2.amount but it's not working for me .
