I'm trying to write a SQL query based on the following tables.
create table purch ( integer user_id, * integer product_id, * integer item_count, date purch_date * ); create table product_categ ( integer product_id, * integer category_id )
Could someone tell me how to find the lag between category switches. By this I mean if user 1 regularly purchased from category 1, but then after 3 days of inactivity returned to purchase from category 2, then the time lag in that category switch is 3 days.
I'm trying to do the SQL that creates a result set which contains user_id, prev_category, next_category, days_lag
I'll have to use window function and a self join on this inline view "Y", but I'm not able to figure out how to do that part. Could someone give me some pointers? I did see a lot of examples on window functions but most of them are simple and I'm not able to relate to this one.
I'm still working on this but if you could give me a starting point, that will be great.
(SELECT p.user_id, p.product_id, c.category_id, p.purch_date FROM purch p INNER JOIN product_categ c ON p.product_id = c.product_id) Y