[SQL] How many unique users per day & Average # of unique users per day?

Data Science Interview QuestionsCategory: Data Science[SQL] How many unique users per day & Average # of unique users per day?
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?

1 Answers
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. 

Your Answer

11 + 15 =