19 Jan 2022 Ignacio L. Bisso What Is a Common Table Expression (CTE) in SQL? The common table expression (CTE) is a powerful construct in SQL that helps simplify a query. CTEs work as virtual tables (with records and columns), created during the execution of a query, used by the query, and eliminated after query execution. CTEs often act as a bridge to transform the data in source tables to the format expected by the query. A common table expression, or CTE, is a temporary named result set created from a simple SELECT statement that can be used in a subsequent SELECT statement. Read more 23 Dec 2021 Ignacio L. Bisso How to Use the SQL PARTITION BY With OVER At the heart of every window function call is an OVER clause that defines how the windows of the records are built. Within the OVER clause, there may be an optional PARTITION BY subclause that defines the criteria for identifying which records to include in each window. Read on and take an important step in growing your SQL skills! What Is the PARTITION BY Clause in SQL? The SQL PARTITION BY expression is a subclause of the OVER clause, which is used in almost all invocations of window functions like AVG(), MAX(), and RANK(). Read more 29 Oct 2021 Ignacio L. Bisso What Is a Rolling Average and How Do You Compute It in SQL? In this article, we will show three different examples of using SQL to calculate rolling averages. Learn this and you will take an important step in your SQL growth! A rolling average is a metric that allows us to find trends that would otherwise be hard to detect. It is usually based on time series data. In SQL, we calculate rolling averages using window functions. First, let’s talk about what rolling averages are and why they’re useful. Read more 30 Sep 2021 Ignacio L. Bisso What Is the OVER() Clause in SQL? Window functions are one of SQL’s most powerful resources, but they are not frequently used by the average SQL developer. In this article, we will explain how you can define different kinds of window frames using the OVER clause. The OVER clause is essential to SQL window functions. Like aggregation functions, window functions perform calculations based on a set of records – e.g. finding the average salary across a group of employees. Read more 5 Aug 2021 Ignacio L. Bisso How to Define a Window Frame in SQL Window Functions The window function is a super powerful resource of the SQL language. At the core of any window function, there is a set of records, called the window frame, defined using an OVER clause. Knowing what records are in the window frame, how they are ordered, and what their upper and lower bounds are, are critical in understanding how window functions work. In this article, we will analyze and explain with examples how you can define different types of window frames. Read more 16 Jul 2021 Ignacio L. Bisso Top 10 SQL Window Functions Interview Questions Many interesting job positions require SQL skills – and that includes window functions, which are not commonly taught in online courses. In this article, I will cover the top window function questions for every experience level. If you’re going for a job interview for an advanced SQL position or for intermediate to advanced data analyst positions, you’ll probably be asked about your knowledge of SQL window functions. Don’t panic! Although these functions aren’t commonly covered in online courses, we’ve got the answers right here. Read more 26 May 2021 Ignacio L. Bisso How to Compute Year-Over-Year Differences in SQL How did your business do this year compared to last year? The year before? Find out how to use SQL to calculate year to year and month to month differences. Year-over-year (YOY) comparisons are a popular and effective way to evaluate the performance of several kinds of organizations. Year to year differences are easy to understand – e.g. it’s easy to understand that your revenue growth is 35% year to year. Read more 29 Apr 2021 Ignacio L. Bisso Why Do You Need a Primary Key in a Database Table? What is a primary key and why is it so important in SQL? Find out in this article. Primary keys are a central element in SQL databases. They provide a unique value that can identify a specific row in a table, which opens up a lot of other benefits. This article covers what a primary key is, how it works, and how to use it in an SQL database. Read more 24 Mar 2021 Ignacio L. Bisso What Is a NOT NULL Constraint in SQL? SQL has several ways of dealing with NULLs. In this article, we’ll focus on the NOT NULL constraint and other NULL-related clauses. Understand this and you’ll take an important step in your SQL growth! Before we get straight into the NOT NULL constraint, let’s quickly define what NULLs and constraints are. NULL values are a central concept in SQL databases. The idea behind a NULL value is simple: a NULL value means that we do not have a known value for that field. Read more 29 Jan 2021 Ignacio L. Bisso Data Types in SQL Read this article and learn the ABC of SQL data types. Data types are used to define the type of data stored in a database. However, there are related concepts that a good SQL developer should know: data type conversion, what operations are possible between different data types, date arithmetic, etc. SQL also provides functions that manipulate values of different data types (i.e. functions for string data, date and time data, etc. Read more «« « 1 2 3 4 » »»