A curated collection of helpful SQL queries and functions, maintained by Count.
All contributions are very welcome - let's get useful SQL snippets out of Slack channels and Notion pages, and collect them as a community resource.
| Snippet | Databases |
|---|---|
| Cohort analysis |  |
| Cumulative distribution functions |    |
| Compound growth rates | |
| Generate a binomial distribution | |
| Generate a uniform distribution | |
| Histogram bins |  |
| Median | |
| Median (UDF) | |
| Outlier detection: MAD method | |
| Outlier detection: Standard Deviation method | |
| Outlier detection: Z-score method | |
| Random Between | |
| Random sampling | |
| Ranking | |
| TF-IDF | |
| Arrays to columns |  |
| Filtering arrays | |
| Generating arrays | |
| Get last array element | |
| Reverse array | |
| Transforming arrays | |
| Least non-null value in array (UDF) | |
| Greatest/Least value in array | |
| Sort Array (UDF) | |
| Bar chart | |
| Horizontal bar chart | |
| Time series | |
| 100% stacked bar chart | |
| Heat map | |
| Stacked bar and line chart | |
| Check if column exists in table | |
| Search for text in Stored Procedures | |
| Date/Time Formatting One Pager | |
| Converting from strings | |
| Converting to strings | |
| Local Timezone to UTC | |
| Converting epoch/unix to timestamp | |
| Generate timeseries | |
| Last day of the month | |
| Last X days | |
| Calculating the distance between two points | |
| Parsing JSON strings | |
| Parsing URLs | |
| Validating emails | |
| Extract repeating words | |
| Split on Uppercase characters | |
| Replace multple conditions | |
| CONCAT with NULLs | |
| Capitalize initial letters (UDF) | |
| Concatenating strings | |
| Pivot | |
| Unpivoting / melting | |
| Replace NULLs | |
| Replace empty string with NULL | |
| Counting NULLs | |
| Splitting single columns into rows | |
| Latest Row (deduplicate) | |
| First row of each group | |
| Moving averages | |
| Running totals | |
| Year-on-year changes | |
| Creating equal-sized buckets | |
- bigquery-utils - a BigQuery-specific collection of scripts