Calculate Video Call Usage Metrics by Country and Date

video_calls

+---------+-----------+------------+---------+----------+ | caller | recipient | ds | call_id | duration | +---------+-----------+------------+---------+----------+ | 123 | 456 | 2019-01-01 | 4325 | 864.4 | | 032 | 789 | 2019-01-01 | 9395 | 263.7 | | 456 | 032 | 2019-01-01 | 0879 | 22.0 | +---------+-----------+------------+---------+----------+

​

users

+---------+------------+---------+-------------+----------+------------+ | user_id | age_bucket | country | primary_os | dau_flag | ds | +---------+------------+---------+-------------+----------+------------+ | 123 | 25-34 | US | iOS | 1 | 2019-01-01 | | 456 | 35-44 | FR | Android | 1 | 2019-01-01 | | 789 | 18-24 | US | Android | 0 | 2019-01-01 | +---------+------------+---------+-------------+----------+------------+

Scenario

Video-calling platform wants usage KPIs by country and date.

Question

What percentage of users located in France made at least one video call yesterday? What is the average time spent on calls per daily active user (DAU) in the United States today?

Hints

Join call and user tables, filter by ds for yesterday/today, count distinct users, sum durations, divide for ratios or averages.