Periscope
Please enable JavaScript to continue!
Sign Up
Login
Community
Home
Ask, Share and Learn
Add new
SQL Code Examples
Helpful SQL Snippets
Filter
Newest
Active
Popular
Most likes
Most replies
Most views
Tags
Clear filter
Follow
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 Kumar
7 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 Kumar
8 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 Campbell
moderator
Customer Solutions Engineer
Charlotte_Campbell
7 mths ago
1
reply
757
4
Ethan Abernathey
4 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 Stamper
moderator
Periscope Solutions Engineer!
Britton_Stamper
1 yr ago
3
replies
754
2
Neha Kumar
1 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 Campbell
moderator
Customer Solutions Engineer
Charlotte_Campbell
6 mths ago
3
replies
364
4
Amy Budd
3 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 Stamper
moderator
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 Kazanc
4 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 Harris
moderator
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 Harris
moderator
5 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 Harris
moderator
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 Bartlett
5 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 Cook
1 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 Harris
moderator
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 Kumar
6 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
Add SQL Snippet
Helpful SQL Snippets
Create a Post
Categories
Announcements and Events
Announcements
Events
Ask, Share and Learn
Ask a Question!
R and Python Code Examples
SQL Code Examples
Datasets
Learn SQL!
Competition Submissions
SQL Interview Questions
Questions
Answer Keys
Discussion Forums
For Data Managers
Product Talk
Data Engineering
Analytics Discussions
Off Topic
Top contributors
View all members
Recent activity
Home
Announcements and Events
Announcements
Events
Ask, Share and Learn
Ask a Question!
R and Python Code Examples
SQL Code Examples
Datasets
Learn SQL!
Competition Submissions
SQL Interview Questions
Questions
Answer Keys
Discussion Forums
For Data Managers
Product Talk
Data Engineering
Analytics Discussions
Off Topic