Summary Statistics

When you get a new dataset, one of the first things you want to do is find your summary statistics. These essential numbers tell you the basics about your data so you start off your analysis on an informed foot.

The Basics

The main summary statistics are:

Min

Max

Average

Median

Mode

Inter-quartile Range

Standard Deviation

select * from PUBLIC.LONDON_WEATHER

DATE	TEMP	WEATHER	PRECIPITATION	VISIBILITY	WINDSPEED
2018-04-11	48.5	fog	0.2	2	5.7
2016-10-31	54.7	fog	0.01	2.5	2.1
2018-11-23	44.2	rain	0.01	2.5	5.5
2018-01-09	41.6	rain	null	3	5.5
2017-01-26	31	sunny	0	3	7.7
2017-02-13	42.8	sunny	0	3	11.9
2016-12-31	42.8	fog	null	3.5	5.7
2018-05-31	65.7	fog	0	3.5	5.1
2017-01-27	37.8	sunny	0	3.5	6.1
2018-03-02	30.6	rain	0	3.5	14.2
2019-02-16	47.4	sunny	0.01	3.5	6.3
2018-02-19	47.7	rain	0.02	3.5	5.4
2017-01-31	45	rain	0.03	3.5	7.1
2019-12-04	38.9	fog	0	4	2.3
2017-11-02	47.5	fog	0	4	2.7
2017-03-11	54.2	sunny	0	4	5.6
2018-05-29	59.5	fog	0	4	6.4
2017-03-16	48.7	fog	0	4	7.8
2018-03-24	46.2	rain	0.09	4	4.1
2016-12-28	36	fog	0.01	4	2.9
2019-11-09	40.4	fog	0.01	4	6
2018-05-30	60	rain	0.42	4	3.3
2018-04-14	55.2	sunny	0	4.5	4.5
2019-12-28	46.4	fog	0	4.5	5.7
2017-02-06	39.2	fog	0	4.5	5.1
2019-04-02	42.6	rain	0	4.5	5.8
2019-02-28	48.8	rain	0	4.5	8.6
2018-03-04	43	rain	0.19	4.5	7
2017-10-18	56.1	rain	0.01	4.5	4.3
2016-12-14	48.8	fog	0.01	4.5	4.9
2018-11-01	48	fog	0.16	4.5	5.3
2016-11-21	47.8	rain	0.4	4.5	8.8
2017-08-21	62.1	rain	0.26	4.5	4.9
2017-12-10	34.9	rain	0.21	4.5	6.1
2016-06-04	59.9	sunny	0	5	4.1
2019-11-08	40.7	fog	0	5	3.7
2018-10-05	60.5	sunny	0	5	4.3
2017-02-14	44.6	sunny	0	5	6.9
2019-04-30	53.5	sunny	0	5	4.3
2018-10-06	50.1	fog	0	5	6.8
2019-02-17	49	fog	0	5	8
2018-11-28	53.3	rain	0.27	5	11.7
2016-04-11	52.5	rain	0.05	5	6
2016-12-10	52.8	rain	0.03	5	8.4
2018-02-20	47.3	rain	0.06	5	7.8
2016-01-17	36.3	rain	0.07	5	6.4
2017-12-02	40.2	rain	null	5.5	4.8
2016-09-22	61.4	rain	null	5.5	6.6
2018-10-20	53.1	sunny	0	5.5	4
2019-01-11	45.5	rain	0	5.5	4.4
2017-05-07	54.5	sunny	0	5.5	5.4
2018-03-09	43.6	rain	0	5.5	5.8
2019-04-06	47.4	fog	0	5.5	7.6
2018-02-28	24.6	snow	0	5.5	8.9
2018-03-10	51.5	rain	0.25	5.5	8.1
2018-05-25	59.5	rain	0.09	5.5	3.3
2019-06-25	67.9	rain	0.11	5.5	4.1
2019-06-13	54.8	rain	0.57	5.5	10.5
2018-04-02	46.9	rain	0.46	5.5	9.3
2018-12-25	41.5	sunny	0.01	5.5	3.4
2017-04-04	53.1	rain	0.01	5.5	4.8
2018-07-29	64.6	rain	0.01	5.5	13
2019-02-08	48.6	rain	0.01	5.5	15.5
2019-01-23	34.8	fog	0.16	5.5	3.9
2018-05-27	69.3	rain	0.43	5.5	5.5
2018-04-22	61.7	rain	0.43	5.5	9.1
2019-12-20	46.8	rain	0.43	5.5	8.2
2016-06-09	62.7	sunny	0.05	5.5	4.9
2016-04-15	50	rain	0.1	5.5	4.2
2018-09-12	56.4	rain	0.13	5.5	3.8
2017-12-07	48	rain	0.12	5.5	12.7
2019-11-16	43.1	rain	0.07	5.5	4
2017-09-19	53.7	rain	0.14	5.5	4.3
2016-11-03	43.5	rain	null	6	5
2016-04-02	50.3	rain	null	6	7.6
2016-05-17	55.3	rain	null	6	7.7
2019-02-26	50.7	sunny	0	6	3
2018-11-26	43.1	rain	0	6	4.8
2016-12-01	35	sunny	0	6	4.1
2019-11-18	43.6	rain	0	6	4.6
2019-04-23	60.4	sunny	0	6	4.8
2019-08-25	76.3	fog	0	6	5.1
2017-03-10	49.1	rain	0	6	6.1
2018-06-02	65.4	sunny	0	6	7.8
2019-01-29	35.4	rain	0	6	5
2018-10-25	49.6	sunny	0	6	5.4
2018-11-14	52.1	rain	0	6	7.2
2018-02-22	37.7	rain	0	6	7.5
2017-04-03	51.5	fog	0	6	7
2018-05-09	57.1	sunny	0	6	7.2
2018-03-21	41.5	sunny	0	6	7.3
2016-04-01	45.9	sunny	0	6	7.7
2016-04-21	51.1	sunny	0	6	9.5
2019-01-16	45.6	rain	0	6	9.8
2018-09-11	66.6	rain	0	6	11.6
2018-11-09	50.9	rain	0	6	11.3
2016-09-05	64.6	rain	0.09	6	6
2017-10-13	61.4	sunny	0.09	6	10.5
2018-04-03	51.8	rain	0.18	6	10.3
2019-06-08	58.4	rain	0.33	6	15.3

