postgresql – 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

Schema (PostgreSQL v12)

CREATE TABLE table1 (
  "drug" VARCHAR(13),
  "date" Date,
  "new_price" FLOAT
);

INSERT INTO table1
  ("drug", "date", "new_price")
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
     "new_price"
FROM table1
WHERE "drug" = 'acetaminophen'
AND "date"  < '2020-02-01'
ORDER BY "date"
LIMIT 1;

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

Query #2

SELECT
    "drug", "new_price"
FROM table1
WHERE 
    "date"  = '2020-02-10'
ORDER BY "new_price" ASC
LIMIT 10;

| drug      | new_price |
| --------- | --------- |
| valaxirin | 2.34      |

View on DB Fiddle