Window Function (copy)

SELECT product_id, product_name, model_year, list_price FROM products

SELECT order_id, order_date FROM sales.orders 

SELECT order_id, product_id, discount FROM sales.order_items

SELECT
    model_year,
    AVG(list_price) avg_price
FROM products
GROUP BY model_year

SELECT
   model_year,
   product_name,
   list_price,
   AVG(list_price) OVER 
     (PARTITION BY model_year) avg_price
FROM products
ORDER BY product_name

/* Rank price from LOW->HIGH  */
SELECT 
product_name, 
list_price,
RANK() OVER (ORDER BY list_price ASC) rank
FROM products

   SELECT
    year(order_date) year,
    COUNT(DISTINCT order_id) num_orders
    FROM sales.orders
    GROUP BY year(order_date)

/* Rank price from HIGH->LOW  */
SELECT 
product_name, 
list_price,
RANK() OVER (ORDER BY list_price DESC) rank
FROM products

/* Find the difference of price from cheapest alternative */
SELECT
  product_name,
  list_price,
  FIRST_VALUE(list_price) OVER (ORDER BY list_price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) cheapest_price,
  list_price - FIRST_VALUE(list_price) OVER (ORDER BY list_price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) diff
FROM products

/* Find the difference of price from the priciest alternative */
SELECT
   product_name,
   list_price,
   LAST_VALUE(list_price) OVER (ORDER BY list_price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) highest_price,
   LAST_VALUE(list_price) OVER (ORDER BY list_price ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) - list_price diff
FROM products

/* Find number of orders in a year */
WITH yearly_orders AS (
   SELECT
    year(order_date) year,
    COUNT(DISTINCT order_id) num_orders
    FROM sales.orders
    GROUP BY year(order_date)
)
 
/* Compare this year's sales to last year's */
SELECT
*,
LAG(num_orders) OVER (ORDER BY year) last_year_order,
LAG(num_orders) OVER (ORDER BY year) - num_orders diff_from_last_year
FROM yearly_orders

/* Find number of orders in a year */
WITH yearly_orders AS (
   SELECT
    year(order_date) year,
    COUNT(DISTINCT order_id) num_orders
    FROM sales.orders
    GROUP BY year(order_date)
)
 
/* Compare number of years compared to next year */
SELECT *,
   LEAD(num_orders) OVER (ORDER BY year) next_year_order,
   LEAD(num_orders) OVER (ORDER BY year) - num_orders diff_from_next_year
FROM yearly_orders