CODEWITHSUNDEEP
sqlpostgresqldatetimecountgaps-and-islands
kizunairo
kizunairo

Reputation: 41

Counting number of rows leading to some number

The following is a snippet of my table... My table has a lot of more users and higher order_rank

enter image description here

I'm trying to get the number of visits leading up to that order_rank in postgres. So the result I'm trying to generate looks like...

enter image description here

Upvotes: 0

Views: 25

Answers (1)

GMB
GMB

Reputation: 222402

I would address this as a gaps-and-island problem, where each island ends with a visit. You want the end of each island, along with the count of preceding records in the same island.

You can define the group with a window count of non-null values that starts from the end of the table. Then, just use that information to count how many records belong to each group:

select *
from (
    select t.*, 
        count(*) over(partition by customer_id, grp) - 1 as number_of_visits
    from (
        select t.*,
            count(order_rank) over(partition by customer_id order by visit_time desc) grp
        from mytable t
    ) t
) t
where order_rank is not null

Demo on DB Fiddle:

customer_id | visit_time | txn_flag | order_rank | grp | number_of_visits
----------: | :--------- | -------: | ---------: | --: | ---------------:
        123 | 2020-01-04 |        1 |          1 |   3 |                3
        123 | 2020-01-06 |        1 |          2 |   2 |                1
        123 | 2020-01-11 |        1 |          3 |   1 |                4

Upvotes: 1

Related Questions