Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

View in another Language

Sponsored [Advertise Here]
Don't have a blog yet?
beyondrelational.com
Start writing a blog at beyondrelational.com, get recognized and build your brand

Izenda Reports
izenda.com
Ad Hoc reporting for ASP.NET that actually works

Syndicate your blog!
beyondrelational.com
Syndicate your blog with us to get wider reach into the technology community. Click here to get started.

TSQL Challenge 57 - Identify rows that match the rollup trade data

This challenge is related to calculations used in stock markets and the original idea was found in one of the SQL forums.

Your job is to scan the trades data and identify combination of trades that match a given rollup data. The sample input data for this puzzle is given below.

Want to make SQL effortless?
With code-completion, SQL reformatting, script summaries and more, SQL Prompt 5 makes writing, editing, and exploring SQL effortless. Download a 14-day free trial now.

Sample Data

Trades Table
TradeId  Quantity  Price
-------  --------  -----
	  1	       10  20.00
	  2		   10   5.00
	  3	       25   7.50
	  4	       10  10.00
	  5	        2   2.00
      6	       10   7.50 

RollupData Table

ID  Quantity  Price
--  --------  ----- 
 1		  30   7.50
 2	      12   4.50

The 'Trades' table contains information about various trades available. The table 'RollupData' contains the rollup information. Your job is to take each row in the RollupData table and idenfity the combination of trades from the 'Trades' table. The rollup data is calculated as "SUM of Quantity * price divided by total number of quantities involved in the operation".

For example, a rollup operation involving trade 1 and 2 would produce the following rollup value. Price: (10 * 20 + 10 * 5) / (10 + 10) = 12.5, Quantity (10 + 10) = 20. Similarly, a rollup operation involving trade 3, 4 and 5 would produce the following rollup data. Price: (25 * 7.5 + 10 * 10 + 2 * 2) / (25 + 10 + 2) = 7.88, Quantity (25 + 10 + 2) = 37. Your job is to identify the trades that produce the rollup data of each row given in the 'RollupData' table.

Expected Results

ID Trades
-- -------
 1 2, 4, 6
 2 2, 5

Rules

  1. The 'Trades' column of the expected results should show the trade numbers in ascending order.
  2. A trade can be used only once within a rollup operation.
  3. It is legal to use the same trade number in multiple rollup operations.
  4. If there are no valid trades, the output column should show NULL.
  5. While calculating rollup value, the result is always rounded to two decimals.
  6. Sample data will be such that only one possible solution can be created for each row of RollupData Table.
  7. All prices and quantities are non-negative.

Sample Script

Use the TSQL Script given below to generate the source tables and fill them with sample data.

IF OBJECT_ID('TC57_Trades', 'U') IS NOT NULL BEGIN
	DROP TABLE TC57_Trades
END
GO

CREATE TABLE TC57_Trades(
	[TradeId] INT IDENTITY PRIMARY KEY,
	[Quantity] INT,
	[Price] DECIMAL(6,2)
)
GO

INSERT INTO TC57_Trades(Quantity,Price)
SELECT 10, 20.0 UNION ALL
SELECT 10, 5.0 UNION ALL
SELECT 25, 7.5 UNION ALL
SELECT 10, 10.0 UNION ALL
SELECT 2, 2.0 UNION ALL
SELECT 10, 7.5 

SELECT * FROM TC57_Trades
GO

IF OBJECT_ID('TC57_RollupData', 'U') IS NOT NULL BEGIN
	DROP TABLE TC57_RollupData
END
GO

CREATE TABLE TC57_RollupData (
	[ID] INT IDENTITY PRIMARY KEY,
	[Quantity] INT,
	[Price] DECIMAL(6,2) 
)
GO

INSERT INTO TC57_RollupData (Quantity, Price) 
SELECT 30, 7.5 UNION ALL
SELECT 12, 4.5

SELECT * FROM TC57_RollupData

Restrictions

  1. The solution should be a single query that starts with a "SELECT" or “;WITH”

Notes

  1. Read the Submission Guidelines and make sure that your solution follows them.
  2. If you would like to use a Tally Table, you can use the script given here. Your solution should not include the script to create and populate the tally table. You can assume that the tally table will be available in the database where the evaluation team will run your Code.
SQL Source Control - now with static data support
"By allowing static data to be versioned, SQL Source Control now becomes a complete source of truth for the application's data layer. I'm in." Troy Hunt, Software Architect.
More information and a free trial.

Jacob Sebastian
1 · 100.00% · 32235
Submit a Solution
View Solutions
View All Challenges Previous Challenge Next Challenge
2
Liked
14
Comments
43
Solutions



Submit

14   Comments   

Subscribe to Notifications
  • "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
    
    commented on May 30 2011 1:13AM  .  Report Abuse This post is not formatted correctly
    Leszek Gniadkowski
    8 · 18% · 5729
  • 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."

    commented on May 30 2011 5:18PM  .  Report Abuse This post is not formatted correctly
    Slobodan Bogunovic
    72 · 2% · 777
  • Right. I missed that.

    commented on May 31 2011 12:28AM  .  Report Abuse This post is not formatted correctly
    Leszek Gniadkowski
    8 · 18% · 5729
  • Why does no one wins?

    commented on Jun 6 2011 11:27AM  .  Report Abuse This post is not formatted correctly
    Adrian Facio
    471 · 0% · 82
  • 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.
    commented on Jun 9 2011 7:26AM  .  Report Abuse This post is not formatted correctly
    Maciej Chrzanowski
    92 · 2% · 594
  • This challenge is tough, but very interesting. Am still breaking my head finding the logic

    commented on Jun 15 2011 6:26AM  .  Report Abuse This post is not formatted correctly
    Dinesh
    647 · 0% · 53
  • (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.

    commented on Jun 15 2011 2:57PM  .  Report Abuse This post is not formatted correctly
    MilenBlagojevic
    3429 · 0% · 1
  • 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"?:

    commented on Jun 16 2011 11:34AM  .  Report Abuse This post is not formatted correctly
    Adrian Facio
    471 · 0% · 82
  • 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.

    commented on Jun 16 2011 11:43AM  .  Report Abuse This post is not formatted correctly
    Adrian Facio
    471 · 0% · 82
  • 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?

    commented on Jul 22 2011 4:31AM  .  Report Abuse This post is not formatted correctly
    Stefan_G
    22 · 8% · 2734
Previous 1 | 2 Next

Your Comment


Sign Up or Login to post a comment.


Managed Windows Shared Hosting by OrcsWeb

Copyright © Rivera Informatic Private Ltd.