Back to questions

FAANG Stock Monthly Change (Part 2) Bloomberg SQL Interview Question

FAANG Stock Monthly Change (Part 2)

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 for the FAANG stocks.

Your task is to analyze the inter-month change in percentage for each FAANG stock by month over the years. This involves calculating the percentage change in closing price from one month to the next using the following formula:

Inter-month change in percentage = (Current month's closing price - Previous month's closing price) / Previous month's closing price x 100

For each FAANG stock, display the ticker symbol, the last day of the month, closing price, and the inter-month value change in percentage rounded to two decimal places for each stock. Ensure that the results are sorted by ticker symbol and date in chronological order.

Schema:

Column NameTypeDescription
datedatetimeThe specified date (mm/dd/yyyy) of the stock data.
tickervarcharThe stock ticker symbol (e.g., AAPL) for the corresponding company.
opendecimalThe opening price of the stock at the start of the trading day.
highdecimalThe highest price reached by the stock during the trading day.
lowdecimalThe lowest price reached by the stock during the trading day.
closedecimalThe closing price of the stock at the end of the trading day.

Example Input:

Note that the table below displays randomly selected AAPL, AMZN, and GOOG data.

datetickeropenhighlowclose
01/31/2020 00:00:00AAPL74.0681.9673.1977.38
02/29/2020 00:00:00AAPL76.0781.8164.0968.34
03/31/2020 00:00:00AAPL70.5776.0053.1563.57
01/31/2020 00:00:00AMZN93.75102.7990.77100.44
02/29/2020 00:00:00AMZN100.53109.3090.5694.19
03/31/2020 00:00:00AMZN95.3299.8281.3097.49
01/31/2020 00:00:00GOOG67.0875.1667.0871.71
02/29/2020 00:00:00GOOG73.1076.6163.5566.97
03/31/2020 00:00:00GOOG67.5870.5150.6858.14

Example Output:

tickerdatecloseintermth_change_pct
AAPL01/31/2020 00:00:0077.38NULL
AAPL02/29/2020 00:00:0068.34-11.68
AAPL03/31/2020 00:00:0063.57-6.98
AMZN01/31/2020 00:00:00100.44NULL
AMZN02/29/2020 00:00:0094.19-6.22
AMZN03/31/2020 00:00:0097.493.50
GOOG01/31/2020 00:00:0071.71NULL
GOOG02/29/2020 00:00:0066.97-6.61
GOOG03/31/2020 00:00:0058.14-13.19
  • AAPL (Apple Inc.): Decrease in Feb 2020 (-11.68%) followed by a smaller increase in Mar 2020 (-6.98%).
  • AMZN (Amazon.com Inc.): Decrease in Feb 2020 (-6.22%), but an increase in Mar 2020 (3.50%).
  • GOOG (Alphabet Inc.): Similar pattern to AAPL with a drop in Feb 2020 (-6.61%) followed by a notable plunge in Mar 2020 (-13.19%).

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

Input

Output