WITH CTE
AS
(
SELECT  ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY order_date DESC) AS RowNumber,
        customer_id,
        order_date,
        order_amt
FROM DailySalesTotals
)
SELECT  order1.customer_id,
        ' dropped purchase amount on ',
        order1.order_date
FROM CTE order1
    INNER JOIN CTE order2
        ON order2.customer_id = order1.customer_id
            --The second most recent order
            AND order2.rownumber = 2
WHERE
    --The most recent order
    order1.RowNumber = 1
    --Recent order amount lower than second most recent order amount
    AND order1.order_amt < order2.order_amt;

/*
Customer_id order_date order_amt
----------- ---------- ---------------------------------------
Adam        2004-05-08 278.46
Adam        2005-02-26 612.43
Adam        2005-08-13 452.65
Adam        2007-07-22 499.30
Adam        2008-11-25 799.89
*/

/*
Customer_id order_date order_amt
----------- ---------- ---------------------------------------
Adam        2004-05-08 278.46
Adam        2005-02-26 612.43
Adam        2005-08-13 452.65
Adam        2007-07-22 499.30
Adam        2008-11-25 799.89
*/

/*
--Joe's solution:
customer_id	(No column name)	        (No column name)
--------------------------------------------------------
Aaron       dropped purchase amount on  2008-08-07
Abel        dropped purchase amount on  2008-12-23
Abraham     dropped purchase amount on  2008-01-03
Aimee       dropped purchase amount on  2009-09-19

--My solution:
customer_id	(No column name)	        order_date
--------------------------------------------------------
Aaron       dropped purchase amount on  2008-08-07
Abel        dropped purchase amount on  2008-12-23
Abraham     dropped purchase amount on  2008-01-03
Aimee       dropped purchase amount on  2009-09-19
*/

/*--------------------------------------------------------------------------+
¦ The execution plan looks way over the top for this, but here goes anyway. ¦
¦ The idea is to assign row numbers to the base recordset, and match it     ¦
¦ to a second recordset which has a row number offset by +1.  Using a join  ¦
¦ to match row number to offset row number the order amounts from current   ¦
¦ row and previous row can be compared.                                     ¦
+--------------------------------------------------------------------------*/

select customer_id, ' dropped purchase amount on ', order_date
  from ( select customer_id,
                row_no = row_number() over ( partition by customer_id order by order_date ),
                order_date,
                max_order_date = max(order_date) over (partition by customer_id),
                order_amt
           from DailySalesTotals
       ) as dst,
       ( select prev_row_customer_id = customer_id,
                offset_row_no = row_number() over ( partition by customer_id order by order_date ) + 1,
                prev_row_order_amt = order_amt
           from DailySalesTotals
       ) as PreviousRow_DailySalesTotals
 where customer_id = prev_row_customer_id
   and row_no = offset_row_no
   and order_date = max_order_date
   and order_amt < prev_row_order_amt

Inflatable sailors never die, they gently deflate over time.

-- For each customer, number all records customer in descending order of order_date
;WITH NumberedDailySalesTotals AS
(
    SELECT
      customer_id, order_date, order_amt, 
      ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) rn
    FROM
      DailySalesTotals
)
-- For each customer, collect the date, amount of the record with the highest date, 
-- and the date and amount of the second highest date
, PivotDailySalesTotals AS
(
  SELECT
    customer_id, 
    MAX(CASE WHEN rn = 1 THEN order_date ELSE '1753/01/01' END) last_date,
    SUM(CASE WHEN rn = 1 THEN order_amt ELSE 0.0 END) last_amt, 
    MAX(CASE WHEN rn = 2 THEN order_date ELSE '1753/01/01' END) second_last_date,
    SUM(CASE WHEN rn = 2 THEN order_amt ELSE 0.0 END) second_last_amt
  FROM  
    NumberedDailySalesTotals dst
  GROUP BY
    customer_id
)
-- Filter out the records where last_amount is less then the second last amount
-- Requires only a single table scan and 12 logical reads
SELECT
  customer_id, ' dropped purchase amount on ', last_date
FROM 
  PivotDailySalesTotals
WHERE
  last_amt < second_last_amt

SELECT
    *,
    Row_Number() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS OrderSeq
INTO
    #T
FROM
    ##DailySalesTotals ;

ALTER TABLE #T
ADD 
    PRIMARY KEY (customer_id, order_date) ;

CREATE INDEX IDX_OrderSeq ON #T (OrderSeq, order_amt) ;

SELECT
    T2.customer_id, T2.order_date as 'dropped qty on'
FROM
    #T T1
INNER JOIN #T T2
    ON T1.customer_id = T2.customer_id
       AND T1.OrderSeq = 2
       AND T2.OrderSeq = 1
       AND T1.order_amt > T2.order_amt ;

SELECT
  t1.customer_id,
  ' dropped purchase amount on ',
  MAX(CASE WHEN t1.order_ordinal = 1 THEN t1.order_date END)
FROM(
	SELECT
	  DST.customer_id,
	  DST.order_date,
	  DST.order_amt,
	  RANK() OVER 
		(PARTITION BY DST.customer_id ORDER BY DST.order_date DESC) AS order_ordinal,
	  RANK() OVER 
		(PARTITION BY DST.customer_id ORDER BY DST.order_amt ASC) AS order_amt_ordinal
	FROM dbo.DailySalesTotals AS DST) t1
WHERE
  --Only review last n orders
  t1.order_ordinal <= 2
GROUP BY
  t1.customer_id
HAVING
  --Eliminate customers with fewer than n orders
  MAX(CASE WHEN t1.order_ordinal = 2 THEN t1.order_date END) IS NOT NULL
  
  --Compare most recent order amount with first order amount and return those where the purchase amount decreased
  --Change or add CASE statements to alter scenarios desired
  AND MAX(CASE WHEN t1.order_ordinal = 1 THEN t1.order_amt END) < MAX(CASE WHEN t1.order_ordinal = 2  THEN t1.order_amt END)
GO