13. Window Aggregate Functions

Question 1

Find the number of non-NULL entries in column sales of table Orders.

Answer 1

select count(sales)
from Orders;

Note: if we use count(*), that will include count of null entries also.

Question 2

Display the total number of non-NULL entries in column Sales of table MyOrders, for each Product, alongside Sales amount.

Answer 2

select Product,
Sales,
count(Sales) over(partition by Product) as salesCount
from MyOrders;

Question 3

Find ProductIDs which are repeated (duplicate products) in Orders table.

Answer 3

select 
  ProductID,
  count(ProductID)
from MyOrders
group by ProductID
having count(ProductID) > 1;

Question 4

Find orderIds which are repeated (duplicate rows) in Orders table.

Answer 4

select 
  orderId,
	repetition
from(
select orderId,
count(orderId) over(partition by orderId) as repetition
from Orders
) t
where repetition > 1;

Question 5

Find the percentage contribution of each product’s sale to the total sales.

Answer 5

select
  ProductID,
  sum(Sales) as salesPerProduct,
  sum(sum(Sales)) over() grandTotalSales,
  cast((sum(Sales) * 100.0) / sum(sum(Sales)) over() as decimal(10,2)) as contribution
from MyOrders
group by ProductID;

Question 6

Find the average Sales for each product. Treat Null entries in Sales column as 0.

Answer 6

select
  ProductID,
  avg(coalesce(Sales, 0)) as meanSales
from MyOrders
group by ProductID;

Question 7

Find all orders with sales that are higher than the global average sales.

Answer 7

select
  *
from
(select
  OrderID,
  Sales,
  avg(Sales) over() avgSales
from MyOrders)t
where Sales > avgSales;

Question 8

Find the deviation of each sale from minimum and maximum.

Answer 8

select
  Sales,
  max(Sales) over() - Sales as deviationFromMax,
  Sales - min(Sales) over() as deviationFromMin
from MyOrders;

Question 9

Find the running total (cumulative sum) of revenue (= Quantity * Sales), for each month.

Answer 9

select OrderID, OrderDate,
  Quantity * Sales as revenue,
  sum(Quantity * Sales) over(partition by datetrunc(month, OrderDate) order by OrderDate) as runningTotalPerMonth
from MyOrders;

Question 10

Find the running total of Sales, running for each month in Orders table.

Answer 10

select
  Sales,
  OrderDate,
  sum(Sales) over(partition by datetrunc(month, OrderDate) order by OrderDate) as runningTotalPerMonth
from MyOrders;

Question 11

Find the rolling total of Sales, for each month in Orders table, with frame spanning previous order and current order.

Answer 11

select
  OrderDate,
  Sales,
  sum(Sales) over(partition by datetrunc(month, OrderDate) order by OrderDate rows between 1 preceding and current row) as rollingTotal 
from MyOrders;

Question 12

Find the moving average of sales for each product over time.

Answer 12

select
ProductID,
Sales,
avg(Sales) over(partition by ProductID order by OrderDate) as movingAverage
from MyOrders;

Question 13

Find the moving average of sales for each product over last 2 orders.

Answer 13

select
ProductID,
OrderDate,
Sales,
avg(Sales) over(partition by ProductID) as avgSalesPerProduct,
avg(Sales) over(partition by ProductID order by OrderDate rows between 2 preceding and current row) as movingAverage
from MyOrders
order by ProductID;