SOME SAMPLES OF OF WINDOW FUNCTION
AGGREGATION ON SQL
-- Running Totals
SELECT order_date,
sum(order_amount) OVER (ORDER BY order_date)
AS running_total
FROM orders;
-- Moving Average
SELECT order_date,
AVG(order_amount) OVER (ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS
moving_average
FROM orders;
-- Rank
SELECT product_name, product_price, DENSE_RANK()
OVER (ORDER BY product_price DESC) AS rank
FROM products;
-- Percent Rank
SELECT product_name, product_price, PERCENT_RANK()
OVER (ORDER BY product_price DESC) AS
percent_rank
FROM products;
Sebastiano Gazzola Follow me on linkedin
@sebastianogazzola for the next tip!
SOME SAMPLES OF OF WINDOW FUNCTION
AGGREGATION ON SQL
-- Lead and Lag
SELECT order_date, order_amount,
LAG(order_amount) OVER (ORDER BY order_date)
AS prev_amount,
LEAD(order_amount) OVER (ORDER BY order_date)
AS next_amount
FROM orders;
-- First Value and Last Value
SELECT order_date, order_amount,
FIRST_VALUE(order_amount) OVER (ORDER BY
order_date) AS first_amount,
LAST_VALUE(order_amount) OVER (ORDER BY
order_date) AS last_amount
FROM orders;
-- Cumulative Distribution
SELECT product_name, product_price, CUME_DIST()
OVER (ORDER BY product_price) AS
cumulative_dist
FROM products;
Sebastiano Gazzola Follow me on linkedin
@sebastianogazzola for the next tip!
SOME SAMPLES OF OF WINDOW FUNCTION
AGGREGATION ON SQL
-- Ntile
SELECT product_name, product_price,
NTILE(4) OVER (ORDER BY product_price)
AS quartile
FROM products;
-- Percentile Cont
SELECT product_name, product_price,
PERCENTILE_CONT(0.5) WITHIN GROUP
(ORDER BY product_price) OVER ()
AS median_price
FROM products;
-- Concatenation
SELECT department, employee_name,
STRING_AGG(phone_number, ', ') WITHIN
GROUP (ORDER BY phone_number) AS
phone_list
FROM employees
GROUP BY department, employee_name;
Sebastiano Gazzola Follow me on linkedin
@sebastianogazzola for the next tip!
SOME SAMPLES OF OF WINDOW FUNCTION
AGGREGATION ON SQL
-- Dense Rank with Ties
SELECT employee_name, department,
DENSE_RANK() OVER (PARTITION BY
department ORDER BY salary DESC)
AS dense_rank
FROM employees;
-- Sum with Grouping Sets
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS ((department), ());
Sebastiano Gazzola Follow me on linkedin
@sebastianogazzola for the next tip!
