ROW_NUMBER, PARTITION, & other reasons SQL is beautiful

The more I work with raw SQL, the more I enjoy it. Not only do I get a professional confidence boost, I also take back a bit of power over my data from whatever ORM happens to proliferate our codebase.

For example, we needed the lowest-priced offer per product for each marketplace.

Attempt #1

SELECT * FROM (
  SELECT ROW_NUMBER() OVER (
    PARTITION BY product_id, market ORDER BY price ASC
  ) AS r, t.* FROM offers t
) offers

WHERE offers.r = 1

That returns the best offer for each product in each marketplace. How? r refers to the "row number" of each record within its own partition i.e. grouped by product and market. Our WHERE clause allows us to only return the top offer. Results look something like:

r	product	price	market	size
1	100	45.99	amazon	c
2	100	49.00	walmart	a
1	200	15.00	walmart	d
2	200	26.50	amazon	e

Great. Next, we wanted to consider the user's size preferences.

The first solution was adding a WHERE clause to only include sizes that are in the user's preferences. But that's no good because it leaves us without any offer at all for the product when the user's size is unavailable. Instead, we can use a few cool features of SQL - WITH, arbitrarily selecting a raw value & conditional statements.

Let's create a "virtual property" in our offers selection. Let's also move it outside of our main query using WITH for readability. Then we'll sort by both our "virtual attribute" and price:

Attempt #2

WITH offers_a AS (
  SELECT *,
    CASE WHEN size IN (a, b)
      THEN 1
      ELSE 2
    END AS size_pref
  FROM offers
)

SELECT * FROM (
  SELECT ROW_NUMBER() OVER (
    PARTITION BY product_id, market ORDER BY size_pref ASC, price ASC
  ) AS r, t.* FROM offers_a t
) offers

WHERE offers.r = 1

Now, for each product, the query will return the lowest-priced offer in the user's preferred size or the lowest-priced offer in any size. Results now look something like this:

r	product	price	market	size	size_pref
1	100	49.00	walmart	a	1
2	100	45.99	amazon	c	2
1	200	15.00	walmart	d	2
2	200	26.50	amazon	e	2

The first product's offers swapped positions because size_pref takes precedence in our sorting.

---

We are able to feed other API endpoints with only minor alterations to our query. Primarily, we can remove the market partition wherever we load a product and its offers. That allows us to always display the most attractive, relevant price using a single query.

Feel the SQL ❤️ !!