Click here to Skip to main content
15,878,945 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
We have an activity database that records user interaction to a website, storing a log that includes values such as `Time1`, `session_id` and `customer_id` e.g.

2022-05-12 08:00:00|11|1
    2022-05-12 08:20:00|11|1
    2022-05-12 08:30:01|11|1
    2022-05-12 08:14:00|22|2
    2022-05-12 08:18:00|22|2
    2022-05-12 08:16:00|33|1
    2022-05-12 08:50:00|33|1


I need to have two separate queries:

**Query #1:** I need to count sessions multiple times if they have a log of 30 minutes or more grouping them on sessions on daily basis.

For example: Initially count=0

* For session_id = 11, it starts at 08:00 and the last time with the same session_id is 08:30 -- count=1

* For session_id = 22 it starts at 08:14 and the last time with the same session is 08:14 -- still the count=1 since it was less than 30 min

I tried this query, but it didn't work

SQL
select 
        count(session_id)
    from
        table1 
    where 
        @datetime between Time1 and dateadd(minute, 30, Time1);


Expected result:

Time1        count(session_id)
2022-05-12         2


**Query #2:** it's an extension of the above query where I need the unique customers on daily basis whose sessions were 30 min or more.

For example: from the above table I will have two unique customers on May 8th

Expected result

Time1        count(distinct(customer_id))
2022-05-12         2



For the Time1 column, the input is in timestamp format when I show it in output I will group it on a basis.

What I have tried:

SQL
select 
        count(session_id)
    from
        table1 
    where 
        @datetime between Time1 and dateadd(minute, 30, Time1);
Posted
Updated 12-May-22 8:26am
v2
Comments
CHill60 12-May-22 15:13pm    
You need to describe the problem with more clarity. Based on the data you have posted session 22 starts at 08:14 but ends at 08:18 not 08:14. And this whole counting sessions thing - what is the relevance of the 30 minutes if you are counting session 11 as one and session 22 as one - do you mean anything of e.g. 40 minutes should be counted as two sessions? I.e. Up to 30 minutes is a session, 31 to 60 minutes is another session, 61 to 90 minutes is third etc etc?
Maciej Los 12-May-22 15:39pm    
Your requirements are not entirely clear...
[no name] 13-May-22 10:17am    
You talk about "May 8" then show data for May 12. Confused or just sloppy? Either way, that won't get you anywhere in this business.
CHill60 17-May-22 4:30am    
We have asked for more detail and some clarification of your problem. If you still want help you are going to have to help us to help you. Gerry Schmitz and I have pointed out some inaccuracies in your data and I have asked a specific question. Without this additional information we cannot even try to help you.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900