SQL Puzzle Series 4: Which product should appear in the buy box?
If you are ever trying to build an e-commerce platform, then you are definitely in for a wild ride when it comes to selling products competitively when you have many providers of the same product in the market.
Generally, when you see products on a website, they are either sold to you directly by the e-commerce platform acting as a virtual seller itself OR a real seller using the e-commerce platform as a virtual marketplace. Which is why at time you might see one product featuring at different price points. Now, which product seller combination gets this real estate on the ecommerce is extremely important most you who buy stuff online usually buy the first product you see instead of what the other sellers are selling.
In case you hop on Amazon you click on a product you want, and you see the option to “See All Buying Options”, this is you will also see the list of other sellers selling the same product.
Now, if I wanted to design a website the simplest way to place my product on the buy box would be to use a price of the seller and ratings of the seller to rank this offer and put it up on the buy box.
We will go ahead and write the SQL query for this and get the top offer to place on the buy box.
For the sake of this example we will work through 3 scenarios —
- Sellers with different ratings and prices
In this example its extremely straightforward and simple example where Seller A selling the DualShock 4 Wireless Controller will get the buy box on the e-commerce website.
Unrelated to the coding part but think about what would happen in case Seller D decided to drop the price by 4 dollars and now has the lowest selling price. His rating is not too bad either why shouldn't they get the buy box? Something for the product managers to ponder, I guess.
2. Sellers with same rating but different prices (same applies if price is same and rating is different)
In this case notice how the ranks for Seller E and C is the same. This is because the Rank function repeats the rank for same values and skips the next rank in place. Lets add another seller selling the same product and see what would his Rank_By_Ratings be.
As you can see Seller F gets the 4th rank.
Now in SQL there are 2 other functions you can try out to remedy this “issue” — Dense_Rank and Row_Number. Lets see the results using the these two functions.
The Dense Rank follows the same suit as Rank but does not skip ranks. And Row Number just numbers the sellers sorted on ratings. Now which method is best? The answer is very ambiguous to say because it depends on the use case and how the solution solves the business problem.
These are extremely simplistic approaches to place your product on the buy box, the actual algorithm and effort that goes into this is much more complex some challenges to consider.
- A completely new product sold by a few sellers who has never been rated, then who gets the buy box?
- If the seller is highly rated but does not have the product in stock?
This example just a case for when we can use these querying techniques and some scenarios.
References:
Code is present here
How to Win the Amazon Buy Box in 2022 (FBA Sellers Guide) (repricerexpress.com)
