Question

Writea SELECT statement that uses the ranking functions to rank products by the total quantity sold. Returnthese columns:The product_name column from the Products tableA column named total_quantity that shows the sum of the quantity for each product in theOrder_Items tableA column named rank that uses the RANK function to rank the total quantity in descending sequenceA column named dense_rank that uses the DENSE_RANK function to rank the total quantity in descending sequence

Answer 1

Answer:

SELECT  product_name, SUM(DISTINCT quantity) AS total_quantity

RANK() OVER (PARTITION BY total_quantity ORDER BY product_name) AS rank,

DENSE_RANK () OVER (ORDER BY quantity DESC) AS dense_rank

FROM Order_items

JOIN products ON Order_items.product_id = products.product_id

GROUP BY product_id

Explanation:

The SQL query returns four columns namely, product name, total quantity, rank and dense rank. The query uses the rank and the dense rank function to return the rows according to numeric ranks.