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
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:
select
count(session_id)
from
table1
where
@datetime between Time1 and dateadd(minute, 30, Time1);