SQL table and data extraction

Question

I had never done SQL before, and I read about it. The book I'm reading has an exercise to get me started, I'm also looking for a website called W3School, and the book says that I'm trying to do the following:

Bidding , which has the following structure -

• trade_id: primary key
• timestamp: trade timestamp
• security: basic security (bought or sold in trade)
• quantity: basicquantity (positive means bought, negative indicates sold)
• price: the price of 1 security item for this transaction.

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')
    ;

, , 10 , 10%. .

, SQL, , . , , .

:

First_Trade Second_Trade    PRICE_DIFF
TRADE1      TRADE2          25

fiddle, . - , , .

Answer 1

, .

;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

:

1) - varchars. , , . datetime, int, float .. ( CTE @Jeroen-Mostert)

2) , (10- ) .

Answer 2

, . "", , .

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

, , ; CAST :

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

:

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
    ;

Answer 3

used a leading function to get the expected result. try the following:

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

This is based on the fact that the partition is performed on security. Feel free to comment or suggest corrections.