MYSQL: For each week return videos that generated the most number of ads

Question

I have 2 tables, users and events:

**Users:**
usersid
age
geo_country
gender


**adbreaks:**
ts
videoid
ads_watched
geo_country
userid

My question is : For each week in January, return the ten videos that generated the most number of ads.

Am doing this code right?

    SELECT video_id, COUNT(ads_watched) AS ad_watched
    FROM (SELECT video_id, MAX(ads_watched) FROM adbreaks) adw
    WHERE WEEK(ts) < WEEK('2017-02-01')
    GROUP BY video_id
    ORDER BY ad_watched DESC
    LIMIT 10;

Any hint would be greatly appreciated.

Thanks,

Claudia

Could [this post](http://stackoverflow.com/q/2129693/2298301) be of some help? — Dhruv Saxena, Mar 17 '17 at 18:09

cur4so · Accepted Answer · 2017-03-17T19:40:23.183

"for each week of January" do you mean 1 query per week or entire month has to be presented in the result? If 1 query per week is expected in postgresql :

SELECT video_id, COUNT(ads_watched) AS ad_watched
    FROM adbreaks
    WHERE EXTRACT(WEEK FROM ts) = 1 AND EXTRACT(YEAR FROM ts) = 2017
    GROUP BY video_id
    ORDER BY ad_watched DESC
    LIMIT 10;

Then run the query with WHERE EXTRACT(WEEK FROM ts) = 2,3, and 4 it will give you the stat for January 2017

If you do not care about the year and want cumulative over all available data , drop the year constrain

In mysql:

SELECT video_id, COUNT(ads_watched) AS ad_watched
    FROM adbreaks
    WHERE WEEK(ts) = 1 AND YEAR(ts) = 2017
    GROUP BY video_id
    ORDER BY ad_watched DESC
    LIMIT 10;

Thanks, I believe the first one is correct - 'for each week of January' - it means one set of ads for each week of January. Claudia — Claudia, Mar 18 '17 at 04:54

MYSQL: For each week return videos that generated the most number of ads

1 Answers1