SQL – Sparse data in time series


[I wasn’t sure if this is the proper place to post SQL questions.] I have a public dataset of pharmaceutical prices. Any given drug gets a new price on some unpredictable day, and then the price remains that price until the next price change.

E.g.

drug            date          new_price acetaminophen   2020-01-09    0.25 oxycontin       2020-01-10    1.40 valaxirin       2020-02-10    2.34 oranicin        2020-02-11    1.54 acetaminophen   2020-02-12    1.47 

I have to do a variety of analytics e.g. "what was the price of acetaminophen on 2020-02-01?" Well that would of course be 0.25, but I need a way to figure that out in SQL. I have a variety of more complex queries, e.g. "list the ten cheapest drugs on a given date". So a solution I think needs to be generalized.

I realize that one possible solution would be to run a job that populates the database with prices for every day of the year, but I prefer not to solve the problem that way.