5. (2 points) Calculate the 7-day (current day and 6 previous days, skip days without orders which are weekends as you’ve already known from Q2) rolling average of sales over time for Northwind. (HINT: Use Window Function. Set rolling average of first 6 rows to be null because there are no 7-days’ worth of information. Use WITH Clause to create temporary tables.)

Assuming we have a table `orders` with columns `order_id`, `order_date`, `customer_id`, and `order_total`, we can use the following query to calculate the 7-day rolling average of sales over time for Northwind, skipping weekends:

WITH daily_sales AS (
  SELECT order_date, SUM(order_total) AS total_sales
  FROM orders
  WHERE EXTRACT(DOW FROM order_date) NOT IN (0, 6) -- exclude weekends
  GROUP BY order_date
), daily_sales_ranked AS (
  SELECT order_date, total_sales, 
         RANK() OVER (ORDER BY order_date ASC) AS daily_rank
  FROM daily_sales
), rolling_average AS (
  SELECT order_date, total_sales, 
         CASE WHEN daily_rank <= 6 THEN NULL 
              ELSE AVG(total_sales) OVER (ORDER BY order_date ASC ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
         END AS sales_moving_average_7
  FROM daily_sales_ranked
)
SELECT *
FROM rolling_average
ORDER BY order_date ASC;

In this query, we first create a CTE called `daily_sales` that sums up the `order_total` for each `order_date` and excludes weekends using the `EXTRACT` function.

Then, we create another CTE called `daily_sales_ranked` that assigns a rank to each row based on the `order_date`.

Finally, we create a third CTE called `rolling_average` that calculates the 7-day rolling average of sales using the `AVG` window function and sets the rolling average of the first 6 rows to null.

The final SELECT statement retrieves all columns from the `rolling_average` CTE and orders the results by `order_date` in ascending order.