7. The Scientific Method and Applied Problem Solving

Activity 7.01: Quantifying the Sales Drop

Solution

1. Load the sqlda database:

   $ psql sqlda

2. Compute the daily cumulative sum of sales using the OVER and ORDER BY statements. Insert the results into a new table called bat_sales_growth:

   sqlda=# SELECT *, sum(count) OVER (ORDER BY sales_transaction_date) INTO bat_sales_growth FROM bat_sales_daily;

   The following output should be produced:

   SELECT 964

   Compute a 7-day lag function of the sum column and insert all the columns of bat_sales_daily and the new lag column into a new table, called bat_sales_daily_delay. This lag column indicates what the sales were 1 week before the given record:

   sqlda=# SELECT *, lag(sum, 7) OVER (ORDER BY sales_transaction_date) INTO bat_sales_daily_delay FROM bat_sales_growth;

3. Inspect the first 15 rows of bat_sales_growth:

   sqlda=# SELECT * FROM bat_sales_daily_delay LIMIT 15;

   The following is the output of the preceding code:

   Figure 7.27: Daily sales...