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:
- Load the
sqldadatabase with psql. - Using the
OVERandORDER BYstatements, 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 calledbat_sales_growth:SELECT *, sum(count) OVER (ORDER BY sales_date) INTO bat_sales_growth FROM bat_sales_daily;
- Compute a seven-day
lagof thesumcolumn, and then insert all the columns ofbat_sales_dailyand the newlagcolumn into a new table,bat_sales_daily_delay. Thislagcolumn 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...
