SQL - Sparse data in time series
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