Master SQL Window Functions: FIRST_VALUE, LAST_VALUE, and More!

In this video, we'll explore SQL Window Functions, including:

FIRST_VALUE and LAST_VALUE: Find the most and least expensive products in each category.
NTILE: Categorize products into "Expensive Phones," "Mid Range Phones," and "Cheaper Phones."
Understanding Frame Clauses: Learn how they affect your results.
Check out the example queries and get a solid understanding of how to use these functions in your own SQL projects.

select * from product;

DROP TABLE product;
CREATE TABLE product
(
product_category varchar(255),
brand varchar(255),
product_name varchar(255),
price int
);

INSERT INTO product VALUES
('Phone', 'Apple', 'iPhone 12 Pro Max', 1300),
('Phone', 'Apple', 'iPhone 12 Pro', 1100),
('Phone', 'Apple', 'iPhone 12', 1000),
('Phone', 'Samsung', 'Galaxy Z Fold 3', 1800),
('Phone', 'Samsung', 'Galaxy Z Flip 3', 1000),
('Phone', 'Samsung', 'Galaxy Note 20', 1200),
('Phone', 'Samsung', 'Galaxy S21', 1000),
('Phone', 'OnePlus', 'OnePlus Nord', 300),
('Phone', 'OnePlus', 'OnePlus 9', 800),
('Phone', 'Google', 'Pixel 5', 600),
('Laptop', 'Apple', 'MacBook Pro 13', 2000),
('Laptop', 'Apple', 'MacBook Air', 1200),
('Laptop', 'Microsoft', 'Surface Laptop 4', 2100),
('Laptop', 'Dell', 'XPS 13', 2000),
('Laptop', 'Dell', 'XPS 15', 2300),
('Laptop', 'Dell', 'XPS 17', 2500),
('Earphone', 'Apple', 'AirPods Pro', 280),
('Earphone', 'Samsung', 'Galaxy Buds Pro', 220),
('Earphone', 'Samsung', 'Galaxy Buds Live', 170),
('Earphone', 'Sony', 'WF-1000XM4', 250),
('Headphone', 'Sony', 'WH-1000XM4', 400),
('Headphone', 'Apple', 'AirPods Max', 550),
('Headphone', 'Microsoft', 'Surface Headphones 2', 250),
('Smartwatch', 'Apple', 'Apple Watch Series 6', 1000),
('Smartwatch', 'Apple', 'Apple Watch SE', 400),
('Smartwatch', 'Samsung', 'Galaxy Watch 4', 600),
('Smartwatch', 'OnePlus', 'OnePlus Watch', 220);

select * from product;

-- 1) Write query to display the most expensive product under each category (corresponding to each record)

select * from product order by price desc;
select distinct product_category from product;

-- Wrong solution
select product_category , max(price), max(product_name)
from product
group by product_category

select *
, first_value(product_name) over(partition by product_category order by price desc) as most_exp_prod
from product ;
--order by product_category, price desc

-- 2) Write query to display the most & least expensive product under each category
-- (corresponding to each record)

select *
, first_value(product_name) over(partition by product_category order by price desc) as most_exp_prod
, last_value(product_name)
over(partition by product_category order by price desc) as least_exp_prod
, last_value(product_name)
over(partition by product_category order by price desc
RANGE between unbounded preceding and current row) as least_exp_prod
, last_value(product_name)
over(partition by product_category order by price desc
RANGE between unbounded preceding and unbounded following) as least_exp_prod
from product ;

-- FRAME CLAUSE
-- HIERARCHY OF DATA ::: RESULTSET == WINDOWS == FRAME

-- In PostgreSQL, the default frame clause is:
-- RANGE between unbounded preceding and current row

-- 3) Write query to display the 2nd most expensive product under each category
-- (corresponding to each record)

-- Using aggregate functions as window functions

select *
, max(price) over(partition by product_category order by price desc) as max_price
, min(price) over(partition by product_category order by price desc
range between unbounded preceding and unbounded following) as min_price
from product

-- Write a query to segregate all the expensive phones, mid range phones and the cheaper phones.

select product_name, price
, case when buckets = 1 then 'Expensive Phones'
when buckets = 2 then 'Mid Range Phones'
else 'Cheaper Phones'
end phone_category
from (
select *
, ntile(3) over(order by price desc) as buckets
from product
where product_category = 'Phone') x

Easily Delete Duplicate Rows in SQL Server with ROW_NUMBER()

Understanding Recursive CTEs in SQL: A Simple Guide

Master SQL PIVOT: Transform Rows into Columns
SQL for Beginners: A Complete Introduction to SQL Basics!

Master SQL Commands: DDL, Data Types & Constraints Explained!

Mastering SQL Constraints: Primary Key, Unique Key, Not Null, and CHECK Explained!

Understanding SQL Foreign Keys: Building Relationships Between Tables

SQL Identity Column: Auto-Generating Unique Values for Your Tables

SQL DML & DDL Commands Explained | Insert, Update, Delete, Truncate, Drop

If you found this video helpful, please like, subscribe, and hit the bell icon for more SQL tutorials!