0

I have a table named stock_info with the following columns: dt, stock, close_price. I would like to make a query that would calculate the rolling three month price change for each stock. The previous date that will be used will be the first trading date after subtracting 3 months from the current date. For example, let's say the date we are calculating for is sept 26, the prev date to be used will be june 27, but that could fall in a holiday or a weekend so we are selecting the date after that where the markets are open. Note also that even though the markets are open, some stocks may not be traded on that day because they are suspended. So in this case, the prev date to be used will be the next date when the suspension is lift off and they can be traded.

6
  • What version of MySQL? (8.0 has OVER...PARTITION BY)
    – Rick James
    Commented Sep 26, 2022 at 17:09
  • @rick james. I'm using mysql 8. I'm on the verge of getting the answer. can you please fix this code for me: min(dt) over(partition by stock order by dt range between interval 3 month -1 day preceding and current row) as prev_date
    – jomx99
    Commented Sep 26, 2022 at 17:15
  • @rick james. my problem is on combining the month and day to use as the beginning of the frame
    – jomx99
    Commented Sep 26, 2022 at 17:20
  • LEFT(date, 7) -- Example: SELECT LEFT(NOW(), 7); --> 2022-09 (OK that does not address your first Comment)
    – Rick James
    Commented Sep 26, 2022 at 23:40
  • SELECT stock, AVG(close_price) OVER ... FROM ... GROUP BY stock -- maybe
    – Rick James
    Commented Sep 26, 2022 at 23:44

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.