Statistics in SQL: The Mann–Whitney U test

The series so far:

  1. Statistics in SQL: Pearson’s Correlation
  2. Statistics in SQL: Kendall’s Tau Rank Correlation
  3. Statistics in SQL: Simple Linear Regressions
  4. Statistics in SQL: The Kruskal–Wallis Test
  5. Statistics in SQL: The Mann–Whitney U Test
  6. Statistics in SQL: Student's T Test

Imagine you have some data. Maybe you’ve done a web redesign and you want to do some A/B testing to see if by redesigning the page your visitors spend more time reading your content. You set up an experiment in which visitors are randomly sent to either the old or new design, and you measure how long they spend engaged with the content. (you try to ensure that all other factors besides the design of the website are ‘controlled’, meaning that they are equal for both samples. Is the new design significantly better than the old one? You need to disprove the ‘null hypothesis’ that the two samples, from those who got the new design and those who got the old one are from the same population.

There are several ways that you can test this, but nobody is going to argue with you if you use a Mann–Whitney U test to test whether two samples come from the same distribution. It doesn’t require that the data has any particular type of distribution. It just requires that each observation is done by a different member of the population so that all the observations from both groups are independent of each other. It is really just a test of differences in mean-rank between two populations’ pooled ranking. To test this difference It has to be possible to compare any of the observations with any of the others and say which of the two are greater. Your objective is to disprove the assumption that The distributions of both populations are equal. Calculating a measure of the difference is simple, and was designed to be done easily by hand before computers. The probability that the observed difference occurred by chance is easily calculated for large samples because U then approximates to the normal distribution, but it is complex for small samples. Here, we have a small sample and are just interested in whether the two-tailed test is signifcant at the five percent level so we dodge the bullet by using a significance lookup table for the critical value of U.

This, when executed in SQL Server yields this result …

With all this series, I’m not giving you the cut n’ paste formula for doing production stats, The aim is to provide something you can experiment with, even if it just changing the figures and trying things out to see how easy it is to get from statistics theory to practice. It is much better to do this- even work things out on paper – before you get to crank the handle of a package such as R. There is too much magic in doing that if you don’t first get a feel for what is really being done, and what the assumptions are. The real expertise of statistics is in ensuring that the design of the test is right and in choosing the right statistical method for the task. Otherwise a tool like R merely gets you to the wrong answer quicker.