The concept of window functions are very important while writing SQL queries and also building logic while writing transformation code in data processing frameworks like Apache spark. 

Suppose, you are given three tables reviews table, orders table and products table and you are asked to find top three product categories which were given highest reviews.

query:

select distinct temp.prod_category, temp.rank from (select prod.product_category_name as prod_category, dense_rank() over(partition by prod.product_category_name order by  reviews.review_score desc) as `rank` from `hardy-position-352014.ecommerce.products` prod  left join `hardy-position-352014.ecommerce.orders_list` ord on prod.product_id = ord.product_id left join `hardy-position-352014.ecommerce.reviews` reviews

on ord.order_id =  reviews.order_id) temp where temp.rank in (5, 4, 3) order by temp.rank desc