Mixing paid placement, recency, and relevance in a single query

2026-05-02

How I built the promotion ranking system for AutoMarket — keeping listings fresh and relevant while still rewarding dealers who pay for visibility, all from one Prisma query.

A marketplace has a job that's harder than it looks: order listings in a way that's fair to buyers, fresh enough to keep the catalog alive, and gives paying dealers something they actually feel they paid for. Most classifieds sites pick one and let the others rot.

On AutoMarket I tried to do all three from a single query.

The naive version — and why I didn't ship it

The obvious approach is two lists glued together: a "Featured" strip at the top with promoted listings, then everything else by createdAt DESC. It's easy. It also signals to buyers that the top of the page is an ad and they should scroll past it. Dealers pay, but the placement doesn't actually convert, and over time they stop paying.

I wanted promoted listings interleaved with organic ones — boosted, but not roped off — so they live or die based on whether buyers actually want them.

Three signals, one score

Every listing gets a score computed from three pieces:

• Paid weight — how much promotion the seller bought (none, retail per-listing, dealer-package tier).
• Recency — newer listings rank higher, but the boost decays so a week-old listing doesn't get permanently buried.
• Relevance — does the listing match the buyer's filters (make, price band, location)? An exact match outranks a near-match outranks a category match.

Final order is ORDER BY score DESC, createdAt DESC. The createdAt tiebreaker matters more than it looks — without it, every listing in a tied bucket comes back in unstable order across page loads.

Recency, but with a decay

A pure createdAt sort means yesterday's listing always beats last week's. That's brutal for sellers and bad for the catalog — anything older than a few days becomes invisible. I used a decay instead of a binary cliff:

exp(-age_in_days / 14.0)

A fresh listing scores 1.0, a week-old listing about 0.6, a month-old listing about 0.12. Old listings still appear; they just need help from one of the other signals to compete.

The 14.0 half-life was a guess that I tuned twice — once after looking at how long listings actually stay relevant in this market (longer than I thought), and once after a dealer complained that his car had "disappeared" after four days.

Paid weight without making it pay-to-win

The trap with paid placement is letting it dominate. If a promoted listing always wins, the ranking collapses into "ads first" and you're back to the naive version. I capped the paid contribution at roughly the same magnitude as a fresh, relevant listing — so a promoted listing with a stale or off-topic match still loses to an organic listing that fits what the buyer asked for.

Concretely: paid weight is additive (+0.4 for retail promotion, +0.7 for dealer package), not multiplicative. The relevance and recency signals can match or exceed it. A new BMW listing from a normal seller will outrank a week-old, irrelevant promoted Mercedes.

Doing it all in one query

The interesting part is that none of this needs an application-layer ranker. Postgres can compute the score in the SELECT, sort by it, and return a page — all in one round trip:

const listings = await prisma.$queryRaw`
  SELECT
    l.*,
    (
      COALESCE(p.weight, 0)
      + exp(-EXTRACT(EPOCH FROM (now() - l."createdAt")) / (14 * 86400))
      + ${relevanceScore(filters)}
    ) AS score
  FROM "Listing" l
  LEFT JOIN "Promotion" p
    ON p."listingId" = l.id AND p."expiresAt" > now()
  WHERE ${buildFilters(filters)}
  ORDER BY score DESC, l."createdAt" DESC
  LIMIT 24 OFFSET ${offset};
`;

relevanceScore is built per request from the filters the buyer applied — exact make match adds more than category match, price-band match adds a little, location proximity adds a little. The whole thing collapses into a numeric literal by the time it reaches Postgres, so the query plan stays predictable.

A partial index on Promotion(listingId) WHERE "expiresAt" > now() keeps the join cheap, and a btree on Listing("createdAt") covers the tiebreak.

What I'd change

Two things, if I were doing this again or extending it:

1. Move relevance to a stored column or materialized view once the filter set grows. Computing it inline is fine for five signals; at fifteen it'll start showing up in query times.
2. Log the score components, not just the final score. When a dealer asks "why is my listing on page 3?" you want to be able to say "because relevance was low and your promotion expired Tuesday," not just shrug.

The shape of the problem — blending business goals (paid), product health (fresh), and user intent (relevant) into one ordering — comes up everywhere: search results, feeds, recommendations. Most teams over-engineer it with a separate ranking service. For a single-database product, you can get a long way with one query and three honest signals.