Getting price difference between two dates

July 21, 2022

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.

>Solution :

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;