Posts

Showing posts from January 24, 2019

PostgreSQL Performance: Query to find stocks reaching 52 week highs (joining rows of max values)

Image
2 I have a very simple database structure with "end of day" stock prices which look similar to: finalyzer_pricedata=> d pdEndOfDayPricEentity Table "public.pdendofdaypriceentity" Column | Type | Collation | Nullable | Default ---------------+---------------+-----------+----------+--------- id | uuid | | not null | close | numeric(19,2) | | not null | day | date | | not null | instrument_id | uuid | | not null | (instrument_id is the unique ID of the stock) I now want to select all instrument_id which reach their 52 week high in the current week. (ie. all stocks where the close column in the last 7 days is higher than in the 52 week before that) I