Transaction Trail Analysis :Compare Transaction Amounts Within Species by Ranking and Measuring Sequential Differences Intermediate

          with transaction_patterns as
  (select c.species,
          o.orderid as transactionid,
          o.totalamount
   from orders o
   join customers c on o.customerid = c.customerid)
select species,
       transactionid,
       totalamount,
       rank() over (partition by species
                    order by totalamount desc) as transactionrank,
                   totalamount - lag(totalamount) over (partition by species
                                                        order by totalamount desc) as differencefromprevious
from transaction_patterns;