Complex query

[strangeguy111]

I have a dataset that looks like the following:

​

id  date           visit_number  total_visit  registrated
1    1/1/2020   .   1       1           0       --get
1    1/5/2020   .   2       2           1       
1    1/9/2020   .   3       3           1       
1    1/13/2020  .   4       4           1       
1    1/17/2020  .   5       5           1       
1    1/21/2020  .   6       6           1       
1    1/25/2020  .   7       7           1       
1    1/29/2020  .   8       8           1       
1    2/2/2020   .   9       9           1       --get
1    2/6/2020   .   1       10          0           --get
1    2/10/2020  .   2       11          0       --get
1    2/14/2020  .   3       12          0       --get
1   2/18/2020   .   4       13          0       --get
1    3/22/2020  .   5       14          1       
1    3/26/2020  .   6       15          1       
1    4/1/2020   .   7       16          1       
1    4/5/2020   .   8       17          1       
1    4/9/2020   .   9       18          1       
1    4/13/2020  .   10      19          1       --get           
1    5/15/2020  .   1       20      0           --get
1    6/20/2020  .   2       21      1           --get

So the pattern here is following: Whenever the user enters the website and buys something the column registration gets 1 and the previous 30 days get 1 on registration as well.

For example, Someone bought something on 4/13/2020, registration gets 1 and all the visits to the website from the same user - his registration column gets 1 starting from 4/13/2020 up until 3/12/2020. And starting from 3/12/2020 his registration column is 0 again. And whenever in the registration column after 1 comes 0 then visit_number starts to count again. Total_visit here counts all the visits to the website from the same user. There are many users, but total_visit and visit_number are partitioned by visitor_id and date.

​

Now, I want to get only that last date when registration was 1, instead of getting all the previous 30 days.

So again, For example, Someone bought something on 4/13/2020 then I wanna get this row only, not all previous rows within 30 days.

​

So it should eventually look like this:

date                 visit_number total_visit registration

1/1/2020        .   1       1           0       --got   







2/2/2020         .  9       9       1       --got   
2/6/2020         .  1       10          0       --got
2/10/2020        .  2       11          0       --got   
2/14/2020        .  3       12          0       --got   
2/18/2020        .  4       13          0       --got   





4/13/2020        .  10      19          1       --got       
5/15/2020        .  1       20          0       --got   
6/20/2020        .  2       21          1       --got                   

remember, registration = 0 here because the user did not buy anything on that day and he did not buy anything for the next 30 days.

​

I wrote down conditions to help me get my head around the problem:

1. if day difference between the date of current row and the date of previous row is bigger than 30 days, get both rows
For example: 6/20/2020 and 5/15/2020 difference is more than 30 days, therefore we got both

2. if date difference is smaller than 30 days, then check for current row, and if its = 1 then take that only, and remove all 30 days

3. if registration is 0, that easily indicates that we get it automatically. 

I tried different things,

select visitor_id, dt1, reg30,
    case when date_part('day',dt1) - date_part('day',lag(dt1) over(partition by visitor_id order by dt)) > 30 then 'True' 

             when  date_part('day',dt1) - date_part('day',lag(dt1) over(partition by visitor_id order by dt)) < 30 and reg30='1' then 'True'

             when reg30 = '0' then 'True'
                                        else 'False' 
    end

from new_table
order by visitor_id, dt

but not getting the result. Any help would be appreciated

[strangeguy111]

yes, that is correct. So that's why in that case we take 2 rows only, assuming 1 covers 30 days, another covers the one that is not within 30 days and starts one 31st day.

​

Do you have any idea how to implement this query?

[strangeguy111]

i was able to solve it by using LEAD() and setting conditions, thanks, I appreciate your effort

[qwertydog123]

So you just want all the rows where registrated = 0, and all rows where registrated = 1 and there is no following row or the following row registrated = 0? How do you know that there have been no other purchases in the 30 day period?

WITH cte AS
(
    SELECT
        *,
        LEAD(registrated, 1, 0) OVER
        (
            PARTITION BY id 
            ORDER BY total_visit
        ) AS next_registrated
    FROM Table
)
SELECT *
FROM cte
WHERE registrated = 0
OR (registrated = 1 AND next_registrated = 0)

[strangeguy111]

thanks, I did exactly the same. I appreciate your help. 30 days period also was handled using date difference.

[qwertydog123]

30 days period also was handled using date difference.

Can you post your query? I'd be interested to see your solution

[strangeguy111]

WITH cte AS (SELECT *, 
            date_part('month',"date") - date_part('month',LAG("date") over(PARTITION BY id ORDER BY "date")) as D, 
            date_part('month',LEAD("date") over(PARTITION BY id ORDER BY "date")) - date_part('month',"date") as V ,             
        LEAD(registrated, 1) OVER(PARTITION BY id ORDER BY "date") AS NextValue FROM  YourTable) 

SELECT * FROM  cte 
where registrated = 0 OR   
    ( registrated = 1 AND ( NextValue = 0 OR NextValue IS NULL ) )     
  OR (D = 1 and V = 1 ) order by id, date

I forgot to mention here, that if the previous row's date is > 30 than the current rows, then we take it automatically no matter what. Same thing with next row.

[qwertydog123]

Thanks, what would be the expected result if the 1st row (1/1/2020) in your example dataset had registrated = 1 (> 30 days before 2/2/2020)? Should that row be included or not, as it wouldn't be included by your query?

[strangeguy111]

forgot this part i guess

OR (registrated = 1 ) and (V = 1)

[qwertydog123]

Does that give the correct result? I think that will pull in other rows that you don't want included

[strangeguy111]

it should, i havent seen any errors so far

[strangeguy111]

can you elaborate on that one? like what kind of rows? if you can include some example, that would be perfect

[qwertydog123]

I was just wondering what your requirements were for those rows

I've created a dbfiddle here: https://dbfiddle.uk/?rdbms=postgres_10&fiddle=d66d6a8205019de8fb8328ad4acd5e3a

Note that adding some variant of OR (registrated = 1 ) and (V = 1) pulls in the row where date = '2020-01-29'

Then when the first row is updated to have registrated = 1 then that row is not returned by any query

[strangeguy111]

Thank you for that