Amazon's product analytics team wants to identify catalog items whose latest monthly price increased consistently. Assume the reporting date is 2024-06-01, so the three complete months are March, April, and May 2024. Write a PostgreSQL query that returns products with a strictly higher month-end price in each successive month.
Requirements
- Select the latest recorded price for each product in each of the three months.
- Exclude products without exactly one qualifying monthly price for March, April, and May.
- Return products whose March price is less than April's price and April's price is less than May's price.
- Return the product ID, product name, and the three monthly prices, ordered by product ID.
Output details
- Return columns named
product_id, product_name, march_price, april_price, and may_price, in that order.
- Order results by
product_id ascending.
- When timestamps tie for a product-month, select the row with the greatest
price_history_id.