Thread by @marckohlbrugge

Ever wondered how you can sort records by a popularity metric, but add a time discount? (e.g. newer records rank higher)

I asked #gptchat and it came up with this formula:

ORDER BY upvotes_count * POW(0.998, extract(day FROM CURRENT_DATE - startups.featured_at)) DESC

It takes a popularity metric (in this case upvotes_count) and multiplies it by a time-discount multiplier.

The longer ago a startup was featured, the lower this number.

Here's how it works:

It calculates the days since the startup was featured:

extract(day FROM CURRENT_DATE - startups.featured_at)

For example, this might return 60 for a startup featured two months ago

It then uses the POW() function to turn it into 0.998⁶⁰ which equals about 0.89

This is used to discount the upvotes_count:

100 upvotes become × 0.89 = 89 "discounted" upvotes

The longer ago a startup was featured, the lower this multiplier will be.

These are all ranked about the same:

Featured today with 89 upvotes
Featured 2 months ago with 100 upvotes
Featured year ago with 185 upvotes

Where does the magic 0.998 number from?

I made it up. It's what determines how much time discounting you want to do.

I came to 0.998 by trial and error. I ran a bunch of queries and noticed that with 0.998 it discounts startups featured a year ago by about 50%.

I wanted to priortize newer startups over older startups. But you can also do the reverse.

If you multiply by a positive number like 1.01ʸ then recent records will rank lower versus older records with the same amount of upvotes.

Give it a try. Curious what you come up with :)