SELECT * FROM (
SELECT
  'TEMP' COL,
  MIN(TEMP) MIN,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY TEMP) "25th PERCENTILE",
  MEDIAN(TEMP) MEDIAN,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY TEMP) "75th PERCENTILE",
  MAX(TEMP) MAX,
  AVG(TEMP) AVG,
  MODE(TEMP) MODE,
  STDDEV(TEMP) STD_DEV
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL)
UNION ALL (
  SELECT
  'PRECIPITATION' COL,
  MIN(PRECIPITATION) MIN,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY PRECIPITATION) "25th PERCENTILE",
  MEDIAN(PRECIPITATION) MEDIAN,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY PRECIPITATION) "75th PERCENTILE",
  MAX(PRECIPITATION) MAX,
  AVG(PRECIPITATION) AVG,
  MODE(PRECIPITATION) MODE,
  STDDEV(PRECIPITATION) STD_DEV
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL
)
UNION ALL (
  SELECT
  'VISIBILITY' COL,
  MIN(VISIBILITY) MIN,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY VISIBILITY) "25th PERCENTILE",
  MEDIAN(VISIBILITY) MEDIAN,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY VISIBILITY) "75th PERCENTILE",
  MAX(VISIBILITY) MAX,
  AVG(VISIBILITY) AVG,
  MODE(VISIBILITY) MODE,
  STDDEV(VISIBILITY) STD_DEV
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL
)
UNION ALL (
  SELECT
  'WINDSPEED' COL,
  MIN(WINDSPEED) MIN,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY WINDSPEED) "25th PERCENTILE",
  MEDIAN(WINDSPEED) MEDIAN,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY WINDSPEED) "75th PERCENTILE",
  MAX(WINDSPEED) MAX,
  AVG(WINDSPEED) AVG,
  MODE(WINDSPEED) MODE,
  STDDEV(WINDSPEED) STD_DEV
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL
)

COL	MIN	25th PERCENTILE	MEDIAN	75th PERCENTILE	MAX	AVG	MODE	STD_DEV
TEMP	24.6	45.675	52.75	61.8	83.4	53.512741047	57.7	10.211334572
PRECIPITATION	0	0	0	0.07	1.44	0.06960468521	0	0.1466155086
VISIBILITY	0.2	5.8	6.4	6.7	14.8	6.105027548	6.7	1.080844202
WINDSPEED	1.8	5.2	7.2	9.5	22.1	7.690013774	4.8	3.228622125

SELECT
  'DATE' COL,
  MIN(DATE) MIN,
  MAX(DATE) MAX,
  MODE(DATE) MODE,
  COUNT(DISTINCT DATE) UNIQUE_VALUES
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL

COL	MIN	MAX	MODE	UNIQUE_VALUES
DATE	2016-01-01	2019-12-31	2018-04-11	1452

SELECT
  'WEATHER' COL,
  MIN(WEATHER) MIN,
  MAX(WEATHER) MAX,
  MODE(WEATHER) MODE,
  COUNT(DISTINCT WEATHER) UNIQUE_VALUES
FROM PUBLIC.LONDON_WEATHER
GROUP BY COL

COL	MIN	MAX	MODE	UNIQUE_VALUES
WEATHER	fog	thunderstorm	rain	5

Correlation Matrix

Correlation matrices let you quickly see the correlations between all the different variables.

To create them, we will make use of the CORR

