Hi there,

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


Edited by peter_budo: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.

9 Years
Discussion Span
Last Post by debasisdas
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.