How to delete records of orders that is canceled within 5 minutes in database?

I have a record of users’ purchasing behavior. However, it is long and includes a lot of redundant data. I want to delete orders that purchased and deleted within 5 min

My query so far: –TABLE 3 COD

select z.user_id,
       z.date,
       z.actions,
       x.name,
       x.email,
       x.address
       sum(z.debit) over (partition by z.seller_id order by z.created_at) - 
          sum(z.credit) over (partition by z.seller_id order by z.created_at) 
as balance
from table_1 z

left join

table_2 x

on z.seller_id = x.uid
order by seller_id, created_at

For simplicity, i got this result

user    actions        credit debit  balance     date
 1    do_action_A      5000    0      5000      2020-01-01 1:00:00     #no need these 2
 1    cancel_A          0     5000      0       2020-01-01 1:03:00     #in result
 1    do_action_A      5000    0      5000      2020-01-01 1:10:00
 1    do_action_b      3000    0      8000      2020-01-01 1:20:00
 1    do_action_c       0     7000    1000      2020-01-01 1:30:00

 2    do_action_A      5000    0      5000      2020-01-01 1:00:00
 2    do_action_B      3000    0      8000      2020-01-01 1:10:00

We know that users can only cancel their orders within 5 minutes, unfortunately, there is a lot of cancels. I need to make this data table simple and short so as to track and visualize it easily.

Here is my expectataion:

user    actions        credit debit  balance     date

 1    do_action_A      5000    0      5000      2020-01-01 1:10:00
 1    do_action_b      3000    0      8000      2020-01-01 1:20:00
 1    do_action_c       0     7000    1000      2020-01-01 1:30:00

 2    do_action_A      5000    0      5000      2020-01-01 1:00:00
 2    do_action_B      3000    0      8000      2020-01-01 1:10:00

Answer

You can try using lead()

select * from
(
select z.user_id,z.date,z.actions,x.name,
       x.email,x.address,debtit, credit, balance,
lead(z.actions) over(parition by z.user_id order by z.created_at) as next_action
from table_1 z left join table_2 x
on z.seller_id = x.uid
)A where next_action not like '%cancel%' and actions not like '%cancel%'

Advertisement

Answer