Suppose you have a table with all orders of an e-commerce website, with one row per order. For every order, at this point in time, you want to find the number of upcoming orders made by this specific customer in a limited time window, let’s say the next 30 days.
SELECT
curr.user_id,
curr.order_id,
curr.created_at,
COUNT(DISTINCT next_30d.order_id) AS next_30d_orders
FROM orders AS curr
/* Join on same table, for each order, within a limited timeframe */
LEFT JOIN orders AS next_30d
ON curr.user_id = next_30d.user_id
AND next_30d.created_at > curr.created_at # Exclude current order time
AND next_30d.created_at <= curr.created_at + INTERVAL '30 days'
WHERE curr.created_at BETWEEN '2023-02-01' AND '2023-02-08'
GROUP BY 1,2,3
ORDER BY user_id, created_at