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;*/

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

/*select *,
FIRST_VALUE(product_name) over(partition by product_category order by price desc)
from product*/

-- Write query to display the least expensive product under each category (corresponding to each record)
/*select *,
LAST_VALUE(product_name) over(partition by product_category order by price desc
                                range between unbounded preceding
                                and unbounded following)
from product*/

-- Alternate way to write SQL query using Window functions
/*select *,
FIRST_VALUE(product_name) over w as most_expensive_Product,
LAST_VALUE(product_name) over w as least_expensive_Product
from product
window w as (partition by product_category order by price desc
                                range between unbounded preceding
                                and unbounded following)*/
                                
-- Write query to display the Second most expensive product under each category.
/*select *,
NTH_VALUE(product_name,2) over w as second_most_expensive_Product
from product
window w as (partition by product_category order by price desc
                                range between unbounded preceding
                                and unbounded following)*/
                                
  
-- NTILE
-- Write a query to segregate all the expensive phones, mid range phones and the cheaper phones.
/*select *,
case 
    when segregation=1 then 'expensive'
    when segregation=2 then 'mid-range'
    else 'cheaper'
end as ordering
from(
select *,
NTILE(3) over (order by price desc) as segregation
from product
where product_category = 'Phone') x
*/


-- Query to fetch all products which are constituting the first 30% 
-- of the data in products table based on price.
/*Select product_name,cume_dist_perce
from(
Select *,
CUME_DIST() over (order by price desc) as cume_dist_calcu,
Round(CUME_DIST() over (order by price desc)*100,2) as cume_dist_perce
from product) x
where x.cume_dist_perce<=30
*/
-- Query to identify how much percentage more expensive is "Galaxy Z Fold 3" when compared to all products.
Select product_name, expensive_calc_per
from(
Select *,
Percent_rank () over (order by price desc) as expensive_calc,
Round(Percent_rank () over (order by price desc)*100,2) as expensive_calc_per
from product)x
where x.product_name='Galaxy Z Fold 3'