How to enable Javascript

SQL Code Examples

Helpful SQL Snippets

Follow

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
- 1 yr ago
- 1 reply
- 1,383
- 4
- Adam Lubamoderator1 mth ago
- SQL Code Examples
Up Arrow or Down Arrow for KPIs
Use this case statement to add up and down arrows to your metrics, for instance: case when [value_field] < lag([value_field]) over(order by [order_field]) then '▲ ' when [value_field] =…
- Sean Cook
- Product Analyst @ Facebook
- Sean_Cook
- 2 yrs ago
- 2 replies
- 618
- 6
- Ankush R2 mths ago
- 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
- 8 mths ago
- 3 replies
- 439
- 8
- Chade Li3 mths ago
- SQL Code Examples
Fixing DISTINCT ON not supported
Ever seen the following error when materializing a view? SELECT DISTINCT ON is not supported The DISTINCT ON clause is a Postgres function which selects the first value for a group given the order…
- Ray Harrismoderator
- Ray
- 3 mths ago
- 31
- SQL Code Examples
Adding a Total Row or Column to your Cohort
In many cases, a cohort grid can benefit from showing the rolled-up row or column totals. This can be accomplished within the query by UNION'ing the appropriately grouped columns.…
- Andreas Djokicmoderator
- Senior Solutions Engineer
- Andreas_Djokic
- 1 yr ago
- 2 replies
- 3,722
- 11
- Taisa Grant3 mths ago
- SQL Code Examples
Custom Date Range Filter
Update: Periscope Data now allows you the ability to create additional date range filters without having to go through the below workaround. Refer to our docs here under "Additional Date Range…
- Neha Kumar
- Solutions Engineer
- Neha_Kumar
- 4 mths ago
- 9 replies
- 4,773
- 6
- James Corbishley4 mths ago
- SQL Code Examples
Create custom filter for start of the week.
Periscope has the default aggregation which takes week start from Monday. Some times we need a week starting from Wednesday or some other days. code:- case when [value] = 'week_monday' then…
- Chinki
- Data Data Data.........
- crai
- 3 mths ago
- 38
- SQL Code Examples
Using a case/when clause with default filter values
Sometimes when using direct replacement filters , you may want to filter or manipulate your data differently depending on the value or values supplied by the filter.…
- Ray Harrismoderator
- Ray
- 5 mths ago
- 117
- SQL Code Examples
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
- 7 mths ago
- 48
- 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
- 944
- 5
- Tracy Carroll-Grygiel7 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
- 7 mths ago
- 284
- 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
- 7 mths ago
- 2 replies
- 793
- 2
- Karen Goeller1 yr 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
- 8 mths ago
- 83
- 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
- 9 mths ago
- 1 reply
- 58
- Neha Kumar8 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
- 8 mths ago
- 121
- 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
- 1 yr ago
- 1 reply
- 930
- 4
- Ethan Abernathey9 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
- 825
- 2
- Neha Kumar9 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
- 10 mths ago
- 102
- 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
- 10 mths ago
- 140
- 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
- 11 mths ago
- 338
- 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
- 1 yr ago
- 3 replies
- 449
- 4
- Amy Budd11 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
- 11 mths ago
- 642
- 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
- 11 mths ago
- 172
- 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
- 1 yr ago
- 906
- 3
- 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
- 681
- Serra Kazanc1 yr ago
- SQL Code Examples

1 2 3 4 Next

Back to top

Helpful SQL Snippets

Categories

Top contributors

View all members

Recent activity