Database Reference
In-Depth Information
function should be applied. Additionally, the window function can be applied to
groupings of a given dataset using the
PARTITION BY
clause. The following SQL
query provides the customer rankings based on sales within product categories.
SELECT s.category_name,
s.customer_id,
s.sales,
RANK()
OVER (
PARTITION BY s.category_name
ORDER BY s.sales DESC ) AS sales_rank
FROM (SELECT c.category_name,
r.customer_id,
SUM(r.item_quantity * r.item_price) AS sales
FROM orders_recent r
LEFT OUTER JOIN product p
ON r.product_id = p.product_id
LEFT OUTER JOIN category c
ON p.category_id = c.category_id
GROUP BY c.category_name,
r.customer_id) s
ORDER BY s.category_name,
sales_rank
category_name customer_id sales sales_rank
Apparel 596396 4899.93 1
Apparel 319036 2799.96 2
Apparel 455683 2799.96 2
Apparel 468209 2700.00 4
Apparel 456107 2118.00 5
.
.
.
Apparel 430126 2.20 78731
Automotive Parts and Accessories 362572 5706.48 1
Automotive Parts and Accessories 587564 5109.12 2
Automotive Parts and Accessories 377616 4279.86 3
Automotive Parts and Accessories 443618 4279.86 3
Automotive Parts and Accessories 590658 3668.55 5
