User Activity - SQL Query

Question

I need some help in writing an SQL query

Here are the data: Application usage data is kept in the following table: Table sessions: Id, userId, duration, data_timestamp

I am trying to write a query that selects userId and average session duration for each “Good User”

• Good user: a user with an average of at least 4 sessions in a week

I tried writing the following query but I am still missing lots of points

SELECt avg_sessions
FROM (
SELECT
    S1.userid,
    COUNT(distinct id) as num_sessions
FROM
    sessions
WHERE data_timestamp BETWEEN DATE_SUB(data_timestamp, INTERVAL 7 DAY)
GROUP BY userid) as cte

Example: for the following data: Sample Data

we get the following analysis: Analysis

and the final result is : Result

Answer 1

Here is a way to do this. I use a week_dt column to group together the dates. This is followed by selecting users who have had atleast 5 sessions during that week.

SELECT
     S1.userid,
     DATE_SUB(data_timestamp, INTERVAL WEEKDAY(data_timestamp) DAY) as week_dt,
     COUNT(distinct id)/7 as num_sessions
FROM
    sessions
GROUP BY userid
HAVING COUNT(distinct id)/7>=5