Hey guys, wondering how you would answer a question like this?

I’m pretty new to SQL and want to study up for a job interview coming up

Application usage data is kept in the
following table:

Table sessions: Id, userld, duration, data_timestamp

Write a query that selects userld and average session duration for each "Good User"

Good User- a user with an average of at least 3 sessions in a week

god i wish sequel wasnt so mind numbingly boring

window functions are really useful for this:

WITH good_users AS (
SELECT
s.userid
, DATE_PART('week', s.timestamp) AS week
, count(s.id) AS session_count
, AVG(session_count) OVER(PARTITION BY userid) AS avg_sessions_per_week
FROM
sessions AS s
GROUP BY
s.userid
, s.week
)
SELECT
DISTINCT gu.userid
, AVG(s.duration) OVER(PARTITION BY s.userid) AS avg_session_duration
FROM
good_users AS gu
LEFT OUTER JOIN sessions AS s ON (s.userid = gu.userid)
WHERE
gu.avg_sessions_per_week >= 3

could be easier ways to do it though idk

damn i just realised this thread was made a month ago

well i hope u figured it out