9. Using SQL to Uncover the Truth: A Case Study 

Activity 9.01: Quantifying the Sales Drop

Solution:

Perform the following steps to complete this activity:

1. Load the sqlda database with psql.
2. Using the OVER and ORDER BY statements, compute the daily cumulative sum of sales. This provides you with a discrete count of sales over a period of time on a daily basis. Insert the results into a new table called bat_sales_growth:

   SELECT 
     *, 
     sum(count) OVER (ORDER BY sales_date) 
   INTO 
     bat_sales_growth 
   FROM 
     bat_sales_daily;

3. Compute a seven-day lag of the sum column, and then insert all the columns of bat_sales_daily and the new lag column into a new table, bat_sales_daily_delay. This lag column indicates the sales amount a week prior to the given record, allowing you to compare sales with the previous week:

   SELECT 
     *, 
     lag(sum, 7) OVER (ORDER BY sales_date) 
   INTO 
     bat_sales_daily_delay...