---
# System prepended metadata

title: Follow up after home assignment (v1)

---

# Follow up after home assignment (v1)

Task 1:
```sql=BigQuery
select
    date,
    country,
    count(*) as `result`
from (
    select
        user_id,
        event_date as date,
        any_value(geo.country) as country
    from `zedge-android.events.all`
    where event_date between current_date - interval 2 day and current_date - interval 1 day
    group by user_id, date
)
group by date, country
order by date, count(*) desc
```

What is the runtime of the query expressed for $n$ events per day, $u$ users, $d$ days and $c$ countries?

What is the memory consumption?

Task 2:
A session is defined as a period of activity in the application. If there is a period longer than 5 minutes when the user does not do anything actively the session ends. The session time is the time between the first action to the last action of the session.

Write the algorithm calculating all session times of all users.

Example input:
| userId | minute timestamp of activity |
|-------|----|
| user1 | 1  |
| user1 | 2  |
| user2 | 3  |
| user1 | 7  |
| user1 | 9  |
| user1 | 13 |

Expected output:
[1, 6, 0]

Task 3:
We would like the users of our app to come back and use the app again. Especially our new users seeing the app for the first time.

We have a year of data about individual users, what content they have interacted with and if they came back on their second day. How would you use this data to achieve the goal of more users coming back on day 2?

What if you are limited only to what content you show?
