Window Functions in SQL

Welcome to the course!

SQL (Structured Query Language) is a powerful tool for managing and manipulating databases. In this course, we’ll focus on learning window functions, which are a powerful feature for performing complex calculations and data analysis.

Each section in this course has a set of examples, exercises, and quizzes to help you learn.

Course Outline

Let’s get started!

What is SQL? SQL is a standard language for accessing and manipulating databases. It is used by data analysts, developers, and database administrators to manage data stored in relational databases.

What are Window Functions? Window functions perform calculations across a set of table rows that are somehow related to the current row. They are widely used in data analysis to perform complex calculations such as running totals, moving averages, and ranking without the need for self-joins.

Your first window function The following code is interactive. You can run the code by clicking the run button. The code will calculate the running total of sales.

 SELECT sales, SUM(sales) 
OVER (ORDER BY date) 
AS running_total 
FROM sales_data;

 

Congratulations! You’ve written your first window function in SQL.

Exploring an Example dataset In this course, we’ll use a simple dataset to learn about window functions. This dataset contains sales records with columns for sales amount, sales date, and product category.

To create a table, we can use the CREATE TABLE statement.

 CREATE TABLE sales_data (
  sales_amount INT,
  sales_date DATE,
  product_category VARCHAR(255)
);
 
INSERT INTO sales_data (sales_amount, sales_date, product_category) VALUES
(100, '2023-01-01', 'Electronics'),
(200, '2023-01-02', 'Electronics'),
(150, '2023-01-03', 'Furniture'),
(300, '2023-01-04', 'Furniture'); 

Review We made it through the first section! We learned how to write a window function to calculate a running total, create a simple dataset, and insert data into a table.