Homework H06

Your first day at LongShort, LLC

FinCalc

Learning Goal

This homework is designed to refresh your familiarity with SQL queries, or to get you started with SQL if you are new to the language.

Optional Prework

Check the SQL readings in Resources.  You will find reference materials to support your learning the SQL language.  Your favorite GenAI can help, too!

Context

It’s your first day at LongShort LLC, a small but prestigious hedge fund tucked into a quiet corner of Midtown Manhattan. The elevator doors open onto the trading floor. Multi‑screen workstations are flashing charts, code, and market data.
Tracy Talentpool, the firm’s HR manager, leads you through the floor with practiced ease. “Your performance during the interview process was exceptional,” she says with a professional smile. “We’re very lucky you chose to join us.”
She stops near a glass‑walled office at the far end of the room. Inside, a woman is speaking rapidly into a headset while scanning figures on four different screens. Her tone is calm but unmistakably in control.
“...and perfect timing!” Tracy says. “This is Ms. Penny Tradewell, our Chief Investment Officer... and your new manager.”
Ms. Tradewell removes her headset and looks up. Her eyes settle on you for a brief moment. A faint smile follows.
“Are you the new hire?” she asks.
“Yes, ma'am,” you reply, a bit more quickly than intended.
“Good.” Ms. Tradewell says. “Welcome to LongShort."
She glances at an HR screen. "You just graduated from that UVA Finance and IT program, correct?”
Before you can answer, she continues. “Good. We need someone with that skill set. Are you comfortable working with financial databases?”
“Yes. I...”
“Good.” she cuts in again, not unkindly, but decisively. “I’m finalizing a report and need several data points verified. Accuracy matters. Speed matters too.”
She slides a single page across her desk. “Please pull the data and bring it back to me.”
The conversation is clearly over.
“I am on it, Boss.”
A few minutes later, as you settle into your workstation, another junior analyst leans over.
“So, you met the CIO! She can be intense, but she knows her stuff. Everybody at LongShort respects her. When she asks for something, do it well, and do it fast.”
You nod, already opening your database tools.

Task

For this homework you must use VS and the database specified below. Watch the video below, consult the first section of my very short Introduction to SQL and write in one single file the ten queries that answer Ms. Tradewell's questions. Save the project with the queries. You do not need to save the answers. Zip it and submit it as H06.zip

Note: assignments requiring DB connections will not work from the big Shumway lab. You may use the two financial labs (in front of the Dean office), 395, and the last row of the PCs in 300, and the smaller Shumway Lab. 

FinCalc

Connection information:

  • Server: f-sg6m-s4.comm.virginia.edu
    (if on-grounds or using ADV, you do not need a VPN. If off grounds, you do).
  • Provider (if asked) : Microsoft Data SQLClient
  • Authentication Type: Windows Authentication    <<< this is different than what you did in COMM3220
  • Trust server certificate:  True
  • Server group: leave the default
  • DB: HedgeTournamentBETA

Criteria/Requirements

This assignment is more about using VS to connect to a database and writing queries than finding specific answers.  So, do not expect step-by-step instructions about how to write the queries. Figuring that out *is* the assignment. Some struggle is normal, especially if you are new to SQL.  If I gave you precise step-by-step instructions on what commands to use, you would not learn much. Use the listed resources.

Your work must satisfy the following requirements

  • All ten queries in a  single file, separated by a comment (e.g.; '-- Q1', '-- Q2'...)
  • Each question must be answered by a single query.
  • Each query must address only the question asked. Do not provide more information that requested.
  • Ensure that all result columns have an easy-to-understand name.
  • Do not format results unless explicitly requested. If asked, format $ figures with $ symbol, comma separating thousands, and two decimals
  • If a question does not ask for specific columns, provide them all, except duplicates.
  • If asked to show data about a person or an item, always include their ID.
  • Do not show duplicate records.

Ms. Tradewell's Questions

1. Produce a list of Team00 positions.
2. Produce a list of all the tickers in the StockMarket table. No repetitions. Alphabetized.
3. List all the transactions by Team03 (teamId = '03') in April 2025. Table: TransactionQueue.
4. List all the transactions by Team04 above $1,000,000. Order by date ascending. (This text is ambiguous. We will consider correct both sell and buy or only sell transactions).
5. What was the ask price for Apple on July, 25, 2025? Format the result.
6. What was the average bid price for Yeti in August 2025?
7. How many long (qty > 0) positions does Team00 have? Capital Account counts as long.
8. What is the mtm (mark to market = (bid+ask)/2)) price for Airbnb on July, 25, 2025?
9. What are the monthly average bid and ask prices for CROX? List all the months we have.
    Show month as a column - ok if you show it as a number.
10. At what average price have we sold shares of ULTA? Use all the transaction data we have. Careful: Ticker is called 'Symbol' in that table.

Hints and Tips

  •  Google and GenAI are your friends as you learn the SQL language. Do not just hunt for the right homework answers. Ask 'why?' until you feel you understand what is going on.
  • There is a common error made by beginners and intermediate SQL coders alike.  When you format a column with FORMAT, it becomes a string (i.e., text).  If you then rename the formatted column with its original name and later use it to sort the results with 'ORDER BY', the sort will not work as expected (it will be an alphabetic sort, not a numeric sort).  In an alphabetic sort $100 is less than $20 (I know... weird, eh? think it through or ask your GenAI to explain it).
    Easy fix: when you rename the formatted column with 'AS', change a little the column name and use the original name in the 'ORDER BY'.