5. Window Functions for Data Analysis

Activity 7: Analyzing Sales Using Window Frames and Window Functions

Solution

1. Open your favorite SQL client and connect to the sqlda database.
2. Calculate the total sales amount for all individual months in 2018 using the SUM function:

   SELECT sales_transaction_date::DATE,
   SUM(sales_amount) as total_sales_amount
   FROM sales
   WHERE sales_transaction_date>='2018-01-01'
   AND sales_transaction_date<'2019-01-01'
   GROUP BY 1
   ORDER BY 1;

   The following is the output of the preceding code:

   Figure 5.15: Total sales amount by month

3. Now, calculate the rolling 30-day average for the daily number of sales deals, using a window frame:

   WITH daily_deals as (
   SELECT sales_transaction_date::DATE,
   COUNT(*) as total_deals
   FROM sales
   GROUP BY 1
   ),
   moving_average_calculation_30 AS (
   SELECT sales_transaction_date, total_deals,
   AVG(total_deals) OVER (ORDER BY sales_transaction_date ROWS BETWEEN 30 PRECEDING and CURRENT ROW) AS deals_moving_average...