CODEWITHSUNDEEP
sqlpostgresql
traftOR1
traftOR1

Reputation: 3

Getting price difference between two dates

There is a table where once a day/hour lines are added that contain the product ID, price, name and time at which the line was added.

CREATE TABLE products 
(
    id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    product_id integer NOT NULL,
    title text NOT NULL,
    price double precision NOT NULL,
    checked_at timestamp with time zone DEFAULT now()
);

The data in the products table looks like this:

id product_id title price checked_at
1 1000 Watermelon 50 2022-07-19 10:00:00
2 2000 Apple 30 2022-07-19 10:00:00
3 3000 Pear 20 2022-07-19 10:00:00
4 1000 Watermelon 100 2022-07-20 10:00:00
5 2000 Apple 50 2022-07-20 10:00:00
6 3000 Pear 35 2022-07-20 10:00:00
7 1000 Watermelon 150 2022-07-21 10:00:00
8 2000 Apple 50 2022-07-21 10:00:00
9 3000 Pear 60 2022-07-21 10:00:00

I need to pass a date range (for example, from 2022-07-19 to 2022-07-21) and get the difference in prices of all unique products, that is, the answer should be like this:

product_id title price_difference
1000 Watermelon 100
2000 Apple 20
3000 Pear 40

I only figured out the very beginning, where I need to get the ID of all unique products in the table using DISTINCT. Next, I need to find the rows that are closest to the date range. And finally find the difference in the price of each product.

Upvotes: 0

Views: 357

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521457

You could use an aggregation approach here:

SELECT product_id, title,
       MAX(price) FILTER (WHERE checked_at::date = '2022-07-21') -
       MAX(price) FILTER (WHERE checked_at::date = '2022-07-19') AS price_difference
FROM products
GROUP BY product_id, title
ORDER BY product_id;

Upvotes: 2

Related Questions