9. Using SQL to Uncover the Truth – a Case Study

Activity 18: 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 table shows the daily cumulative sum of sales:

   Figure 9.48: Daily sales count

3. 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, bat_sales_daily_delay. This lag column indicates what the sales were like 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;

4. Inspect the first 15 rows of bat_sales_growth:

   sqlda=# SELECT * FROM bat_sales_daily_delay LIMIT 15;

   The following is the output of...