SQL Resources
Practical guides to SQL, BigQuery, Snowflake, and SQL Server - functions, joins, window functions, dates, and more.
SQL
- COUNTHow the COUNT function works in SQL
- DatesHow to create, manipulate, convert and analyse dates using SQL. This article covers the use of the CAST, PARSE_DATE, FORMAT_DATE, DATE_ADD and EXTRACT functions.
- FROMThe FROM clause is one of the fundamental building blocks of a SQL SELECT statement. In this article we'll explain how it works.
- JOINThe optional JOIN clause can be placed in the FROM part of a SQL SELECT statement. In this article we'll explain how it works.
- StringsHow to create, manipulate, convert and analyse strings using SQL. This article covers the use of the TRIM, LPAD, UPPER, REVERSE, CONCAT, LENGTH, STARTS_WITH and SUBSTR functions.
- WHEREThe WHERE clause is one of the fundamental building blocks of a SQL SELECT statement. In this article we'll explain how it works.
BigQuery Standard SQL
- ARRAY_AGGARRAY_AGG function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The ARRAY_AGG function creates an ARRAY from another expression or table.
- Arrays Explained
- Arrays ExplainedEverything you need to know about SQL Array functions. Arrays are ordered lists in BigQuery. They are very powerful and flexible once you know how to use them.
- CASECASE function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The CASE function allows you to perform conditional statements in SQL.
- CASTCAST function. Definition, syntax, examples and common errors using BigQuery Standard SQL. CAST allows you to convert to different Data Types in BigQuery.
- COALESCECOALESCE function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The COALESCE function will return the first non-NULL expression.
- CONCATCONCAT function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The CONCAT function allows you to combine (concatenate) one more values into a single result.
- COUNT [DISTINCT]COUNT DISTINCT function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The COUNT function returns the number of rows in a SQL expression.
- CURRENT_DATECURRENT_DATE function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The CURRENT_DATE function returns the date at the time the query is evaluated.
- CURRENT_DATETIMECURRENT_DATETIME function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The CURRENT_DATETIME function returns the date and time at the moment the query is evaluated.
- CURRENT_TIMESTAMPCURRENT_TIMESTAMP function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The CURRENT_TIMESTAMP function returns the date and time at the moment the query is evaluated.
- DATETIME_DIFFDATETIME_DIFF function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The DATETIME_DIFF function allows you to find the difference between 2 datetime objects.
- DATETIME_TRUNCDATETIME_TRUNC function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The DATETIME_TRUNC function in BigQuery will truncate the datetime to the given date_part.
- DATE_DIFFDATE_DIFF function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The DATE_DIFF function allows you to find the difference between 2 date objects.
- DATE_TRUNCDATE_TRUNC function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The DATE_TRUNC function in BigQuery will truncate the date to the given date_part.
- Data TypesEverything you need to know about SQL Data Types in BigQuery. BigQuery supports several data types, some are quite standard, others are more complex.
- Dates and TimesEverything you need to know about SQL Dates and Times in BigQuery. BigQuery offers no shortage of functionality to help you get the most out of date and time data.
- EXTRACTEXTRACT function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The EXTRACT function returns the number of rows in a SQL expression.
- IFIF function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The IF function allows you to evaluate a boolean expression and return different results based on the outcome.
- MEDIANMEDIAN function. Definition, syntax, examples and common errors using BigQuery Standard SQL. There is no MEDIAN function in BigQuery, but it can be calculated using the PERCENTILE_CONT function.
- SUBSTRSUBSTR function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The substring allows you to extract a section of a longer string in BigQuery.
- String Functions ExplainedEverything you need to know about SQL string functions. Strings are a crucial part of any dataset and being able to confidently manipulate and transform them can make all the difference in your analysis.
- TIMESTAMP_DIFFTIMESTAMP_DIFF function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The TIMESTAMP_DIFF function allows you to find the difference between 2 timestamp objects.
- TIMESTAMP_TRUNCTIMESTAMP_TRUNC function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The TIMESTAMP_TRUNC function in BigQuery will truncate the timestamp to the given date_part.
- TIME_DIFFTIME_DIFF function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The TIME_DIFF function allows you to find the difference between 2 time objects.
- TIME_TRUNCTIME_TRUNC function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The TIME_TRUNC function in BigQuery will truncate the time to the given date_part.
- UNNESTUNNEST function. Definition, syntax, examples and common errors using BigQuery Standard SQL. The UNNEST function takes an ARRAY and returns a table with a row for each element in the ARRAY.
- Window Functions ExplainedEverything you need to know about SQL Array functions. Arrays are ordered lists in BigQuery. They are very powerful and flexible once you know how to use them.
Snowflake
- HistogramsHistograms are essential tools to understand how data is distributed. Mastering the art of building and customizing histograms in SQL can be tricky at first, but once you've got it down it's a recipe you'll be coming back to again and again.
- Linear RegressionLinear Regression is a powerful way to understand your data and make predictions. Doing this in SQL has always been difficult, but Snowflake has a few built-in functions that simplify the process.
- Moving averagesMoving averages in Snowflake are an excellent way to discover patterns in your data.
- Pivot TablesWe're all familiar with the power of Pivot tables, but building them in SQL tends to be tricky. This article walks through how to build them in Snowflake.
- Running TotalsRunning Totals or Cumulative Sums are a powerful way to see not just a trend of data, but also the cumulative results.
- Summary StatisticsWhen you get a new dataset, one of the first things you want to do is find your summary statistics. In this article we'll explain how it's done.
- Window FunctionsWindow functions in Snowflake are a method to compute values over a group of rows. In this article we'll explain how they work.
SQL Server
- Data TypesEverything you need to know about Data Types in SQL Server. SQL Server supports several data types, some are quite standard, others are more complex.
- Dates and TimesEverything you need to know about Dates and Times in SQL Server. SQL Server offers no shortage of functionality to help you get the most out of date and time data.