Please enable JavaScript to continue!

SQL Code Examples

Helpful SQL Snippets

Normalizing Parameters with SQL
If you have multiple metrics that you want to weigh together, it may make sense to normalize the data so one metric (that is measured on a larger scale),…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 2 days ago
- 8
- SQL Code Examples
Generate Series of Dates in Snowflake
As Snowflake doesn't have a native generate_series function, here is our solution to generating a table of incrementing dates, starting from the current date, in Snowflake.…
- Ryan Waters
- Ryan_Waters
- 3 wk ago
- 2 replies
- 78
- 5
- Neha Kumar7 days ago
- SQL Code Examples
Exact Row Counts for All Tables in MySQL and load them into table
Hi, I've read article https://www.periscopedata.com/blog/exact-row-counts-for-every-database-table and it works for me, but I need to edit scrit to export result into table.…
- Jiri Hanzlicek
- Jiri_Hanzlicek
- 1 mth ago
- 1 reply
- 38
- Neha Kumar8 days ago
- SQL Code Examples
Extract a phone number from a string in Redshift
Sometimes, phone numbers don't come in a format that easy for extraction. It requires a little bit of magic with Regex expressions in Redshift to get what you're looking for Let's say I have this…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 2 wk ago
- 28
- 3
- SQL Code Examples
Table of Contents
We've found here at Periscope that it's often very helpful to have the default dashboard on a site include a table of contents for even quicker navigation to key dashboards,…
- Charlotte Campbellmoderator
- Customer Solutions Engineer
- Charlotte_Campbell
- 7 mths ago
- 1 reply
- 757
- 4
- Ethan Abernathey4 wk ago
- SQL Code Examples
Chart Type - Bullet Charts
I love a great benchmark or comparison to goals. One solution that Kyle on my team came up with was a bullet chart. I standardized this with a Parameterized SQL Snippet to do all the transformation…
- Britton Stampermoderator
- Periscope Solutions Engineer!
- Britton_Stamper
- 1 yr ago
- 3 replies
- 754
- 2
- Neha Kumar1 mth ago
- SQL Code Examples
Calculating Trimmed Means (SQL and Python Variations)
Data can oftentimes have extreme outliers, which can heavily skew certain metrics, such as the mean. One way to get around this is to using a trimmed mean.…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 2 mths ago
- 79
- SQL Code Examples
Generate Series of Timestamps (1 second intervals)
There may be several reasons why you want to create a list of dates or timestamps. The primary use case is to left join a dataset that has missing dates onto this full list of dates so you can…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 2 mths ago
- 97
- SQL Code Examples
Rolling Averages using Window Functions
Want to show a rolling average for your data, but not the granular breakdown? We can use the average window function to run this calculation. First, I created a CTE that contains the number of new…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 3 mths ago
- 204
- SQL Code Examples
Adding Pagination to Tables with Filters
Sometimes it's important to be able to access large amounts of data directly in a table. For chunks of data that can't be safely loaded in a browser, one option would be to split the results in…
- Charlotte Campbellmoderator
- Customer Solutions Engineer
- Charlotte_Campbell
- 6 mths ago
- 3 replies
- 364
- 4
- Amy Budd3 mths ago
- SQL Code Examples
Chart Type - Period over Period Change
An important analytical technique is to look at the rate of change over time. An easy graph to investigate this is a bar chart with the period over period change in percent or value.…
- Britton Stampermoderator
- Periscope Solutions Engineer!
- Britton_Stamper
- 3 mths ago
- 546
- 1
- SQL Code Examples
Average Time Interval between a Start and End Time
Looking to get the average time interval between a start and an end time (ex: Given the start and end times for runners in a race, find the average time it takes for a runner to complete the race)?…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 3 mths ago
- 130
- SQL Code Examples
Prompt Users to Enter a Filter Value
Oftentimes we want our end users to select a filter in order to use a dashboard. One of the ways we can do this is by building it into then chart SQL, as shown below:…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 3 mths ago
- 660
- 2
- SQL Code Examples
Additional Date Aggregations: Aggregating by 5 Minute Intervals
I worked with a customer recently who wanted to aggregate their data in 5 minute intervals. We had a clean way to implement this: from_unixtime(300*floor(unixtime/300)) How this works:…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 10 mths ago
- 2 replies
- 652
- Serra Kazanc4 mths ago
- SQL Code Examples
MySQL’s sec_to_time() function in Redshift
In MySQL, it’s easy to convert an integer representing a number of seconds into a string representing a human-readable amount of time. This is especially useful when you want to compare two…
- Ray Harrismoderator
- Ray_Harris
- 4 mths ago
- 971
- 3
- SQL Code Examples
Daterange handling partial periods
As a best practice, you don't want to show "partial" historical periods, and you'd like an easy way to separate out currently "not fully baked" data from historical data.…
- Bryce E
- Bryce_E
- 5 mths ago
- 975
- 3
- SQL Code Examples
Calculating Number of Weekdays between 2 Dates (Redshift and Postgres)
The parameterized snippet below can be used to find the number of weekdays between 2 dates. We named this one difference_in_weekdays(start_date,end_date) Redshift (DATEDIFF('day', [start_date],…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 7 mths ago
- 1 reply
- 2153
- 1
- Ray Harrismoderator5 mths ago
- SQL Code Examples
Walkthrough: Unaggregate/Disaggregate Data
Let’s say you have some aggregated data and want to break it out into multiple rows. Maybe the original data source provided the values in this format,…
- Ray Harrismoderator
- Ray_Harris
- 5 mths ago
- 211
- 4
- SQL Code Examples
Syntax error: Unexpected keyword INTERVAL
It's been a long time since I've done any SQL... I'm following the guide on retention analysis from https://www.periscopedata.com/blog/how-to-calculate-cohort-retention-in-sql and falling at the…
- Mike Bartlett
- Mike_Bartlett
- 5 mths ago
- 3 replies
- 456
- Mike Bartlett5 mths ago
- SQL Code Examples
Fill Missing Dates by Generating Series of Timestamps
I was struggling with how to generate missing timesteps so that data is sorted correctly by date when there are 2 series (I was attempting to overlay week-long periods from two different months).…
- David Krysl
- Data Science Analyst @ Locbit
- David_Krysl
- 5 mths ago
- 153
- 3
- SQL Code Examples
Visual Analysis - Comparing KPI to a Benchmark
What methods do you use to effectively communicate "Here is the KPI versus a Benchmark"? One of my favorite is to use the chart series type to indicate the difference between a realized KPI and the…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 6 mths ago
- 3 replies
- 872
- 2
- Sean Cook1 yr ago
- SQL Code Examples
SQL Alert - Tell me if a value dropped since yesterday
SQL Alerts are very handy to keep track of any critical metrics. Oftentimes it makes most sense to do a relative comparison of your data. For instance,…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 6 mths ago
- 226
- 2
- SQL Code Examples
The Ultimate Custom Date Aggregation Filter
Aggregating data by various periods of time is made easy with Periscope's built-in date aggregation filter . Sometimes, however, you may wish to group your data by different periods of time.…
- Ray Harrismoderator
- Ray_Harris
- 6 mths ago
- 854
- 4
- SQL Code Examples
Is there a followup to the "Simple Event Study"?
This post was really good, and suggested a part 2 was shortly coming. But it's been two years and judging by the collection, nothing jumps out. Am I just missing it?
- Scott B
- Scott_B
- 6 mths ago
- 1 reply
- 168
- Neha Kumar6 mths ago
- SQL Code Examples
Filter out selected filter values
Occasionally, we would want to have a chart show everything except the values selected in a filter. For this use case, we recommend using the following SQL in your chart query.…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 6 mths ago
- 711
- 1
- SQL Code Examples

1 2 3 Next

Helpful SQL Snippets

Top contributors

View all members

Community

SQL Code Examples

Normalizing Parameters with SQL

Generate Series of Dates in Snowflake

Exact Row Counts for All Tables in MySQL and load them into table

Extract a phone number from a string in Redshift

Table of Contents

Chart Type - Bullet Charts

Calculating Trimmed Means (SQL and Python Variations)

Generate Series of Timestamps (1 second intervals)

Rolling Averages using Window Functions

Adding Pagination to Tables with Filters

Chart Type - Period over Period Change

Average Time Interval between a Start and End Time

Prompt Users to Enter a Filter Value

Additional Date Aggregations: Aggregating by 5 Minute Intervals

MySQL’s sec_to_time() function in Redshift

Daterange handling partial periods

Calculating Number of Weekdays between 2 Dates (Redshift and Postgres)

Walkthrough: Unaggregate/Disaggregate Data

Syntax error: Unexpected keyword INTERVAL

Fill Missing Dates by Generating Series of Timestamps

Visual Analysis - Comparing KPI to a Benchmark

SQL Alert - Tell me if a value dropped since yesterday

The Ultimate Custom Date Aggregation Filter

Is there a followup to the "Simple Event Study"?

Filter out selected filter values

Categories

Top contributors

Recent activity

Home