Prev: JDBC Driver
Next: db mail
From: stevenleongusa on 24 Sep 2007 14:54
I have given the following question. I need help on this.

Given a schema for a stock trades table that looks like:

*stocktrades (*
* symbol string, -- stock symbol*
* shares integer, -- number of shares traded*
* price float, -- price of the stock for this trade*
* timestamp integer -- timestamp in hhmmss 24-hour format*
* -- i.e., 000000 = midnight, 235959 = 11:59.59
PM*
*)*

Assuming the above table holds data for exactly one day of information
(midnight to 11:59.59 PM), write the query needed to summarize the
stock trades data into the following table that holds weighted average
price for each stock for every hourly period during that day:

*avgtrades (*
* symbol string, -- stock symbol*
* period integer, -- a one or two digit value representing*
* -- the hourly period of the trades*
* -- (0 = 000000-005959, 1 = 010000 - 015959...)*
* avgprice float -- weighted average price is the sum of*
* -- total dollars (price*shares) / total*
* -- number of shares traded in any interval*

From: Roy Harvey (SQL Server MVP) on 24 Sep 2007 15:24
I think this will do it.

SELECT symbol,
timestamp / 10000 as period,
SUM(shares * price) / SUM(shares) as avgprice
FROM StockTrades
GROUP BY symbol, timestamp / 10000

Roy Harvey
Beacon Falls, CT

On Mon, 24 Sep 2007 11:54:54 -0700, stevenleongusa(a)gmail.com wrote:

>I have given the following question. I need help on this.
>
>Given a schema for a stock trades table that looks like:
>
>*stocktrades (*
>* symbol string, -- stock symbol*
>* shares integer, -- number of shares traded*
>* price float, -- price of the stock for this trade*
>* timestamp integer -- timestamp in hhmmss 24-hour format*
>* -- i.e., 000000 = midnight, 235959 = 11:59.59
>PM*
>*)*
>
>Assuming the above table holds data for exactly one day of information
>(midnight to 11:59.59 PM), write the query needed to summarize the
>stock trades data into the following table that holds weighted average
>price for each stock for every hourly period during that day:
>
>*avgtrades (*
>* symbol string, -- stock symbol*
>* period integer, -- a one or two digit value representing*
>* -- the hourly period of the trades*
>* -- (0 = 000000-005959, 1 = 010000 - 015959...)*
>* avgprice float -- weighted average price is the sum of*
>* -- total dollars (price*shares) / total*
>* -- number of shares traded in any interval*
From: stevenleongusa on 24 Sep 2007 19:01
What is 10000 mean?

On Sep 24, 12:24 pm, "Roy Harvey (SQL Server MVP)"
<roy_har...(a)snet.net> wrote:
> I think this will do it.
>
> SELECT symbol,
> timestamp / 10000 as period,
> SUM(shares * price) / SUM(shares) as avgprice
> FROM StockTrades
> GROUP BY symbol, timestamp / 10000
>
> Roy Harvey
> Beacon Falls, CT
>
>
>
> On Mon, 24 Sep 2007 11:54:54 -0700, stevenleong...(a)gmail.com wrote:
> >I have given the following question. I need help on this.
>
> >Given a schema for a stock trades table that looks like:
>
> >*stocktrades (*
> >* symbol string, -- stock symbol*
> >* shares integer, -- number of shares traded*
> >* price float, -- price of the stock for this trade*
> >* timestamp integer -- timestamp in hhmmss 24-hour format*
> >* -- i.e., 000000 = midnight, 235959 = 11:59.59
> >PM*
> >*)*
>
> >Assuming the above table holds data for exactly one day of information
> >(midnight to 11:59.59 PM), write the query needed to summarize the
> >stock trades data into the following table that holds weighted average
> >price for each stock for every hourly period during that day:
>
> >*avgtrades (*
> >* symbol string, -- stock symbol*
> >* period integer, -- a one or two digit value representing*
> >* -- the hourly period of the trades*
> >* -- (0 = 000000-005959, 1 = 010000 - 015959...)*
> >* avgprice float -- weighted average price is the sum of*
> >* -- total dollars (price*shares) / total*
> >* -- number of shares traded in any interval*- Hide quoted text -
>
> - Show quoted text -


From: Roy Harvey (SQL Server MVP) on 24 Sep 2007 22:05
On Mon, 24 Sep 2007 16:01:11 -0700, stevenleongusa(a)gmail.com wrote:

>What is 10000 mean?

Shifting the decimal number over to get rid of the minutes and
seconds. 235959 / 10000 becomes 23.

Roy Harvey
Beacon Falls, CT
 | 
Pages: 1
Prev: JDBC Driver
Next: db mail