count number of occurences before a certain date-CodePudding

base_table

  date      cust_id   review_score
2021-10-19  1              0
2021-08-06  1              7
2021-07-06  1              3
2021-04-06  1              4
2021-07-06  2              5
2021-04-06  2              6

Based on the table above, I would like to create a new column, how many reviews existed before the current review.

Expected output:

date      cust_id   review_score num_prior_reviews
2021-10-19  1              0              3 
2021-08-06  1              7              2
2021-07-06  1              3              1
2021-04-06  1              4              0
2021-07-06  2              5              1
2021-04-06  2              6              0

Explanation: On 19 Oct 2021, customer ID has 3 reviews before that date (in April, in July and in August). So far, I used row_number() over (partition by cust_id order by date DESC) as rank, but not sure how to progress from there.

row_number() over (partition by

CodePudding user response：

you can use window function count or row_number:

select * , count(*) over (partition by cust_id order by date) -1 num_prior_reviews
from tablename

CodePudding user response：

You pretty much have it...

    select date, cust_id, review_score, 
    row_number() over (partition by cust_id order by date asc) - 1 as num_prior_reviews
    from reviews
    order by cust_id, date desc

CodePudding user response：

Just in this case, you can use self join.


SELECT k.*,COUNT(*) FROM dbo.kitap k 
INNER JOIN dbo.kitap k1 
ON k.cust_id = k1.cust_id 
WHERE k.date>=k1.date
GROUP BY k.date,k.cust_id,k.review_score