15. Window Value Functions

Question 1

Return the Sales from previous row.

Answer 1

lag(Sales) over(order by OrderDate)

Question 2

Return the Sales from next row.

Answer 2

lead(Sales) over(order by OrderDate)

Question 3

Analyze the month-over-month (MoM analysis) by finding percentage change in sales between current and previous months.

Answer 3

with monthly_sales as (
select
  datetrunc(month, OrderDate) as Month,
  sum(Sales) currentSalesPerMonth
from MyOrders
group by datetrunc(month, OrderDate)
)
select
  Month,
  currentSalesPerMonth,
  lag(currentSalesPerMonth) over(order by Month) as prevSalesPerMonth,
  (currentSalesPerMonth - coalesce(lag(currentSalesPerMonth) over(order by Month), currentSalesPerMonth))* 100.0 / coalesce(lag(currentSalesPerMonth) over(order by Month), 1) as percentChange
from monthly_sales
order by Month;

Question 4

Order the customers based on the average days between their orders.

Customers with least avg gap between orders tend to be loyal.

Answer 4

with customer_orders as (
select
  CustomerID,
  OrderDate,
  lead(OrderDate) over(partition by CustomerID order by OrderDate) as nextOrderDate
from MyOrders
)
select
  CustomerID,
  avg(datediff(day, OrderDate, nextOrderDate)) as avgDaysUntilNextOrder
from customer_orders
group by CustomerID
order by coalesce(avg(datediff(day, OrderDate, nextOrderDate)), 9999);

Question 5

Find the average shipping duration (in days) per month.

Answer 5

with order_shipping as (
select
  OrderID,
  OrderDate,
  ShipDate,
  datediff(day, OrderDate, ShipDate) as shippingDays,
  datetrunc(month, OrderDate) as Month
from MyOrders
)
select
  Month,
  avg(shippingDays) as avgShippingDayPerMonth
from order_shipping
group by Month;

Question 6

Find the lowest and highest sales value for each product..

Answer 6

select
  ProductID,
  max(Sales) as highestSalesPerProduct,
  min(Sales) as lowestSalesPerProduct
from MyOrders
group by ProductID;

Question 7

Find the OrderIDs of the lowest and highest sales value for each product.

Answer 7

with product_sales as (
select
  OrderID,
  ProductID,
  Sales,
  max(Sales) over(partition by ProductID) as MaxSalesPerProduct,
  min(Sales) over(partition by ProductID) as MinSalesPerProduct
from MyOrders
)
select
  *
from product_sales
where Sales = MaxSalesPerProduct or Sales = MinSalesPerProduct;