SQL request for filter change produtc price. Rails app with PostgreSQL-CodePudding

Please tell me how you can make a query using SQL or ActiveRecord. I have table price_history: id, product_id, price, date . I need to sort those products whose price has changed more than 2 times. I am new to SQL. My application uses Ruby-on-Rails and PostgreSQL . I will be glad for any help. Thank you.

I probably didn't formulate my question clearly. I need to get something like the following:

table: prices_history
id  | product | price | date
--------------------------------
1     milk      2.0     01/01/2022
2     milk      2.5     10/01/2022
3     milk      6.0     20/02/2022
4     bread     2.0     01/01/2022
5     bread     2.1     10/01/2022
6     bread     3.0     20/02/2022

SQL request =>
milk: min_price = 2.0, max_price = 6.0, max_price/min_price = 3 > 2? it`s true
bread: min_price = 2.0, max_price = 3.0, max_price/min_price = 1,5 > 2? it`s false

result SQL request = > milk

CodePudding user response：

Thanks to those who answered. Based on your answers, I formed my request and it works.

SELECT 
  product,
  MIN(price) as first_price,
  MAX(price) as last_price
FROM
  prices_history
GROUP BY
  product
HAVING
  MAX(price)/MIN(price) > 2;

CodePudding user response：

I think just a simple query will give you the result you need.

SELECT
    id,
    price,
    date,
    product_id,
    count (product_id) 
FROM
    price_history
GROUP BY
    product_id
HAVING
    count (product_id) > 2;

more info on having function: https://www.postgresqltutorial.com/postgresql-having

CodePudding user response：

As per Rails,

In your application, define below association in Product, PriceHistory model

class Product
  has_many :price_histories
end

class PriceHostory
  belongs_to :product_id
end

Then below query to find the products having more than 2 price histories

Product.joins(:price_histories).group('products.id').having('count(price_histories) > 2')