CODEWITHSUNDEEP
sqlsql-serversql-server-2008
dave
dave

Reputation: 135

SQL table and data extraction

I have never done SQL before and I been reading up on it. There is a exercise in the book i am reading to get me started, I am also looking up a website called W3School and the book is telling me to attempt the below;

Trades which has the following structure –

Consider the following table

 CREATE TABLE tbProduct
        ([TRADE_ID] varchar(8), [TIMESTAMP] varchar(8), [SECURITY] varchar(8), [QUANTITY] varchar(8), [PRICE] varchar(8))
    ;

    INSERT INTO tbProduct
        ([TRADE_ID], [TIMESTAMP], [SECURITY], [QUANTITY], [PRICE])
    VALUES
        ('TRADE1', '10:01:05', 'BP', '+100', '20'),
        ('TRADE2', '10:01:06', 'BP', '+20', '15'),
        ('TRADE3', '10:10:00', 'BP', '-100', '19'),
        ('TRADE4', '10:10:01', 'BP', '-100', '19')
    ;

In the book it is telling me to write a query to find all trades that happened in the range of 10 seconds and having prices differing by more than 10%. The result should also list the percentage of price difference between the 2 trades.

For a person who has not done SQL before, reading that has really confused me. They have also provided me the outcome but i am unsure on how they have come to this outcome.

Expected result:

First_Trade Second_Trade    PRICE_DIFF
TRADE1      TRADE2          25

I have created a fiddle if this help. If someone could show me how to get the expected result, it will help me understand the book exercise.

Thanks

Upvotes: 4

Views: 227

Answers (3)

Prabhat G
Prabhat G

Reputation: 3029

have used lead function to gain expected result. try this : 

select 
iq.trade_id as FIRST_TRADE, 
t1 as SECOND_TRADE, 
((price-t3)/price*100) as PRICE_DIFF
from
(
 Select trade_id, timestamp, security, quantity, cast(price as float) price,
        lead(trade_id) over (partition by security order by timestamp) t1
        ,lead(timestamp) over (partition by security order by timestamp) t2
        ,lead(cast(price as float)) over (partition by security order by timestamp) t3
 from tbProduct
) iq
where DATEDIFF(SECOND, iq.timestamp,iq.t2) between 0 and 10
and ((price-t3)/price*100) > 10

It is based on fact that partition is done over security. Feel free to comment or suggest corrections.

Upvotes: 1

JeffUK
JeffUK

Reputation: 4241

Slightly different approach to the other answer, but pretty much the same effect. I use 'Between' to find the date range rather than datediff. 

select 
    trade1.trade_ID as TRADE1, 
    trade2.trade_ID as TRADE2, 
    (cast(trade1.price as float)-cast(trade2.price as float))/cast(trade1.price as float)*100  as PRICE_DIFF_PERC 
from 
    tbProduct trade1
inner join 
    tbProduct trade2

on 
    trade2.timestamp between trade1.timestamp and dateadd(s,10,trade1.TIMESTAMP)
and trade1.TRADE_ID <> trade2.TRADE_ID

where (cast(trade1.price as float)-cast(trade2.price as float))/cast(trade1.price as float) >0.1

The schema could definitely be improved; removing the need for 'CAST's would make this a lot clearer:

CREATE TABLE tbProduct2
        ([TRADE_ID] varchar(8), [TIMESTAMP] datetime, [SECURITY] varchar(8), [QUANTITY] int, [PRICE] float)
    ;

Allows you to do:

select *,
    trade1.trade_ID as TRADE1, 
    trade2.trade_ID as TRADE2, 
    ((trade1.price-trade2.price)/trade1.price)*100  as PRICE_DIFF_PERC 
from 
    tbProduct2 trade1
inner join 
    tbProduct2 trade2

on 
    trade2.timestamp between trade1.timestamp and dateadd(s,10,trade1.TIMESTAMP)
and trade1.TRADE_ID <> trade2.TRADE_ID

where (trade1.price-trade2.price) /trade1.price >0.1
    ;

Upvotes: 1

strickt01
strickt01

Reputation: 4048

This will get the result you want. 

;with cast_cte
as
(
  select [TRADE_ID], cast([TIMESTAMP] as datetime) timestamp, [SECURITY], [QUANTITY], cast([PRICE] as float) as price
  from tbProduct
)
select t1.trade_id, t2.trade_id, datediff(ms, t1.timestamp, t2.timestamp) as milliseconds_diff,
((t1.price - t2.price) / t1.price) * 100 as price_diff
from cast_cte t1
inner join cast_cte t2
on datediff(ms, t1.timestamp, t2.timestamp) between 0 and 10000
and t1.trade_id <> t2.trade_id
where ((t1.price - t2.price) / t1.price) * 100 > 10
or ((t1.price - t2.price) / t1.price) * 100 < -10

However, there are a number of problems with the schema and general query parameters:

1) The columns are all varchars. This is very inefficient because they all need to be cast to their appropriate data types in order to get the results you desire. Use datetime, int, float etc. (I have used a CTE to clean up the query as per @Jeroen-Mostert's suggestion)

2) As the table gets larger this query will start performing very poorly as the predicate used (the 10 second timestamp) is not indexed properly.

Upvotes: 1

Related Questions