Question 89339 in PostgreSQL Question posted in
The official documentation can be found here.

compare two records from now to end of year/month postgres – Postgresql

I am trying to compute a Year To Date performance of a stock.
My database is simple as per below

enter image description here

However I am struggling to find the right query

So far I am only able to show details for one date, surely it must be a more technical way to achieve this. If you would have some tutorial recommend or fairly junior documentation, it would be also greatly appreciated.

The expected result would be :

enter image description here

My best query so far, it gives the answer though

SELECT t1.contract_id, t1.timestamp_tick, t1.close, t2.timestamp_tick, t2.close, t2.close / NULLIF(t1.close,0) -1 as perf FROM 
(
SELECT * FROM candles where id in (
SELECT max(id)  FROM public.candles WHERE extract(year from timestamp_tick)= '2022' and freq = 1380
group by contract_id 
    ) order by contract_id
) as t1

    INNER JOIN

(
SELECT * FROM candles where id in (
SELECT max(id)  FROM public.candles WHERE extract(year from timestamp_tick)= '2023' and freq = 1380
group by contract_id 
)
) as t2 
ON t1.contract_id = t2.contract_id  order by t1.contract_id
Tags:

2

Answers


  1. 0

    Here is a simplified example of how I would do this. This solution does not allow for stocks missing any trades within a period (in this case, years) or with a period closing value of 0.

    with yr_closing as (
  select distinct on (contract_id, date_part('year', timestamp))
         date_part('year', timestamp) as year_close, *
    from candles
   order by contract_id, date_part('year', timestamp), timestamp desc
)
select *, 1.0 * close/(lag(close) over w) - 1 as perf  
  from yr_closing
window w as (partition by contract_id order by year_close);

    The yr_closing CTE gets the last row for each year that the stock was traded. You specified that you are interested in only year-to-date, but this could be adapted to produce historical year-over-year and month-to-date performance measures with a single pass.

    The distinct on is Postgres magic that gets the last row of the year. It is well worth learning how to exploit it.

    The date_part() simply reduces the timestamp column to the year number, same as extract().

    The main query uses the lag() window function to make the prior row available in the current row.

    The main query also illustrates the Postgres WINDOW window_name AS (window_definition) for no reason other than it is very convenient and I curse loudly when using other DBMSes because they do not have this feature.

    Please see the working fiddle.

    Login or Signup to reply.
  2. 0

    Remove your order by and add the concat(round( functions. Otherwise what you have in your question looks good.

    https://www.db-fiddle.com/f/mzopNiVexVKwh9HLHRde1n/0

    SELECT t1.contract_id, t1.close_date, t1.close_price, t2.close_date, t2.close_price, concat(ROUND(t2.close_price / t1.close_price - 1, 2) * 100, '%') FROM 
(
SELECT * FROM stockx where tx_id in (
SELECT max(tx_id)  FROM stockx WHERE YEAR(close_date)= '2022'
group by contract_id 
)
) as t1

    INNER JOIN

(
SELECT * FROM stockx where tx_id in (
SELECT max(tx_id)  FROM stockx WHERE YEAR(close_date)= '2023'
group by contract_id 
)
) as t2 
ON t1.contract_id = t2.contract_id  order by t1.contract_id
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search