- ****************************************************************************
- ****************************** * ****
- ******************************************
Table 1: orders
An eCommerce company's online order table.
col_name | col_type --------------+------------------- order_id | bigint product_id | bigint customer_id | bigint order_dt | date qty | integer unit_price_usd| float channel | varchar(20) -- mobile, desktop
Table 2: product_category
One product can only belong to one category
col_name | col_type ----------------------+------------------- product_id | bigint product_category_id | smallint
Sample results
product_category_id | product_id | revenue
---------------------+------------+--------------------+---------
1 | 10000052 | 4586.589999999999
2 | 10000030 | 4731.81
3 | 10000098 | 5045.109999999999
Solution postgres
WITH prod_cat_revenue AS (
SELECT product_category_id, O.product_id, SUM(unit_price_usd * qty) AS revenue
FROM orders O
INNER JOIN product_category C
ON C.product_id = O.product_id
GROUP BY product_category_id, O.product_id
)
SELECT product_category_id, product_id, revenue
FROM (
SELECT product_category_id, product_id, revenue, RANK() OVER(PARTITION BY product_category_id ORDER BY revenue DESC) as ranking
FROM prod_cat_revenue
) X
WHERE X.ranking =2;
Explanation
This query is looking to find the second highest revenue generating product for each product category in a database.
First, it creates a temporary table called prod_cat_revenue that combines data from two tables, orders and product_category, and calculates the total revenue for each product in each category.
Then, the query selects the product category, product ID, and revenue from this temporary table, but adds an additional step. It uses the RANK() function to assign a ranking to each product within its respective category based on revenue.
Finally, the outermost query filters the results to only show the second highest ranking product (i.e. the product with the second highest revenue) for each product category.
Expected results