Search code examples
mysqlsqlgroup-bysql-order-by

SQL Moving window over two level of groupby

I have the following table of orders for users like the following:

    CREATE TABLE orders (
    order_id    UUID,
    user_id     UUID,
    date        date,
    order_type  integer
);

I want to write SQL to do the following:

  • For every order want to compute the number of orders the user has within a previous week (7 days).
    enter image description here

Write the following, but it counts the number of orders for each user but not for two levels of groupby.

SELECT order_id, user_id,
   COUNT(order_id) OVER(PARTITION BY user_id ORDER BY date ROWS BETWEEN 7 PRECEDING AND CURRENT ROW) as num_orders_7days

FROM orders
Solution

  • You should use RANGE clause instead of ROWS with the proper date intervals:

    SELECT order_id, user_id, date,
       COUNT(order_id) OVER (
         PARTITION BY user_id 
         ORDER BY date 
         RANGE BETWEEN INTERVAL 7 day PRECEDING AND INTERVAL 1 day PRECEDING
       ) as num_orders_7days
FROM orders;

    See the demo.