-
"Your job is to identify the trades that produce the rollup data of each row given in the 'RollupData' table."
All trades of each row or only first trades of each row?
truncate table TC57_Trades
INSERT INTO TC57_Trades(Quantity,Price)
SELECT 10, 10.0 UNION ALL
SELECT 10, 20.0 UNION ALL
SELECT 19, 15.0 UNION ALL
SELECT 1, 15.0
truncate table TC57_RollupData
INSERT INTO TC57_RollupData(Quantity, Price)
SELECT 20, 15.0
What is expected result?
ID Trades
1 1,2
or
ID Trades
1 1,2
1 3,4
|
-
There can be only one solution for each row in rollupdata.
Rule 6.: "Sample data will be such that only one possible solution can be created for each row of RollupData Table."
|
|
|
|
|
-
Hi Challengers!
Anyone wants to share stats?
Here's mine for sample data:
(2 row(s) affected)
Table 'Worktable'. Scan count 3, logical reads 498, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TC57_Trades'. Scan count 2, logical reads 129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TC57_RollupData'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16 ms, elapsed time = 6 ms.
|
-
This challenge is tough, but very interesting. Am still breaking my head finding the logic
|
-
(2 row(s) affected)
Table 'Worktable'. Scan count 2, logical reads 369, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TC57_Trades'. Scan count 2, logical reads 129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TC57_RollupData'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 6 ms.
|
-
This are my stats
Table 'Worktable'. Scan count 2, logical reads 369, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TC57Trades'. Scan count 2, logical reads 129, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TC57RollupData'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
how can i get "SQL Server Execution Times"?:
|
-
scintillaspire:
I found the solution using recursive querys to produce combinations. Look you can check out my solution http://beyondrelational.com/puzzles/tsql/solutions/4654/view.aspx. I based it on this article :
http://sqlblogcasts.com/blogs/jorginho/archive/2008/01/08/using-recursive-ctes-to-compute-darts-combinations.aspx.
|
-
The number of combinations to test grows very rapidly as the number of trades grow. A brute-force solution will have to test 2^n solutions where n is the number of trades.
20 trades gives over one million combinations to test.
It seems reasonable to put a limit on the number of trades a solution should support. A large number of trades would just give extremely long execution times.
I suggest we add a rule that the maximum number of trades a solution will have to support is 19
What do you think?
|
|