Who was the string puller? - Data quality and aggregation functions
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.
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.
29.) Which tables are containing bank data?
30.) How are these tables connected with each other and with the names table? Which primary/foreign keys are there? Hint: Often it is a good idea to use pen and paper to draw a diagram denoting the connections. One possible way to to this is to represent tables by a list of their columns and to draw a line for every primary key-foreign key relationship. Ask a coach for details.
31.) 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.
32.) 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:
- The SQL-query was not formulated in a suitable way
- The data does not exists
- 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.
33.) Get a list of account details taking into account the different ways to spell Sarah or Philipp.
To deal with unclean data one sometimes has to be creative and to try different things to find the data one is looking for. Once again: If there is a primary key/foreign key connection, use it! These columns have extra checks by the database, so the probability that these are corrupt is much, much lower than for other columns. If there are no such primary-key columns, it is usually a bad database design which should be a quite rare exception.
Who got the money? - Aggregate Functions
If you want to calculate a quantity from several values from a column you use aggregate functions, e.g. average values or sums. One aggregate function we have already learned about: The count function. Important other functions are:
- avg() Returns the average value
- count() Returns the number of rows
- first() Returns the first value
- last() Returns the last value
- max() Returns the largest value
- min() Returns the smallest value
- sum() Returns the sum
Even if it does not make much sense, we compute the average of all phone numbers:
select avg(phone_number) from phone_contract;
34.) What is the sum of all transactions of Sarah?
35.) What is the sum of all transactions of Sarah between October 23 and October 25?
We could do now the same for Philipp. But we want to learn a few more useful commands first. SQL has a nice command for cases like this one, where you want to use an aggregate function for subsets according to values in another column:
select column_name, aggregate_function(column_name) from table_name where column_name operator value group by column_name;
For instance, if you want to know the sum of all transactions per account number, you can use:
select account_number_fk, sum(amount) from bank_transaction where date > "2014-10-22" and date < "2014-10-26" group by account_number_fk;
Note that group by can also be used for more than one column.
This information would have provided an alternative way to identify suspected persons: Anyone who has gotten more than, for instance 10,000€, during the time period in question in their bank account would have been a suspect. In this case, we want to formulate a condition on the result of an aggregated function. Here, we cannot use a where clause, instead we use the having clause:
select column_name, aggregate_function(column_name) from table_name where column_name operator value group by column_name having aggregate_function(column_name) operator value;
36.) 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? If yes, you might want to learn about some advanced topics.