I have this data frame : basically each row being a transaction carried out by one customer on a day. there are multiple transactions by same customer on same day and on different dates. I want to get a column for a customers number of previous visits.
id date purchase id1 date1 $10 id1 date1 $50 id1 date2 $30 id2 date1 $10 id2 date1 $10 id3 date3 $10
after adding visits column:
id date purchase visit id1 date1 $10 0 id1 date1 $50 0 id1 date2 $30 1 id2 date1 $10 0 id2 date2 $10 1 id2 date3 $10 2
I do this in pandas using factorize :
df.visits = 1 df.visits = df.groupby('id')['date'].transform(lambda x: pd.factorize(x))
I want to do it through SQL, what would the query be like ?