How to write a SQL query: display users who canceled an order twice within half an hour?

Question

SQL

There is a table orders (user_id, order_id, timestamp, status). The status has two states. It is necessary to display all customers who canceled the order twice within half an hour.
As I imagine, first select clients with a subquery:
SELECT user_id
FROM orders
GROUP BY user_id
HAVING COUNT(orders.status='cancel')=2,
then in an incomprehensible way from the status='cancel' event set an interval of 30 minutes and find a repeat on it 'cancel' values.
How to implement this interval?

Answer 1

The answer is on the surface. Since you know the starting point and the exact condition for including the user in the statistics, it is enough to use the INNER JOIN construct to find pairs of identical events in the time period, for example, like this:

SELECT DISTINCT
  orders.user_id
FROM
  orders
INNER JOIN
  orders AS canceledOrders
ON
  canceledOrders.user_id = orders.user_id
  AND canceledOrders.status = orders.status
  AND canceledOrder.order_id <> orders.order_id
  AND canceled.timestamp - orders.timestamp BETWEEN 0 AND 1800
WHERE
  orders.status = 'cancel'