9. Using SQL to Uncover the Truth – a Case Study
Activity 18: Quantifying the Sales Drop
Solution
- Load the
sqldadatabase:$ psql sqlda
- Compute the daily cumulative sum of sales using the
OVERandORDER BYstatements. Insert the results into a new table calledbat_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
- Compute a 7-day
lagfunction of thesumcolumn and insert all the columns ofbat_sales_dailyand the newlagcolumn into a new table,bat_sales_daily_delay. Thislagcolumn 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;
- Inspect the first 15 rows of
bat_sales_growth:sqlda=# SELECT * FROM bat_sales_daily_delay LIMIT 15;
The following is the output of...
