Practice SQL Window Functions | Xorthax's Trading Post

Tables Used

orderdetails

Column	Type
orderdetailid	bigint
orderid	bigint
productid	bigint
quantity	bigint
unitprice	double precision
currencyid	bigint

orders

Column	Type
orderid	bigint
customerid	bigint
orderdate	timestamp without time zone
totalamount	double precision
status	text

products

Column	Type
productid	bigint
name	text
description	text
category	text
price	double precision
supplierid	bigint
stockquantity	bigint

inventory

Column	Type
inventoryid	bigint
productid	bigint
quantityavailable	bigint
location	text
lastrestocked	text

Expected Output

Column
productid
productname
quantityavailable
totalrevenue
demandtier

39. The Rare and the Profitable

Group low-inventory products by revenue to find which rare items sell best.

Xorthax suspects that the rarest artifacts in his trading post — those with the lowest remaining stock — are also the most lucrative. To test this, examine products with low inventory and rank them into 4 tiers based on the total revenue they generated in the last 30 days.

Write Your Query