Back to questions
FAANG Stock Min-Max (Part 1) [Bloomberg SQL Interview Question]
The Bloomberg terminal is the go-to resource for financial professionals, offering convenient access to a wide array of financial datasets. As a Data Analyst at Bloomberg, you have access to historical data on stock performance.
Currently, you're analyzing the highest and lowest open prices for each FAANG stock by month over the years.
For each FAANG stock, display the ticker symbol, the month and year ('Mon-YYYY') with the corresponding highest and lowest open prices (refer to the Example Output format). Ensure that the results are sorted by ticker symbol.
Schema:
| Column Name | Type | Description |
|---|---|---|
| date | datetime | The specified date (mm/dd/yyyy) of the stock data. |
| ticker | varchar | The stock ticker symbol (e.g., AAPL) for the corresponding company. |
| open | decimal | The opening price of the stock at the start of the trading day. |
| high | decimal | The highest price reached by the stock during the trading day. |
| low | decimal | The lowest price reached by the stock during the trading day. |
| close | decimal | The closing price of the stock at the end of the trading day. |
Example Input:
Note that the table below displays randomly selected AAPL data.
| date | ticker | open | high | low | close |
|---|---|---|---|---|---|
| 01/31/2023 00:00:00 | AAPL | 142.28 | 142.70 | 144.34 | 144.29 |
| 02/28/2023 00:00:00 | AAPL | 146.83 | 147.05 | 149.08 | 147.41 |
| 03/31/2023 00:00:00 | AAPL | 161.91 | 162.44 | 165.00 | 164.90 |
| 04/30/2023 00:00:00 | AAPL | 167.88 | 168.49 | 169.85 | 169.68 |
| 05/31/2023 00:00:00 | AAPL | 176.76 | 177.33 | 179.35 | 177.25 |
Example Output:
| ticker | highest_mth | highest_open | lowest_mth | lowest_open |
|---|---|---|---|---|
| AAPL | May-2023 | 176.76 | Jan-2023 | 142.28 |
Apple Inc. (AAPL) achieved its highest opening price of $176.76 in May 2023 and its lowest opening price of $142.28 in January 2023.
The dataset you are querying against may have different input & output - this is just an example!
Solution
Step 1: Retrieve highest open price
Firstly, to find the highest open price for each ticker symbol, we use aggregate function along with the datetime function to format the date. Additionally, we apply the ranking function to assign a row number to each record within each ticker symbol group, ordered by open price in descending order.
We enclose this query within a CTE named .
Output of the first five rows:
| ticker | highest_mth | highest_open | row_num |
|---|---|---|---|
| AAPL | Jul-2023 | 195.26 | 1 |
| AAPL | Jun-2023 | 191.26 | 2 |
| AAPL | Aug-2023 | 187.48 | 3 |
| AAPL | May-2023 | 176.76 | 4 |
| AAPL | Mar-2022 | 174.40 | 5 |
Step 2: Retrieve lowest open price
Similarly, to determine the lowest open price for each ticker symbol, we utilize the aggregate function in conjunction with the datetime function. Then, we apply the ranking function to assign a sequential row number to each record within each ticker symbol group, ordered by open price in ascending order.
Likewise, this query will be wrapped as a CTE named .
Output of the first five rows:
| ticker | lowest_mth | lowest_open | row_num |
|---|---|---|---|
| AAPL | Apr-2020 | 61.63 | 1 |
| AAPL | Mar-2020 | 70.57 | 2 |
| AAPL | May-2020 | 71.56 | 3 |
| AAPL | Jan-2020 | 74.06 | 4 |
| AAPL | Feb-2020 | 76.07 | 5 |
Step 3: Combine CTEs and sort results
In the final step, we combine the and CTEs using an on the ticker symbol. We filter the results to include only rows where the row number in both CTEs is equal to 1, indicating the highest and lowest open prices.
Finally, we sort the results by the ticker symbol in ascending order.
PostgreSQL 14