MockInterview Staff asked 6 years ago Table name: web_events
Columns:
session_id – primary key, there is one row per session
session_ts – timestamp of the beginning of session
user_id – user id
experiment_ids – list of experiment ids that the user is exposed to in a given session (separated by pipe delimiter) e.g ExpA | ExpB | ExpE
This table goes back several years and is current as of yesterday. Write SQL to:
1) For the month of August 2016, how many unique users per day were there? Output: date, user count
2) For the month of August 2016, what was the average number of unique users per day?
MockInterview Staff answered 6 years ago 1)
select date,count(distinct user_id) as usercount from
(
select user_id, cast(session_ts as date) as date from web_events
where month(session_ts) = 8 and year(session_ts) = 2016
) base
group by date
2)
Now this is just the average of the usercount field from query #1.