How to write the SQL query to calculate Average Daily Ad Impressions per User?

Question

This is an example table for reference:

Suppose we’re investigating the average number of daily ad impressions per user. For example, if 100 users had 10 ad impressions each two days ago and 50 different users had 40 ad impressions each yesterday than the average daily ad impressions per user would be 20 across those two days:

                       2 days ago Yesterday
Users                      100          50
Ad Impressions per User     10          40
Ad Impressions per Day     1000        2000

Average Daily Ad Impressions per User ( (100 * 10 + 50 * 40) / 150) = 20

Here's the question: Now suppose you have an impressions table where each row represents impression details. The columns are as follows:

country string (the country the user is from)

date (the date the impression happened on)

user_id int (the id of the user)

impressions int (impression count)

1)Average daily ad impressions per user for only US users

My answer: NOT WORKING- I don't think the formula is right in my calculation

SELECT (b.all_users) from(
SELECT (imp_date),(SUM(impressions)*COUNT(user_id))all_users FROM jobs
WHERE country='US'
GROUP BY imp_date
) AS b;

2)Weekly average impressions per user for only UK users 3)Monthly running average of impressions per user

Answer 1

There's some information missing to answer properly, but I'm still gonna try to answer...

For your daily request, you need:

• grouping your result by day GROUP BY imp_date
• With this group, you can make a sum to have the print total SUM(imp_count)
• the number of user who have done a print during this day COUNT(DISTINCT(user_id))

Now you can have your average daily print SUM(imp_count) / COUNT(DISTINCT(user_id)) limited to US users WHERE country = 'US'

All that remains is to adjust the other requests according to the desired period.

---

You can show a litle demo on dbfiddle

-- DAILY
SELECT imp_date
    ,SUM(imp_count) AS imp_this_day
    ,COUNT(DISTINCT(user_id)) AS users_this_day
    ,( SUM(imp_count) / COUNT(DISTINCT(user_id)) ) AS imp_per_user
FROM   jobs
WHERE  country = 'US'
GROUP  BY imp_date;  

+-------------+---------------+-----------------+--------------+
|  imp_date   | imp_this_day  | users_this_day  | imp_per_user |
+-------------+---------------+-----------------+--------------+
| 2020-01-03  |            1  |              1  |       1.0000 |
| 2020-01-06  |            5  |              1  |       5.0000 |
| 2020-01-13  |           22  |              3  |       7.3333 |
|        ...  |          ...  |            ...  |          ... |
+-------------+---------------+-----------------+--------------+

-- BETWEEN TWO DATE
SELECT SUM(imp_count) AS imp_this_day
    ,COUNT(DISTINCT(user_id)) AS users_this_period
    ,( SUM(imp_count) / COUNT(DISTINCT(user_id)) ) AS imp_per_user
FROM   jobs
WHERE  country = 'US'
    and imp_date>="2020-01-14"
    and imp_date<="2020-01-15";


+------------------+--------------------+--------------+
| imp_this_period  | users_this_period  | imp_per_user |
+------------------+--------------------+--------------+
|              13  |                 2  |       6.5000 |
+------------------+--------------------+--------------+

-- WEEKLY
SELECT WEEK(ANY_VALUE(imp_date)) as week_num
        ,Sum(imp_count) AS imp_this_week
        ,Count(DISTINCT(user_id)) AS users_this_week
        ,( Sum(imp_count) / Count(DISTINCT(user_id)) ) AS imp_per_user
FROM   jobs
WHERE  country = 'US'
GROUP BY week_num; 


+-----------+----------------+------------------+--------------+
| week_num  | imp_this_week  | users_this_week  | imp_per_user |
+-----------+----------------+------------------+--------------+
|        0  |             1  |               1  |       1.0000 |
|        1  |             5  |               1  |       5.0000 |
|        2  |            40  |               4  |      10.0000 |
+-----------+----------------+------------------+--------------+

-- MONTHLY
SELECT MONTH(ANY_VALUE(imp_date)) as month_num
        ,Sum(imp_count) AS imp_this_month
        ,Count(DISTINCT(user_id)) AS users_this_month
        ,( Sum(imp_count) / Count(DISTINCT(user_id)) ) AS imp_per_user
FROM   jobs
WHERE  country = 'US'
GROUP BY month_num; 


+------------+-----------------+-------------------+--------------+
| month_num  | imp_this_month  | users_this_month  | imp_per_user |
+------------+-----------------+-------------------+--------------+
|         1  |             46  |                4  |      11.5000 |
+------------+-----------------+-------------------+--------------+