For each row with a week number value, how do I count the number of rows from the previous weeks?

Question

So I have this table where each row is an order, and the week value stands for the nth week of the year that the order was placed:

orderId	customerId	week
1	1	35
2	1	35
3	2	35
4	1	36
5	1	37
6	1	38

I want to be able to add 2 other columns: week-1_orders and prev-weeks_orders:

orderId	customerId	week	week-1_orders	prev-weeks_orders
1	1	35
2	1	35
3	2	35
4	1	36	2	2
5	1	37	1	3
6	1	38	1	4

week-1_orders gets the count of orders placed by the same customer within the week before the current row's week. So if we're looking at the row of orderId = 4 where week = 36, the same customer (customerId = 1) placed 2 orders in the previous week (week = 35). orderId = 3 is not counted since it is not placed by customerId = 1. Thus, week-1_orders = 2.

The same goes for prev-weeks_orders, except that it counts all the previous weeks instead of just 1 single week.

I was hoping to somehow be able to refer to the current row's value like this, although I can't seem to find a way:

SELECT COUNT(CASE WHEN orders."week" = curr_row."week" - 1 AND orders."customerId" = curr_row."customerId" THEN 1 ELSE NULL END) AS "week-1_orders",
    COUNT(CASE WHEN orders."week" <= curr_row."week" - 1 AND orders."customerId" = curr_row."customerId" THEN 1 ELSE NULL END) AS "prev-week_orders"
FROM orders

Stefanov.sm · Accepted Answer · 2022-10-07T13:14:29.110

Using scalar subqueries would be the easiest to read.

select orderid, customerid, week,
    (select count(*) from the_table where customerid = tx.customerid and week = tx.week - 1) "week-1 orders",
    (select count(*) from the_table where customerid = tx.customerid and week < tx.week) "prev-weeks orders"
from the_table as tx;

Or using a lateral join with a subquery

select orderid, customerid, week, l.*
from the_table as tx cross join lateral     
(
 select 
   count(*) filter (where customerid = tx.customerid and week = tx.week - 1) "week-1 orders",
   count(*) filter (where customerid = tx.customerid and week < tx.week) "prev-weeks orders"
 from the_table
) as l;

Demo

There might be a good solution using window functions too.

FIrst one you mentioned worked perfectly for me. Thanks! – paz10s Oct 11 '22 at 00:32 — paz10s, Oct 11 '22 at 00:32

For each row with a week number value, how do I count the number of rows from the previous weeks?

1 Answers1