Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/forums/oracle-forum/)
-   -   SQL query (http://www.go4expert.com/forums/sql-query-t19366/)

CrazyGal 10Sep2009 02:01

SQL query
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

For example, if a user's activity can be summarized as:

user id product_id categ_id date 1 2 1 1/1/2009 1 3 1 1/1/2009 1 4 2 1/3/2009 1 5 2 1/3/2009 1 6 3 1/3/2009 1 7 4 1/10/2009 1 8 2 1/11/2009

then the result set should show

user id prev categ next categ days lag 1 1 2 2 1 2 3 0 1 3 4 7 1 4 2 1/1/1900

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


CrazyGal 10Sep2009 02:08

Re: SQL query
The tables dint come out as expected. Could you tell me if I need to wrap the tables inside something?

All times are GMT +5.5. The time now is 21:00.