Articles Cookbook
31 Aug 2017
Aldo Zelen

Common SQL Window Functions: Positional Functions

Positional SQL window functions deal with data's location in the set. In this post, we explain LEAD, LAG, and other positional functions. SQL window functions allow us to aggregate data while still using individual row values. We've already dealt with ranking functions and the use of partitions. In this post, we'll examine positional window functions, which are extremely helpful in reporting and summarizing data. Specifically, we'll look at LAG, LEAD, FIRST_VALUE and LAST_VALUE.
Read more
28 Aug 2017
Jenny Hung

Four Reasons You Must Learn SQL in Data Science

Is SQL important for data science? It certainly is! This language can help you build a foundation for your analytical career. Let’s see how you use SQL in data science. Data science is hot right now. What if you could predict the next market crash? Or contain the spread of Ebola? Or accurately predict a health crisis months or even years before it happens? Data scientists are working hard on these kinds of projects, and they are earning healthy salaries in the process.
Read more
21 Aug 2017
Kamila Ostrowska

5 Reasons Why No One Should Be Afraid to Learn SQL

Think SQL is just for tech experts? Think again. In this article, we'll bust some common myths and show you that anyone can learn SQL. Let's dive in and make SQL simple. In today’s world, even those in non-technical jobs need some technical skills. And you don’t have to be a hard-core nerd to get these skills. Everywhere you look, from marketing to management, a touch of tech knowledge can set you apart.
Read more
17 Aug 2017
Francisco Claria

Referential Constraints and Foreign Keys in MySQL

Foreign keys and referential constraints allow you to set relationships between tables and modify some of the database engine’s actions. This beginner’s guide explains referential integrity and foreign key use in MySQL. One of the most important aspects of database usage is being able to trust the information you store. Database engines provide several features that help you maintain the quality of your data, like defining required columns as NOT NULL and setting an exact data type for each column.
Read more
17 Aug 2017
Dorota Wdzięczna

An Illustrated Guide to the SQL OUTER JOIN

We’ve already discussed the SQL CROSS JOIN and INNER JOIN statements. It’s time to explore another: OUTER JOIN. What is it? How does it work? Let’s find out! If you’ve read our other posts, you know that you can link the data in two or more database tables using one of the many types of SQL join operator. Today, we’ll discuss the three kinds of OUTER JOIN: LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN.
Read more
7 Aug 2017
Aldo Zelen

SQL Window Functions: Partitions and Ranking Functions

You’ve started your mastery of SQL window functions by learning RANK, NTILE, and other basic functions. In this article, we will explain how to use SQL partitions with ranking functions. Mastering SQL window functions (AKA analytical functions) is a bumpy road, but it helps to break the journey into logical stages that build on each other. In the previous Common SQL Functions article, you learned about the various rank functions, which are the most basic form of window functions.
Read more
4 Aug 2017
Ignacio L. Bisso

SQL Window Function Example With Explanations

Interested in how window functions work? Scroll down to see our SQL window function example with definitive explanations! SQL window functions are a bit different; they compute their result based on a set of rowsrather than on a single row. In fact, the “window” in “window function” refers to that set of rows. Window functions are similar to aggregate functions, but there is one important difference. When we use aggregate functions with the GROUP BY clause, we “lose” the individual rows.
Read more
31 Jul 2017
Marek Pankowski

How Recursive Common Table Expressions Work

Recursive Common Table Expressions are immensely useful when you're querying hierarchical data. Let's explore what makes them work. Common Table Expressions (CTEs) are some of the most useful constructions in SQL. Their main purpose is improving query design, which makes queries easier to read. One of the reasons CTEs are so popular is that they let you divide longer queries into shorter subqueries. These are easier to read and edit.
Read more
24 Jul 2017
Dejan Sarka

High Performance Statistical Queries – Skewness and Kurtosis

In descriptive statistics, the first four population moments include center, spread, skewness, and kurtosis or peakedness of a distribution. In this article, I am explaining the third and fourth population moments, the skewness and the kurtosis, and how to calculate them. Mean uses the values on the first degree in the calculation; therefore, it is the first population moment. Standard deviation uses the squared values and is therefore the second population moment.
Read more
20 Jul 2017
Dorota Wdzięczna

An Illustrated Guide to the SQL INNER JOIN

What is an SQL INNER JOIN, and how does it work? Let's find out! In my last article, I discussed the CROSS JOIN operation in SQL. Today, we'll look at INNER JOIN and how to use it. Is it the same as a JOIN? How many tables can you link with an INNER JOIN? These are all good questions. Let's look at the answers! What is an INNER JOIN?
Read more