-- Run the following script that creates and populates two tables: CREATE TABLE #Stock (Symbol VARCHAR(4), TradingDate DATE, OpeningPrice MONEY, ClosingPrice MONEY);

INSERT INTO #Stock(Symbol, TradingDate, OpeningPrice, ClosingPrice) VALUES ('A','2014/01/02',5.03,4.90), ('B','2014/01/02',10.99,11.25), ('C','2014/01/02',23.42,23.44), ('A','2014/01/03',4.93,5.10), ('B','2014/01/03',11.25,11.25), ('C','2014/01/03',25.15,25.06), ('A','2014/01/06',5.15,5.20), ('B','2014/01/06',11.30,11.12), ('C','2014/01/06',25.20,26.00); CREATE TABLE #Rates (ID INT IDENTITY ,City varchar(20) ,Rate decimal (5,2) ,Logdate datetime); INSERT INTO #Rates (city, rate, logdate) VALUES ('Dallas', 1.5, '4/1/20'), ('Dallas', 2.5, '4/11/20'), ('Dallas', 4, '4/12/20'), ('Dallas', 3.5, '4/13/20'), ('Dallas', 2.5, '4/14/20'), ('Dallas', .5, '4/15/20'); /* Based on these two temporary tables, solve the following 4 Window Function problems: 1. Write a query against the #Stock table using a window analytic function that calculates the difference between the current closing price and the closing price from the previous day. 2. Modify the query written in Question 1 so that the NULLs in the calculation are replaced with zeros. Use an option in the window analytic function. 3. Write a query against the #Rates table that finds the percent rank of the rate along with the other columns. 4. Write a query against the #Rates table that returns all the columns and also returns the median rate.