[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.
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.