CODEWITHSUNDEEP
mysql
jcampbell1
jcampbell1

Reputation: 4529

SQL Query to find if most recent records are all of the same type

I have a transaction table with a schema like

id | order_id | response | amount
1  | 2        |'payment' | 1000
2  | 5        |'declined'| 0
3  | 5        |'declined'| 0
4  | 5        |'payment' | 500
5  | 5        |'declined'| 0
6  | 11       |'declined'| 0
7  | 11       |'declined'| 0
9  | 11       |'declined'| 0

What I wand to do is find all orders, where the three most recent transactions for that order are 'declined'. Assume the higher the id, the more recent the transaction (or you can assume there is a created_at column).

In the above case, the only order_id that should be returned is 11 because while order_id 5 has 3 declined transactions, the most recent 3 transaction are D P D

Is there a clean way to do this with pure sql that runs in a reasonable about of time (assuming ~50M rows).

Upvotes: 1

Views: 120

Answers (3)

Strawberry
Strawberry

Reputation: 33945

Here's one way...

SELECT DISTINCT a.order_id
           FROM 
              ( SELECT x.*
                     , COUNT(*) rank 
                  FROM my_table x 
                  JOIN my_table y 
                    ON y.order_id = x.order_id 
                   AND y.id >= x.id 
                 GROUP 
                    BY id HAVING COUNT(*) <= 3
              ) a
           LEFT
           JOIN
              ( SELECT x.*
                     , COUNT(*) rank 
                  FROM my_table x 
                  JOIN my_table y 
                    ON y.order_id = x.order_id 
                   AND y.id >= x.id 
                 GROUP 
                    BY id HAVING COUNT(*) <= 3
              ) b
             ON b.order_id = a.order_id
            AND b.response <> 'declined'
          WHERE b.id IS NULL;

Fiddle of same: http://www.sqlfiddle.com/#!2/386aa3/1

Just a thought... does this work (untested)...

SELECT DISTINCT x.order_id
  FROM my_table x
  JOIN my_table y
    ON y.order_id = x.order_id
   AND y.id >= x.id
 GROUP
    BY x.id
HAVING COUNT(*) = 3 
   AND COUNT(*) = SUM(CASE WHEN y.response = 'declined' THEN 1 ELSE 0 END);

Upvotes: 0

Alex Jurado - Bitendian
Alex Jurado - Bitendian

Reputation: 1027

Assuming the higher id the more recent:

SELECT t0.order_id 
FROM transaction t0 
JOIN transaction t1 ON
   ((t1.response=t0.response) AND (t1.order_id=t0.order_id) AND 
   t1.id=(SELECT MAX(id) FROM transaction WHERE id<t0.id and t0.order_id=order_id)) 
JOIN transaction t2 ON 
   ((t2.response=t0.response) AND (t2.order_id=t0.order_id) AND 
   t2.id=(SELECT MAX(id) FROM transaction WHERE id<t1.id AND t0.order_id=order_id)) 
WHERE t0.response='declined' AND 
   t0.id=(SELECT MAX(id) FROM transaction WHERE order_id=t0.order_id);

Upvotes: 1

a1ex07
a1ex07

Reputation: 37382

Not a fast solution at all, but it should give you what you want (I assume recent transaction is transaction with higher value of id column) :

SELECT * FROM 
    (
    SELECT  *,
     (
        SELECT COUNT(1) 
        FROM `transaction` a WHERE a.order_id = b.order_id AND
        a.id >= b.id 
     )as num
    FROM `transaction`b
    ) a WHERE num =3
    AND NOT EXISTS
      (
       SELECT NULL FROM `transaction` b where response<>'declined' 
       and b.order_id = a.order_id and b.id >=a.id
      )

Upvotes: 1

Related Questions