/*
Goldman Sachs SQL Interview Question

You are given a list of exchange rates from various currencies to US Dollars (USD) in different months. Show how the exchange rate of all the currencies changed in the first half of 2020. Output the currency code and the difference between values of the exchange rate between July 1, 2020 and January 1, 2020.
*/
    
-- Schema Setup
CREATE TABLE sf_exchange_rates ( date DATETIME, exchange_rate FLOAT, source_currency VARCHAR(10), target_currency VARCHAR(10));

INSERT INTO sf_exchange_rates (date, exchange_rate, source_currency, target_currency) VALUES ('2020-01-01', 1.12, 'EUR', 'USD'), ('2020-01-01', 1.31, 'GBP', 'USD'), ('2020-01-01', 109.56, 'JPY', 'USD'), ('2020-07-01', 1.17, 'EUR', 'USD'), ('2020-07-01', 1.29, 'GBP', 'USD'), ('2020-07-01', 109.66, 'JPY', 'USD'), ('2020-01-01', 0.75, 'AUD', 'USD'), ('2020-07-01', 0.73, 'AUD', 'USD'), ('2020-01-01', 6.98, 'CNY', 'USD'), ('2020-07-01', 7.05, 'CNY', 'USD');

-- Solution
WITH months_exchange_value AS
(
 SELECT
     source_currency,
     MAX(CASE WHEN MONTH(date) = 01 THEN exchange_rate ELSE 0 END) AS jan_exchange_value,
     MAX(CASE WHEN MONTH(date) = 07 THEN exchange_rate ELSE 0 END) AS july_exchange_value
 FROM sf_exchange_rates
 WHERE target_currency = 'USD'
 GROUP BY source_currency
)

SELECT
    source_currency,
    july_exchange_value - jan_exchange_value AS exchange_rate_diff
FROM months_exchange_value
ORDER BY source_currency;