I'm trying my hand at a couple of tester database interview questions; coming from a client side background, I'm doing a few to get up to scratch, and I'm currently struggling.
I have a portfolio table with 'TradeID', 'Ticker', 'Price', and 'Volume'. Currently 3 rows in the table, 2 buys of 10,000 and 1 sell of 10,000, all of the same company. I basically need to work out the daily portfolio value given a price. So if the current price is 1.00, the value total would be 10,000.
I've tried every subquery possible, but cannot for the life of me get it working, I end up with a aggregate or group by error, any help would be appreciated. Apologies if I've missed anything.
Edit: I'm trying to create a query like this:
select
sum
((sum(trade.volume) * 1.0) -
(sum(trade.volume * trade.price)))
from trade
Edit2: test data I currently have & making example easier:
1.0 is the price at close. I have 3 trades; trade.volume for the three would be +10,000, +10,000, -10,000 - all the same company trade.price : 1.1 - is the price the stock was bought at
so with sum((trade.volume) *1) I'm trying to get 10,000 as the current value from open volumes (sum(trade.volume * trade.price)) would be 11,000 as purchase price
If the sell is negative then
Select ticket, sum(volume*trade) from table group by ticket
I think this is what you have:
CREATE TABLE Portfolio (
TradeID int,
Ticker varchar(10), -- the company identifer
Price money,
Volume int, -- probably not actually an int
)
with data:
INSERT Portfolio VALUES ( 1, 'ACME', 1.1, 10000) -- a buy
INSERT Portfolio VALUES ( 2, 'ACME', 1.1, 10000) -- a buy
INSERT Portfolio VALUES ( 3, 'ACME', 1.1, -10000) -- a sell
Given
I basically need to work out the daily portfolio value given a price. So if the current price is 1.00, the value total would be 10,000.
I think you want the current net Volume
, multiplied by today's price:
DECLARE @priceNow money
SET @priceNow = 1.0
DECLARE @currentValue money
SELECT @currentValue = @priceNow * SUM(Volume) FROM Portfolio WHERE Ticker = 'ACME'
Note that the transaction price is irrelevant to knowing the currently-owned value.