5. Window Functions for Data Analysis

Activity 5.01: Analyzing Sales Using Window Frames and Window Functions

Solution:

The solution to this activity is as follows:

1. Open pgAdmin, connect to the sqlda database, and open SQL query editor.
2. Calculate the total sales amount by day for all the days in the year 2021 (that is, before the date January 1, 2022).
3. The query for this step will be:

   SELECT 
     sales_transaction_date::date, 
     SUM(sales_amount) sales_amount
   FROM 
     sales
   WHERE
     sales_transaction_date::date BETWEEN '20210101' AND '20211231'
   GROUP BY
     sales_transaction_date::date;

The result is:

Figure 5.18: Daily Sales of 2021

4. Calculate the rolling 30-day average for the daily number of sales deals. The query for this step will be:

Activity5.01.sql

1 WITH 
2  daily_sales as (
3    SELECT 
4      sales_transaction_date::date, 
5      SUM(sales_amount) sales_amount
6    FROM 
7      sales
8...