Claudia Claudia -4 years ago 68
MySQL Question

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

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

Answer Source

"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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download