1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

SQL query

Discussion in 'Oracle' started by CrazyGal, Sep 9, 2009.

  1. CrazyGal

    CrazyGal New Member

    Joined:
    May 24, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    0
    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

    Thanks,
    Tina
     
  2. CrazyGal

    CrazyGal New Member

    Joined:
    May 24, 2009
    Messages:
    21
    Likes Received:
    0
    Trophy Points:
    0
    The tables dint come out as expected. Could you tell me if I need to wrap the tables inside something?
     

Share This Page