# 代做 c – C test

### C test

Review the data model described below. Afterwards, answer the 5 data questions by querying the three tables. List any assumptions you made with respect to the solution provided.

Table A Dim_Centres Region Centre_Id Centre_Name North N01 Manta North N02 Vaross East E01 Mavaka East E02 Bragow East E03 Ralo South S01 Verton South S01 Cosa West W01 Sleedburg

Table B Map_Centre_Cust a customer can only be assigned to 1 centre Centre_Id Cust_Id N01 1 N01 2 N02 3 E01 4 S01 5 S02 6 W01 7 W01 8

Table c Fact_Centre_Txn Centre_Id Cust_Id Visit_Date Transaction_Id Closing_Balance N01 1 01/06/2017 09:00 N01 00001 100 N01 1 01/06/2017 10:00 N01 00002 200 N01 1 01/06/2017 14:30 N01 00003 175 N01 1 02/06/2017 11:00 N01 00005 105 S02 6 01/06/2017 10:30 S020 0001 90 W01 8 03/06/2017 15:00 W01 00001 250

Q1: Display the last 100 customers who visited any bank centre and the date of their last visit.

Q2: For each customer, display the number of transactions in the bank over the last 60 days.

Q3: Produce a list of members who visited the bank more than once in a specific day and show the details of all transactions done in that day.

Q4: Display the amount of money kept at each bank centre per day for the current month.

Q5: List all bank centres and the number of customers assigned to the centre. In the same output, display the percentage of each centres customers with respect to its region.

Bonus: From the Q1 result, get the average number of days between the last and second-to-the-last transaction of the last 100 customers who went to the bank.