MySQL_Retrieving stock price based on max and min_date

Question

I am trying to retrieve closed and opened stock prices from the first and last date per month.

For some reason, the output of the 'end_date_closed_stock_price' is NULL.

Do you know any idea why it is giving this result?

Also, could you tell me the appropriate queries to retrieve the last date of the month?

The followings are my queries and output.

Thanks in advance!

SELECT YEAR(date) AS years
     , MONTH(date) AS months
     , CASE WHEN date = MAX(date) THEN close END end_date_closed_stock_price
     , CASE WHEN date = MIN(date) THEN open END first_date_opened_stock_price
  FROM nasdaq_feb_25_1971_feb_25_2021
 GROUP 
    BY 1,2
 ORDER 
    BY 1 DESC;

---OUTPUT---
2020    5    NULL   9382.349609
2019    1    NULL   6947.459961
2019    2    NULL   7266.279785
2019    3    NULL   7582.290039

Answer 1

There is probably a more efficient solution, but this should work:

WITH dates as
  (SELECT YEAR(date) as years
     ,MONTH(date) AS months
     ,MAX(date) as end_date
     ,MIN(date) as first_date
   FROM nasdaq_feb_25_1971_feb_25_2021
   GROUP BY 1, 2)
SELECT dates.years
     , dates.months
     , price1.close as end_date_closed_stock_price
     , price2.open as first_date_opened_stock_price
  FROM dates
  JOIN nasdaq_feb_25_1971_feb_25_2021 price1
  ON price1.date = dates.end_date
  JOIN nasdaq_feb_25_1971_feb_25_2021 price2
  ON price2.date = dates.first_date
 ORDER 
    BY 1 DESC, 2 DESC;