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. &quot;what was the price of acetaminophen on 2020-02-01?&quot;  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. &quot;list the ten cheapest drugs on a given date&quot;.  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.
This is basic sql
For your second query there is only 1 drug at the given day
for the rest enter Postgre and what you are searching for ijn plain woirds and you usually get a result
The secnd query wrks like this. A subquery grabs with a window functions the last update date for everys drug.
When the originbal table is joined with the drug name and date changed you get the latest price
Which will be sorted by price and grab only the 10 with the lowest prices
Schema (PostgreSQL v12)
CREATE TABLE table1 (
  &quot;drug&quot; VARCHAR(13),
  &quot;date&quot; Date,
  &quot;new_price&quot; FLOAT
);

INSERT INTO table1
  (&quot;drug&quot;, &quot;date&quot;, &quot;new_price&quot;)
VALUES
  ('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');


Query #1
SELECT
     &quot;new_price&quot;
FROM table1
WHERE &quot;drug&quot; = 'acetaminophen'
AND &quot;date&quot;  &lt; '2020-02-01'
ORDER BY &quot;date&quot; DESC
LIMIT 1;

| new_price |
| --------- |
| 0.25      |


Query #2
SELECT
    t1.&quot;drug&quot;, 
    &quot;new_price&quot;
FROM table1 t1
INNER JOIN (SELECT 
            &quot;drug&quot;, 
            MAX(date) OVER (PARTITION BY &quot;drug&quot;  ORDER BY &quot;date&quot;)     AS max_date
        FROM table1
        WHERE 
    &quot;date&quot;  &lt;= '2020-02-10') t2 ON t1.&quot;drug&quot; = t2.&quot;drug&quot; AND t1.&quot;date&quot; = t2.max_date
ORDER BY &quot;new_price&quot; ASC
LIMIT 10;

| drug          | new_price |
| ------------- | --------- |
| acetaminophen | 0.25      |
| oxycontin     | 1.4       |
| valaxirin     | 2.34      |


Query #3
SELECT 
            &quot;drug&quot;, 
            MAX(date) OVER (PARTITION BY &quot;drug&quot;  ORDER BY &quot;date&quot;)     AS max_date
        FROM table1
        WHERE 
    &quot;date&quot;  &lt;= '2020-02-10'
;

| drug          | max_date                 |
| ------------- | ------------------------ |
| acetaminophen | 2020-01-09T00:00:00.000Z |
| oxycontin     | 2020-01-10T00:00:00.000Z |
| valaxirin     | 2020-02-10T00:00:00.000Z |


View on DB Fiddle