At Sisense, we continuously help people build, diagnose, and update their data. When approaching a table of data for the first time, it helps our understanding to see summarizing statistics.



By looking at such staples as the range, sum, and average, you can get a quick glance at the shape and distribution of the data before taking a deep dive.



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-01-11.png" alt="" class="wp-image-78286"/></figure>



Here we will demonstrate how we append this data to our SQL tables. ​&nbsp;



<h2 class="wp-block-heading">Understanding Blog Post Metrics</h2>



Whenever we write a blog post, we want to understand if readers are visiting our homepage and signing up for a trial. We have tables&nbsp;blog_pings,&nbsp;homepage_pings, and&nbsp;signup_pings&nbsp;that let us observe when users visit the homepage after reading a blog post.



We want to count visitors to the blog even if they did not visit the homepage. Since&nbsp;blog_pings&nbsp;includes everyone in&nbsp;homepage_pings, which in turn includes all of&nbsp;signup_pings, we use&nbsp;left join&nbsp;to merge these records without losing any of the rows.



We want to count visitors to the blog even if they did not visit the homepage. Since blog_pings includes everyone in homepage_pings, which in turn includes all of signup_pings, we use left join to merge these records without losing any of the rows.



<pre class="wp-block-code"><code>select
 post
 , count(distinct blog_pings.cookie) as blog
 , count(distinct homepage_pings.cookie) as homepage
 , count(distinct signup_pings.cookie) as signups
from
 blog_pings
 left join homepage_pings on
 blog_pings.cookie = homepage_pings.cookie
 left join signup_pings on
 blog_pings.cookie = signup_pings.cookie
group by
 1</code></pre>



Running this query gives us the counts of visitors who visited the blog page, the home page, and those who signed up for a trial.&nbsp;



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-02-12.png" alt="Visitor count table" class="wp-image-78280"/></figure>



This table makes it very easy to analyze the conversion rates of the most active and least active blog posts, but not how an average post performed just by scrolling through the table. We can add the average and standard deviation as headers to the table to guide the analyst when checking up on a blog post.



First, we bundle the previous query into a temporary table using&nbsp;with&nbsp;blog_signups&nbsp;as&nbsp;(&#8230;)



<pre class="wp-block-code"><code>with blog_signups as (
 select
 post
 , count(distinct blog_pings.cookie) as blog
 , count(distinct homepage_pings.cookie) as homepage
 , count(distinct signup_pings.cookie) as signups
 from
 blog_pings
 left join homepage_pings on
 blog_pings.cookie = homepage_pings.cookie
 left join signup_pings on
 blog_pings.cookie = signup_pings.cookie
 group by
 1
)</code></pre>



 Now we can pull statistics like the total, mean, and standard deviation from&nbsp;blog_signups. 



<pre class="wp-block-code"><code>select 'Total', sum(blog), sum(homepage),
 sum(signups) from blog_signups
union select 'Average', avg(blog), avg(homepage),
 avg(signups) from blog_signups
union select 'Standard Deviation', stddev(blog),
 stddev(homepage), stddev(signups) from blog_signups</code></pre>



<figure class="wp-block-image"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-03-11.png" alt="Blog signups chart" class="wp-image-78274"/></figure>



Having these statistics is great, but it becomes tedious to check multiple data sources. We can address this by simply folding our statistics into the main table by&nbsp;unioning&nbsp;them together. 



<pre class="wp-block-code"><code>select 'Total', sum(blog), sum(homepage),
 sum(signups) from blog_signups
union select 'Average', avg(blog),
 avg(homepage), avg(signups) from blog_signups
union select 'Standard Deviation', stddev(blog),
 stddev(homepage), stddev(signups) from blog_signups
union select * from blog_signups</code></pre>



 This will make sorting the table difficult, since the Total, Average, and Standard Deviation are on either ends of the range of the data. To help us in sorting the data, we can add an&nbsp;index&nbsp;column that we will use as a primary sorting key. First, we add an index to&nbsp;blog_signups: 



<pre class="wp-block-code"><code>with blog_signups as (
 select
 1 as index
 , post
 , count(distinct blog_pings.cookie) as blog
 , count(distinct homepage_pings.cookie) as homepage
 , count(distinct signup_pings.cookie) as signups
 from
 blog_pings
 left join homepage_pings on
 blog_pings.cookie = homepage_pings.cookie
 left join signup_pings on
 blog_pings.cookie = signup_pings.cookie
 group by
 1, 2
)</code></pre>



 Then we index the statistics in our order preference: 



<pre class="wp-block-code"><code>select 4, 'Total', sum(blog), sum(homepage),
 sum(signups) from blog_signups
union select 3, 'Average', avg(blog), avg(homepage),
 avg(signups) from blog_signups
union select 2, 'Standard Deviation', stddev(blog),
 stddev(homepage), stddev(signups) from blog_signups</code></pre>



 Now we wrap this entire query into another&nbsp;with&nbsp;block which we call&nbsp;signup_statistics: 



<pre class="wp-block-code"><code>with signup_statistics as (
 with blog_signups as (...)
 select 4, 'Total', sum(blog), sum(homepage),
 sum(signups) from blog_signups
 union select 3, 'Average', avg(blog), avg(homepage),
 avg(signups) from blog_signups
 union select 2, 'Standard Deviation', stddev(blog),
 stddev(homepage), stddev(signups) from blog_signups
 union select * from blog_signups
)
select
 post
 , blog
 , homepage
 , signups
from
 signup_statistics
order by
 index desc
 , signups desc</code></pre>



<figure class="wp-block-image fancybox"><img decoding="async" src="https://cdn.sisense.com/wp-content/uploads/image-04-11.png" alt="Final table" class="wp-image-78292"/></figure>



And there we have it! Now whenever an author wants to check up on their post, they will be able to more easily understand how their post fared against the distribution.

Adding Summary Statistics to your SQL Table

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