SELECT * FROM 
(SELECT 'TEMP' COL_1, 'PRECIPITATION' COL_2, CORR(TEMP,PRECIPITATION) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL 
(SELECT 'TEMP' COL_1, 'VISIBILITY' COL_2, CORR(TEMP,VISIBILITY) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL
(SELECT 'TEMP' COL_1, 'WINDSPEED' COL_2, CORR(TEMP,WINDSPEED) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL
(SELECT 'PRECIPITATION' COL_1, 'VISIBILITY' COL_2, CORR(PRECIPITATION,VISIBILITY) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL
(SELECT 'PRECIPITATION' COL_1, 'WINDSPEED' COL_2, CORR(PRECIPITATION,WINDSPEED) CORR
FROM PUBLIC.LONDON_WEATHER)
UNION ALL
(SELECT 'VISIBILITY' COL_1, 'WINDSPEED' COL_2, CORR(VISIBILITY,WINDSPEED) CORR
FROM PUBLIC.LONDON_WEATHER)
UNION ALL
(SELECT 'PRECIPITATION' COL_1, 'TEMP' COL_2, CORR(PRECIPITATION,TEMP) CORR
FROM PUBLIC.LONDON_WEATHER)
UNION ALL
(SELECT 'VISIBILITY' COL_1, 'PRECIPITATION' COL_2, CORR(VISIBILITY,PRECIPITATION) CORR
FROM PUBLIC.LONDON_WEATHER)
UNION ALL
(SELECT 'WINDSPEED' COL_1, 'TEMP' COL_2, CORR(WINDSPEED,TEMP) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL
(SELECT 'VISIBILITY' COL_1, 'TEMP' COL_2, CORR(VISIBILITY,TEMP) CORR
FROM PUBLIC.LONDON_WEATHER)
UNION ALL
(SELECT 'WINDSPEED' COL_1, 'PRECIPITATION' COL_2, CORR(WINDSPEED,PRECIPITATION) CORR
FROM PUBLIC.LONDON_WEATHER) UNION ALL
(SELECT 'WINDSPEED' COL_1, 'VISIBILITY' COL_2, CORR(WINDSPEED,VISIBILITY) CORR
FROM PUBLIC.LONDON_WEATHER)

COL_1	COL_2	CORR
TEMP	PRECIPITATION	-0.05288072854
TEMP	VISIBILITY	0.3310294722
TEMP	WINDSPEED	0.03470707836
PRECIPITATION	VISIBILITY	-0.08879966527
PRECIPITATION	WINDSPEED	0.131898554
VISIBILITY	WINDSPEED	0.2166210513
PRECIPITATION	TEMP	-0.05288072854
VISIBILITY	PRECIPITATION	-0.08879966527
WINDSPEED	TEMP	0.03470707836
VISIBILITY	TEMP	0.3310294722
WINDSPEED	PRECIPITATION	0.131898554
WINDSPEED	VISIBILITY	0.2166210513

We can see the highest correlations are between temperature and visibility

Identifying Outliers

Identifying outliers, or points that lie at the most extreme ends of your dataset, is a crucial to the rest of your analysis. Early identification, and potential removal of outliers makes for more accurate predictions and a better understanding of your data.

There are many ways to define outliers, but for this example, we'll use Tukey's fences:

A point is an outlier if it lies outside the range [Q1 - k(Q3-Q1) , Q3 + k(Q3-Q1)] where k is either 1.5 or 3 for identifying particularly 'far out' data.

Step 1: Identfy our Quartiles

We can use PERCENTILE_CONT to find our 25th and 75th percentiles:

SELECT PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY WINDSPEED) "Q1",
PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY WINDSPEED) "Q3"
from PUBLIC.LONDON_WEATHER

Q1	Q3
5.2	9.5

This means we can define an outlier that is outside of the following upper and lower bounds:

SELECT (Q1-1.5*(Q3-Q1)) LOWER_BOUND , (Q3+1.5*(Q3-Q1)) UPPER_BOUND FROM IQR

LOWER_BOUND	UPPER_BOUND
-1.25	15.95

Step 2: Identify points outside our range:

Select WINDSPEED from PUBLIC.LONDON_WEATHER cross join IQR where WINDSPEED not between (Q1-1.5*(Q3-Q1)) AND (Q3+1.5*(Q3-Q1))

WINDSPEED
17.6
16.2
16
16.1
17.3
18
17
18.4
18
16.1
20.2
17.8
19.5
18.5
19.5
22.1
16.3
20.3
16.8
21.6
19.2
17.7
19.7
17.7
16
18.3
17.6

How We Built This

This page was built using Count. It combines the best features of a SQL IDE, Data Visualization Tool, and Computational Notebooks. In the Count notebook, each cell acts like a CTE, meaning you can reference any other cell in your queries.

This makes not only for far more readable reports (like this one) but also a much faster and more powerful way to do your analysis, essentially turning your analysis into a connected graph of data frames rather than one-off convoluted queries and CSV files. And with a built-in visualization framework, you won't have to export your data to make your charts. Go from raw data to interactive report in one document.

Business Improvement

Product

Use cases

Pricing

Professional Services

Customers