Who was the String Puller?

 

A week later the local police found the thieves and had them arrested. The picture was already sold on the black market two days after the coup, but for a reasonable chance for mitigation of punishment they disclosed the buyers and the picture could be brought back to the museum where it belongs.

Data Quality

But for the court trial, further evidence needs to be collected, and the question who actually initiated the fraud is open. For this, one may conclude, that the original initiator kept more money than the henchmen. So, have a look at the bank data of our thieves.

1.) Which tables are containing bank data?

2.) How are these tables connected with each other and with the names table? Which primary/foreign keys are there? 

3.) Which columns can you use to get the connection between names and the account table? Hint: Again, using pen and paper might be very helpful.

4.) Get the account numbers of our thieves.

Depending on the actual query, you might be surprised: Philipp does not have a bank account? But you are almost sure he has! This is a very common situation, that you do not get the results you expect. There are some possibilities:

  1. The SQL-query was not formulated in a suitable way
  2. The data does not exists
  3. The data is corrupt

Our experience with large databases is that the most common reason is the first one, followed by the third reason. So unclean data is a common issue. What can be done in this situation? Try not to compare the exact names with =, but try the like-comparator.

5.) Get a list of account details taking into account the different ways to spell Sarah or Philipp.

Who Got The Money?

6.) What is the sum of all transactions of Sarah?

7.) What is the sum of all transactions of Sarah between October 23 and October 25?

8.) Get a list of account numbers and sums of transactions for all accounts where the amount is greater than $10,000 in the time in question. Who is probably the initiator of the crime?

Have you found the criminal mastermind?