Getting price difference between two dates-CodePudding

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.

CodePudding user response：

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;