Geographic Analysis in SQL: Measuring Polygon Area from Latitude and Longitude

Computing the area of polygons on the Earth's surface can be difficult, especially when the polygons are complex and have a large number of vertices. Here is a pure SQL solution to help you get started with your geographic analysis.

Read More
SQL History and Why Your Database Isn't Really Relational

Rather than a true relational database, SQL is a compromise between convenience and the rigour of relational algebra. Here's how it's different.

Read More
SQL Query Order of Execution

The SQL order of execution defines the order in which the clauses of a query are evaluated. Let's look at how your query SQL executes, and how you can use that knowledge to write faster SQL

Read More
How ZeroCater Made Our Lunch Data-Driven: Survey Analysis and the IoT

Here at Periscope we absolutely love data, so I couldn’t help but notice when our catering company, ZeroCater, brought in an amazing device to collect survey data about our lunches in real time. ZeroCater has always delivered us a great meal, but for a data driven analyst like myself this this was too interesting to ignore...

Read More
Precision Recall and ROC Curves for Pregnancy Tests

As a data nerd who hates taking chances, I thought I’d perform an example analysis on pregnancy tests to figure out whether or not the current standard of measuring the hormone hCG (human chorionic gonadotropin) is the most optimal method. After all, no matter if you are a woman who wants to be pregnant or a woman who doesn’t, having an incorrect prediction is not something you want to risk. With Periscope Data, use SQL to map out Precision-Recall and Receiver Operating Characteristic (ROC) curves.

Read More
What You Need To Know About SQL's GROUP BY

The SQL 'group by' is one of the most common keywords. Here is how it works, and a few special cases you want to be aware of

Read More
SQL Symbol Cheat Sheet

When you are new to programming in SQL, you will come across a lot of hard-to-search-for character operators. If you are prone to forgetting ~ is called tilde, are wondering why there are so many %s in your strings, or have a hard time googling what the (+) symbol in where users.group_id(+) = group.id, this guide is for you.

Read More
Safety in San Francisco: A Numbers Game

When I first moved to San Francisco, my mother warned me “not to go looking for trouble”, but this can be difficult when you do not know the city well.

Read More
Creating Ranked Position Time Series with SQL

The Los Angeles Lakers were the top NBA team in 2008 but the last in 2016. We wanted to compare their downfall to the rise of other teams in the league, so we built a comparison of each team’s rank in the standings over the past ten years.

Read More
Building a Better Pokédex with SQL

Pokémon Go has taken over the world. In the moments I’m not exploring the city trying to catch ‘em all, I’ve found myself idly wondering about Pokémon Theory.

Read More
Cohorted Engagement with Comparable Time Windows in SQL

Cohort analysis is a powerful tool for evaluating the behavior of groups of users over time. One of the most common ways to cohort users is by when they started using your product or playing your game.

Read More
Getting Started with Regular Expressions

Last time we talked about matching strings in SQL, we covered tactics that work well for strings on the simple side. For strings with more complicated patterns, the regular expressions below are a handy tool to have in your belt.

Read More
Using CTEs and Unions to Compute Running Totals

A common pattern in SQL analysis is to use multiple groupings to show grouped totals over time.

Read More
How to Really Use SQL Views

Views are virtual tables that can be a great way to optimize your database experience.

Read More
SQL Joins For Data Analysis

It’s rare for one table to have all the information needed for a query. Across all our customers, the average chart joins 4 tables to get its result!

Read More
Understanding Website Response Time with SQL

At Periscope we are constantly tracking performance statistics for our computing infrastructure, database query times, and, of course, web servers.

Read More
The Top SQL Analysis Errors We See

‍When writing SQL for data analysis there are two kinds of errors: errors that prevent the query from running, and errors that merrily return the wrong data. The second kind of error can be hard to spot, especially when your results are within range of your expectations.

Read More
SQL for Marketers — Your Most Profitable Channels

Knowing how your customer acquisition costs compare to your customer lifetime value is a vital part of marketing. When split across channels, this data allows you to decide whether to double down on a channel or take resources away.

Read More
Understanding SQL's Null

Tony Hoare invented the null reference in 1965, and he considers it his “billion-dollar mistake” for the amount of trouble it has caused. Even today, SQL’s nullvalue is the cause of several common mistakes.

Read More
SQL for Marketers — 3 Ways to Slice Your Acquisition Data

Acquisition metrics are a well covered blog topic. It’s easy to figure out the general metrics you should follow. The only issue is that the authors assume you’re using an analytics tool like Google Analytics or Mixpanel.

Read More
Counting Comma-Delimited Values in Postgres, MySQL, Amazon Redshift and MS SQL Server.

Every once in a great while, the enterprising SQL analyst is confronted with data that is not relational in nature.

Read More
SQL for Marketers — Making Where More Powerful

Data analysis is an important part of a marketer’s work. One of the most common data analyses that marketers conduct is filtering data.

Read More
Rolling Averages in MySQL and SQL Server

Previously we discussed how to write rolling averages in Postgres. By popular demand we’re showing you how to do the same in MySQL and SQL Server.

Read More
SQL for Marketers Part 4 — Common Analysis Queries

Thanks for tuning in to another SQL for Marketers post! For round four, we’re going to cover a few core SQL queries that can answer many common marketing questions.

Read More
Medians in SQL

Finding the min, max, and avg price of an order in your purchases table is easy. But what about the median price?

Read More
SQL for Marketers Part 3 — Our Most Valuable Traffic Source

Another foggy day in San Francisco, another SQL for Marketers post. For round three, we’re going to determine which traffic source has driven the most revenue for us.

Read More
Day-Over-Day Changes Without Window Functions

A couple of months ago, we showed you how to compute day-over-day changes using window functions. For high-growth companies, the growth rate is at least as important as the absolute value of any metric, so this technique is very handy.

Read More
SQL for Marketers Part 2 — Users over the Last 30 Days

Welcome back to SQL for Marketers. Last time we covered basic SQL terms and the SQL commands select, from, where and join.

Read More
SQL for Marketers

In a break from our usual posts about advanced SQL techniques, we’re bringing it back to the very beginning.

Read More
Selecting Only One Row Per Group

Sometimes you just want to graph the winners. Were there more iOS or Android users today? Grouping and counting the daily usage per platform is easy, but getting only the top platform for each day can be tough.

Read More
Computing Day-Over-Day Changes With Window Functions

In most sophisticated analysis, the rate of change is at least as important as the raw values. This makes life tough for a SQL analyst, where adding daily deltas to your result set can be difficult.

Read More
Date Parts in SQL Server

The bread and butter of SQL analysis is grouping by a period of time and then counting the rows for each period.

Read More
How to Format Numbers as Currency in Postgres, MySQL and Redshift

In your venerable orders table, you’re almost certainly storing prices as numbers. Perhaps they’re integer, perhaps they’re numeric, perhaps you’re using Postgres and they’re money, or perhaps you rolled the dice on floating-point rounding errors and went with real.

Read More
Generate Series in Redshift and MySQL

‍A lot of the charts and tables made in Periscope are time series, and the queries behind them are often easier when you can join and aggregate against a list of dates.

Read More
Counting Conditionally in SQL

When preparing a report on the number of customers you have, it can be helpful to split out the premium customers — i.e., those who spend more than $100/month — from the rest of the group.

Read More
4 Ways to Join Only The First Row in SQL

For today’s daily report, we need a list of users and the most recent widget each user has created.

Read More
Range joins give you accurate histories

One fine morning, your investor Leo asks you: How many charts are there across all of Periscope? Because he’s an investor, he also wants to know: How fast is that number growing?

Read More