<h2 class="wp-block-heading">More Random Events</h2>



Following up on last week’s post on <a href="https://www.sisense.com/blog/beyond-random-normal-distributions-in-sql/">normal distributions</a>, this week we’re going to show off another common probability distribution.



Let’s say you typically sell 5 widgets per day. How likely is it that you’ll sell 5 widgets tomorrow? What about between 4 and 6 widgets tomorrow? Obviously we can’t just guess randomly. And the normal distribution won’t help either.



Fortunately, this is what the <a href="https://en.wikipedia.org/wiki/Poisson_distribution" target="_blank" rel="noreferrer noopener" aria-label=" (opens in a new tab)">Poisson Distribution</a> is for. Its formula is:



<figure class="wp-block-image"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-01-15.png" alt="Poisson formula" class="wp-image-78603"/></figure>



Our Poisson Distribution formula takes 3 inputs:



<ul><li>R: Our known rate, in this case 5.</li><li>e: Euler’s Number, 2.71828.</li><li>k: tomorrow’s expected rate.</li></ul>



<h2 class="wp-block-heading">Breaking It Down In SQL</h2>



Let’s start by plotting the Distribution over several expected conversion rates (k) per day. As before, we’ll use generate_series for convenience, so MySQL and Redshift users should try out alternatives.



<pre class="wp-block-code"><code>with numbers as (
 select * from generate_series(0, 20) k
)
select
 k,
 (power(2.71828, -5) * power(5, k) / (k !)) pr
from numbers
order by 1</code></pre>



Using our favorite visualization tool, this gives us a graph that looks like this:



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-02-15.png" alt="Distribution graph" class="wp-image-78609"/></figure>



In other words, there’s an 18% chance that we’ll sell exactly 5 widgets tomorrow. The chances of selling more than 5 decrease rather quickly. There is virtually no chance of selling 15 widgets tomorrow.



<h2 class="wp-block-heading">Asking Specific Questions</h2>



At the top of the post, we asked how likely it was that we’d sell between 4 and 6 widgets tomorrow. To ask a specific question like that, we need only add a where clause:



<pre class="wp-block-code"><code>where k between 4 and 6</code></pre>



Putting it all together, we get SQL that looks like this:



<pre class="wp-block-code"><code>with numbers as (
 select * from generate_series(0, 20) k
)
select
 sum((power(2.71828, -5) * power(5, k) / (k !)) pr
from numbers
where k between 4 and 6
order by 1</code></pre>





In this case, there’s a 50% chance that we’ll sell between 4 and 6 widgets. Not bad!

Estimating Conversions with Poisson Distributions

LinkedIn

Twitter

GitHub

curve-image-unique-image-unique

curve

3-dark-2-image-unique-image-unique

3 DARK 2

Get the latest in analytics right in your inbox.

Article