SQL Query to Find Largest "Drawdown" in Table of Financial Transactions

Question

I have a table with the following columns:

id int(10)
winner int(10)
profit double
created datetime

I'm trying to create a query that returns the largest drawdown, or "peak to trough decline" in terms of the profit column. (More info on drawdown here.)

Ideally it would return the value for created for the beginning and end of the drawdown and the depth in profit from beginning to end.

Edit: The profit column is not cumulative, but the query needs to look for cumulative drawdown. A drawdown can have rises, but once the cumulative profit reaches a new high, the drawdown is over. Here's a line graph that shows it. The X distance between the red dot and the green dot is the maximum drawdown.

Answer 1

I'm not a mySQL wizard, so forgive me if I've made an error. However, if there are no other join criteria, and it's the whole table, then I think this could work for you:

select a.created as starttime
     , b.created as endtime
     , a.profit as startprofit
     , b.profit as endprofit
     , b.profit - a.profit as depth
  from your_table a
  join your_table b
    on a.created < b.created
 order by b.profit - a.profit
 limit 1

If there are other join criteria, and you expect several results by id or by winner, or by something else joined on, then I would try something like the following. However, don't forget that you'll have to correlate the subquery in the where clause. So, if you're doing by id, you'll need a where x.is = a.id in the subquery.

select a.created as starttime
     , b.created as endtime
     , a.profit as startprofit
     , b.profit as endprofit
     , b.profit - a.profit as depth
  from your_table a
  join your_table b
    on a.created < b.created
       ...other join criteria...
 where b.profit - a.profit = (select min(y.profit - x.profit)
                                from your_table x
                                join your_table y
                                  on x.created < y.created
                                     ...other join criteria...
                                     ...where clause to correlate subquery...)

Answer 2

I don't really understand the requirements for drawdown after reading that link. If it means, "show the largest uninterruped decline in value", where uninterrupted means there were was no temporary rally during the downfall, then something like this could work:

select top 1 p1.Created as Created1, p2.Created as Created2, p1.profit as p1, p2.profit as p2, p1.profit - p2.profit as Downfall
from profit p1
inner join profit p2 on p1.profit > p2.profit 
    and p2.Created > p1.Created
    and not exists (
     select 1 
     from profit p1a
     inner join profit p2a on p1a.Created < p2a.Created
      and p1a.profit < p2a.profit
     where p1a.Created >= p1.Created
      and p2a.Created <= p2.Created
    )
order by Downfall desc