Search code examples
sqlinstancecounting

Sql instance number in new column

I have a table with userid in one column, purchase date in second column, and purchase item in the third. I ordered the table by purchase date and want to make a fourth column to record the count instance number of the user. See below for example.

enter image description here

Eventually I want to create a table that shows how much each user bought during the purchase. For example their first purchase they spent 10 second purchase 20 third purchase 30.

Solution

  • What you're looking for is the window function row_number.

    select
  *,
  row_number() over(
    partition by userid
    order by purchasedate
  ) as instance
from that_table