CODEWITHSUNDEEP
sqlpostgresqlcount
paz10s
paz10s

Reputation: 71

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

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

Upvotes: 0

Views: 149

Answers (1)

Stefanov.sm
Stefanov.sm

Reputation: 13009

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.

Upvotes: 1

Related Questions