FAANG Underperforming Stocks (Part 3) Salesforce SQL Interview Question

As a trading analyst at Bloomberg, your task is to identify specific months when a majority of the FAANG stocks (Facebook, Amazon, Apple, Netflix, Google) experienced a gain in value compared to the previous month, while one stock lagged behind its peers by recording a decrease in value. This analysis involves comparing opening prices from the previous month.

In essence, you're seeking months where 5 out of 6 FAANG stocks demonstrated an increase in value with one stock experiencing a decline.

Write a query to display the month and year in 'Mon-YYYY' format along with the ticker symbol of the stock that underperformed relative to its peers, ordered by month and year (in 'Mon-YYYY' format).

Schema:

Example Input:

Note that the table below displays data in June and July 2023.

Example Output:

In July 2023, the GOOG stock underperformed relative to the other five FAANG stocks which is exhibited by comparing their opening prices in June 2023. The remaining FAANG stocks performed better in July 2023, except for GOOG which experienced a decline in value.

The dataset you are querying against may have different input & output - this is just an example!

Step 1: Extract Monthly Changes of Opening Prices

Our first task is to extract the opening prices for the current and previous months. To do so, we use the to fetch the previous month's opening price (). By extracting the previous month's opening price, we can compare it with the current month's opening price to determine if there was a gain in value.

Additionally, we use the TO_CHAR() function to format the date into the 'Mon-YYYY' format.

Output displaying the first five rows:

Step 2: Identify Months with Gain in Opening Prices

Next, we create a CTE named 'monthly_changes' using the query from Step 1.

In the main query, we identify the months during which each stock experienced a gain in value represented as . This involves using a , where a value of 1 is assigned when the current month's opening price is higher than the previous month's opening price (). Otherwise, a value of 0 is assigned.

By determining the gain for each stock and month, we can identify which stocks performed well in each month.

The output displays the individual gain counts for February 2020:

In February 2020, AAPL, AMZN, GOOG, MSFT, and NFLX each recorded a gain count of 1 indicating an increase in value. In contrast, META had a gain count of 0, signifying no gain.

This means that while 5 out of 6 FAANG stocks exhibit an increase in value, one stock (META) experienced a decline in value.

Step 3: Identify Underperforming Stock

Similarly, we create a CTE called 'monthly_gains', building upon the query from Step 2.

In this step, we summarize the stock performance for each month by utilizing the to calculate total gains.

Additionally, we use a to identify underperforming stocks for a given month, checking:

• Condition 1: If the total gains equal 5 () and,
• Condition 2: If the gain count for a specific stock is 0 ().

Assign the ticker if both conditions are met, otherwise assign NULL.

By summarizing the stock performance, we can identify months where 5 out of 6 stocks gained in value and one stock underperformed compared to its peers.

The output displays the stock performance for February 2020, highlighting the identified underperforming stock:

The column identifies any stocks that underperformed relative to their peers. In this instance, the stock with the ticker 'META' is flagged as underperforming as it recorded a gain count of 0 amidst a total gain count of 5 for the month.

Step 4: Filter for Underperforming Stocks

Likewise, we create a CTE called 'stock_summary' based on the query in Step 3.

In the final query, we refine the results by filtering for months where the total gains amount to 5 () and an underperforming stock is identified (). The results are then ordered by month-year.

The final output showing the months where these FAANG stocks showed underperformance compared to the other 5 stocks.