Please enable JavaScript to continue!

SQL Code Examples

Helpful SQL Snippets

Follow

Determine the Fraction of Rows that Meet a Condition (Ex: Calculating Accuracy on a Test Dataset in SQL)
Let's say you have a table that contains a row of expected values and a row of predicted values, and we want to determine the accuracy. What we need to do is find cases where the expected values are…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 1 mth ago
- 19
- SQL Code Examples
Resetting Cumulative Sums By Condition
Suppose you have a table period_end that indicates if a user is paying at the end of each period, like so: period paying 1 1 2 1 3 1 4 0 5 1 You want to calculate the number of consecutive periods…
- Mitchell Posluns
- Data Scientist
- Mitchell_Posluns
- 1 yr ago
- 2 replies
- 822
- 3
- Tracy Carroll-Grygiel1 mth 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
- 1 mth ago
- 232
- 4
- SQL Code Examples
A more robust LIKE/SIMILAR TO/wildcard filter at the dashboard level with a Matching Expression filter!
I previously wrote a post on how to create a LIKE wildcard filter by concatenating the wildcard operator (%) onto a filter value. One of my engineers suggested a better method - using a Matching…
- Alok Subbaraoemployee
- Professional Services Engineer
- Alok_Subbarao
- 1 mth ago
- 2 replies
- 680
- 2
- Karen Goeller10 mths ago
- SQL Code Examples
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 mths ago
- 35
- 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
- 2 mths ago
- 2 replies
- 121
- 6
- Neha Kumar2 mths 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
- 3 mths ago
- 1 reply
- 48
- Neha Kumar2 mths 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 mths ago
- 51
- 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
- 9 mths ago
- 1 reply
- 806
- 4
- Ethan Abernathey2 mths 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
- 783
- 2
- Neha Kumar3 mths 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
- 4 mths ago
- 84
- 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
- 4 mths ago
- 109
- 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
- 5 mths ago
- 235
- 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
- 8 mths ago
- 3 replies
- 392
- 4
- Amy Budd5 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
- 5 mths ago
- 567
- 2
- 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
- 5 mths ago
- 142
- 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
- 5 mths ago
- 724
- 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
- 1 yr ago
- 2 replies
- 656
- Serra Kazanc6 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
- 6 mths ago
- 983
- 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
- 7 mths ago
- 979
- 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
- 9 mths ago
- 1 reply
- 2,237
- 1
- Ray Harrismoderator7 mths ago
- 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
- 7 mths ago
- 3 replies
- 471
- Mike Bartlett7 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
- 7 mths ago
- 162
- 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
- 8 mths ago
- 3 replies
- 924
- 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
- 8 mths ago
- 247
- 2
- SQL Code Examples

1 2 3 Next

Back to top

Helpful SQL Snippets

Categories

Top contributors

View all members

Recent activity