Objective

Write a SQL query to display the month-year with the highest and lowest opening prices for each stock, along with the corresponding open prices.

Table Schema:

• stock_prices

Task Requirements:

• For each stock ticker, identify the record with the highest opening price and the record with the lowest opening price
• Extract the month and year from the date for both the highest and lowest records
• Display both extremes in a single row per ticker
• Format the month-year as Mon-YYYY (e.g., Jan-2022)
• Sort the results by ticker in ascending order
  
  

Output columns: ticker, highest_month, highest_open, lowest_month, lowest_open

WITH ranked AS (
    SELECT
        ticker,
        TO_CHAR(date, 'Mon-YYYY') AS month_year,
        open,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open DESC) AS high_rank,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open ASC) AS low_rank
    FROM stock_prices
)
SELECT
    h.ticker,
    h.month_year AS highest_month,
    h.open AS highest_open,
    l.month_year AS lowest_month,
    l.open AS lowest_open
FROM ranked h
JOIN ranked l
    ON h.ticker = l.ticker
    AND l.low_rank = 1
WHERE h.high_rank = 1
ORDER BY h.ticker;

Explanation

Step 1: Setting Up the CTE with Rankings

WITH ranked AS (
    SELECT
        ticker,
        TO_CHAR(date, 'Mon-YYYY') AS month_year,
        open,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open DESC) AS high_rank,
        ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY open ASC) AS low_rank
    FROM stock_prices
)

A Common Table Expression (CTE) computes two separate rankings for each stock in one pass. high_rank assigns rank 1 to the row with the highest open price per ticker, while low_rank assigns rank 1 to the row with the lowest. PARTITION BY ticker ensures rankings restart for each stock.

Step 2: Formatting the Date

TO_CHAR(date, 'Mon-YYYY') AS month_year

TO_CHAR converts the full date into a Mon-YYYY format like Jan-2022 or Nov-2021. This extracts just the month and year as required by the output.

Step 3: Self-Joining for Both Extremes

FROM ranked h
JOIN ranked l
    ON h.ticker = l.ticker
    AND l.low_rank = 1
WHERE h.high_rank = 1

The CTE is joined to itself on ticker. The alias h is filtered to high_rank = 1 (highest open), and alias l is filtered to low_rank = 1 (lowest open). This brings both the highest and lowest records for each stock into a single row.

Step 4: Selecting the Output Columns

SELECT
    h.ticker,
    h.month_year AS highest_month,
    h.open AS highest_open,
    l.month_year AS lowest_month,
    l.open AS lowest_open

The highest month and price come from h (the high-ranked row), while the lowest month and price come from l (the low-ranked row). Each ticker produces exactly one output row with both extremes side by side.

Step 5: Sorting by Ticker

ORDER BY h.ticker

The results are sorted alphabetically by ticker so AAPL appears first, followed by AMZN, GOOG, META, and NFLX.