Member Avatar for Jammie

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

Thanks

Edited by peter_budo because: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
  • 2 Contributors
  • 1 Reply
  • 82 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by debasisdas
Member Avatar for debasisdas

what are all those * in your create table code ?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.