How to find products with dropped price in ClickHouse?

Question

In ClickHouse I have table product_prices with a history of product prices. This table includes the following attributes:

CREATE TABLE product_prices
(
    ProductId UInt64,
    TrackedAt Date,
    Price Nullable(Decimal(6, 2))
)
ENGINE MergeTree() ORDER BY (ProductId, TrackedAt)

From a predefined set of product_ids, I need to find those, for which the following condition is satisfied:
The latest price for this product_id is lower than the penultimate one.

---

Example:

| ProductId  | Price       | TrackedAt    |  
|:-----------|------------:|:------------:|  
| 1          |       20    |  2019-01-16  |  
| 1          |       19    |  2019-01-17  |  
| 2          |        5    |  2019-01-16  |  
| 2          |        7    |  2019-01-17  |  

I need to get

| ProductId  |
|:-----------|
| 1          |

I can find the difference only for one product:

select (argMax(Price, TrackedAt) - argMin(Price, TrackedAt)) from (select ProductId, Price, TrackedAt from product_prices where ProductId = 1000 order by TrackedAt DESC limit 2)

Do you know the way I can do this?

Answer 1

The basic idea is to use array to capture the local states for each product. By using a subquery that returns ascending records w.r.t TrackedAt, you can get ascending arrays for each product.

WITH groupArray(Price) AS ps
SELECT ProductId
FROM
(
    SELECT *
    FROM product_prices
    ORDER BY TrackedAt ASC
)
GROUP BY ProductId
HAVING (length(ps) > 1) AND (ps[-1] < ps[-2])