Round 1
- Look at Bloomberg ticker. What can you tell me about this instrument
- IBM 10 P150 Equity
- It's a put option on ibm equity with 150 strike price
- When does it expire?
- 10/2016
- What day?
- third friday of the month
- Assume IBM stock is priced at 155. Calculate the MV of 1000 standard contracts of the option above if it were a Call
- 1000 contracts * 100 shares * (155 price - 150 strike price) = 500,000
- Assume you have compliance rules that specify certain tickers you cannot trade. These rules are specified at the firm level and at the fund level. Firm level restrictions apply to all funds. Write a query that shows all the funds and restricted tickers for each fund.
- select FundID, Ticker from Fund cross join Restrictions where FundID is null
- union
- select FundID, Ticker from Restrictions where not FundID is null
- You are given a table of ticker, date, and daily P&L. Find the top 10 tickers for each date by P&L.
- select * from (select date, ticker, rank = dense_rank() over (partition by date order by dailypnl desc) ) X where rank<=10
- You are given an array that represents a maze. 0 = space, 1 = wall, 2 = exit, 3 = start. How would you write a program that solves the maze?
- In a hand of texas hold-em, you have two tens. your opponent has queen, 9. The flop shows J, 10, 8. What is the probability of winning?
Round 2
- Design a database with the following data entities and constraints:
- DataEntities
- User - properties: user name
- Address - properties: street, city, state
- Order - properties: amount
- Office Location - properties: location name
- Constraints:
- User must have an address
- No two users can have the same address
- Every order has a user
- Not every user has an order
- User may have any number of office locations, including zero
- Solution:
- Users table has Primary key of User ID and a nullable address ID column with a Foreign Key and a unique constraint on address ID
- Address table has a primary key of address id
- Orders table has primary key of order id and non-nullable foreign key of user id.
- Office location table has primary key of office id
- UserOffice table has composite primary key of (userid, officeid) and foreign keys to user and office tables.
- Write a query and results based on tables in question 1 that would give name of all users, order id and amount of any orders for those users. E.g. Username, Order ID, Amount
- Table Data:
- User
- ID = 1, Name = U1
- ID = 2, Name = U2
- ID = 3, Name = U3
- Order
- ID = 101, UID = 1, Amount = 10
- 102, 2, 20
- 103, 1, 30
- 104, 2, 40
- 105, 1, 50
- 106, 2, 60
- Solution:
- Query
- select a.UName, b.OID, b.Amount
- from user a
- left join order b
- on a.UID=b.UID
- order by a.
UNam, b.OID, b.Amount
- Results
- U1 , 101, 10
-
U1, 103, 30
-
U1
, 105, 50
- U2, 102, 20
-
U2
, 104,40
-
U2
, 106,60
- U3, NULL, NULL
- Write a query and results
based on tables and data in question 1 & 2 that would give name of all users and total amount of any orders for those users
- Solution:
- Query:
- select a.
UName, total = Sum(Amount)
- from user a
- left join order b
- on a.UID=b.UID
- group by a.
UName
- Results
- U1, 80
- U2, 120
- U3, NULL
- Same as question 3, except only show users where total is greater than 100
- Solution
- Query:
- select a.
UName, total = Sum(Amount)
- from user a
- left join order b
- on a.UID=b.UID
- group by a.
UName
- having sum(Amount)>100
- Results
- U2, 120
- Same as question 3, except there's a new user with a duplicate name: ID=4, Name=U1 and a new order: ID=107, UID=4, Amount = 100. Ensure each unique user has their own total.
- Solution:
- Query:
- select a.
UName, total = Sum(Amount)
- from user a
- left join order b
- on a.UID=b.UID
- group by a.UID, a.
UName
- Results:
- U1, 80
- U2, 120
- U3, NULL
- U1, 100
- Write C# code that would give you the same output as question 3. Assume you have C# objects with properties that are equivalent to columns in the tables. Assume an object called DataService has methods getUsers() and getOrders() that return the user and order data, respectively.
- Solution:
- see Main() below
- Same as 6 except assume the data set is now very large (e.g. # of users >100,000 and # orders > 1,000,000). Hint: you will need a more efficient algorithm.
- Solution:
- See Alternate() below
sample code for #6 and #7
using System.Collections.Generic;
namespace MQuiz
{
public class User
{
public int UID { get; set; }
public string UName { get; set; }
}
public class Order
{
public int OID { get; set; }
public int UID { get; set; }
public float Amount { get; set; }
}
public class Program
{
public static void Main(string[] args)
{
var users = DataService.getUsers();
var orders = DataService.getOrders();
foreach (User u in users)
{
bool isMatched = false;
foreach(Order o in orders)
{
if(u.UID == o.UID)
{
isMatched = true;
System.Console.WriteLine(u.UName + " " + o.OID + " " + o.Amount);
}
}
if(!isMatched) { System.Console.WriteLine(u.UName + " NULL NULL"); }
}
}
public static void Alernative()
{
var users = DataService.getUsers();
var orders = DataService.getOrders();
var orderDictionary = new Dictionary<int, Dictionary<int, Order>>();
foreach(Order o in orders)
{
Dictionary<int, Order> orderList;
if (orderDictionary.ContainsKey(o.UID))
{
orderList = orderDictionary[o.UID];
}
else
{
orderList = new Dictionary<int, Order>();
}
orderList.Add(o.OID, o);
orderDictionary.Add(o.UID, orderList);
}
foreach (User u in users)
{
if (orderDictionary.ContainsKey(u.UID))
{
var oDict = orderDictionary[u.UID];
var o = oDict[u.UID];
System.Console.WriteLine(u.UName + " " + o.OID + " " + o.Amount);
}
else
{
System.Console.WriteLine(u.UName + " NULL NULL");
}
}
}
}
public static class DataService
{
public static List<User> getUsers() { return new List<User>(); }
public static List<Order> getOrders() { return new List<Order>(); }
}
}
