MySQL – List Top N Rentals for Each Month

aggregatecountgreatest-n-per-groupMySQL

I'm trying to work out the SQL to list the top 5 rentals for each month on MySQL or SQL Server. The database is the Sakila Sample database and the table is rental. Here are the first several rows in my database client (Navicat Premium):

I'd like the take the month from the rental_date field.
Each film has a unique inventory_id.

So far I've got this:

SELECT 
  MONTH( rental_date ) AS month,
    inventory_id,
    COUNT ( inventory_id ) AS no_of_rentals
FROM
    rental 
GROUP BY
    MONTH ( rental_date ),
    inventory_id 
HAVING
    COUNT ( inventory_id ) > 1 
ORDER BY
    MONTH ( rental_date ),
    inventory_id;

Unfortunately, that returns all films with more than one rental per month, which is not what I want.

I'd like to see something more like:

month   inventory_id  no_of_rentals
_________________________________
April   367           5
April   98            4
(3 more rows)
May     144           5
May     32            5
(3 more rows)
etc...

Bonus points if you can get the film title!

Thanks!

Rob

UPDATE:

Thanks to Dominique Boucher for the answer.

Here's my finished query:

WITH cte AS (
    SELECT 
      MONTH(rental_date) rental_month_no,
      DATENAME(month, rental_date) rental_month,
      inventory_id,
      COUNT( inventory_id ) no_of_rental,
      ROW_NUMBER( ) OVER ( PARTITION BY MONTH(rental_date) ORDER BY COUNT( inventory_id ) DESC) Rank
    FROM
      rental 
    GROUP BY
      MONTH(rental_date),
      DATENAME(month, rental_date),
      inventory_id 
    HAVING COUNT( inventory_id ) > 1
) 
SELECT
    rental_month,
    cte.inventory_id,
    title,
    no_of_rental,
    rank
FROM
  cte
  JOIN inventory ON cte.inventory_id = inventory.inventory_id
  JOIN film ON inventory.film_id = film.film_id
WHERE
  Rank < 6
ORDER BY rental_month_no, no_of_rental DESC;

I added code to fetch the titles and limited results to films that were rented more than once in a month.

Here are the results in Navicat:

Best Answer

Use pretty much the same script in a CTE but add the row number (partition by month). This will return all rows with a row number in front, reseting at 1 for every month. Then you can just issue your query with a "where rn <=5" and that will make it.

Here's an example just to give you a starting point:

create table #Demo (Rental_id int identity, rental_month int, inventoryId int, customer_id int)

insert into #Demo values (2,556,495),(2,236,495),(2,116,495),(2,556,495),(2,556,495),(2,116,495),(2,323,495),(2,56,495),(2,49,495),(2,49,495),
(2,116,495),(2,556,495),(2,453,495),(2,236,495),(2,34,495),(2,116,495),(2,556,495),(2,556,495),(2,24,495),(2,24,495)

insert into #Demo values (3,117,495),(3,236,495),(3,117,495),(3,236,495),(3,117,495),(3,116,495),(3,117,495),(3,236,495),(3,117,495),(3,236,495),
(3,116,495),(3,556,495),(3,453,495),(3,236,495),(3,34,495),(3,117,495),(3,556,495),(3,556,495),(3,117,495),(3,24,495),(3,34,495)

go


with cte as (
select rental_month, inventoryId, COUNT(inventoryId) "no_of_rental", ROW_NUMBER() over (PARTITION by rental_month order by count(inventoryId)) "RN"
from #Demo
group by rental_month, inventoryId
)
select * from cte where RN <6;


drop table #Demo

Related Solutions

Mysql – Getting the top count of records from a table for each month and user

Due to lack of windows functions and cte, mysql version is quite verbose, but I hope it will give you desired results in reasonable time (I know it's not fast at all):

SELECT a.*, c.*
FROM 
(
   SELECT yr,mn, MAX(translations_per_month) as max_user_translations
   FROM
   (
     SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id, 
     COUNT(1) as translations_per_month
     FROM `gp_translations` t1
     INNER JOIN gp_users u ON 
      (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
     GROUP BY  YEAR(`date_added`), MONTH(`date_added`),user_id
    )b
    GROUP BY yr,mn
)a
INNER JOIN 
(
   SELECT  YEAR(`date_added`) as yr, MONTH(`date_added`) as mn, user_id, display_name,
   COUNT(1) as translations_per_month
   FROM `gp_translations` t1
   INNER JOIN gp_users u ON (u.id = t1.user_id AND u.STATUS='current' OR u.STATUS='old')
   GROUP BY YEAR(`date_added`), MONTH(`date_added`),user_id, display_name
)c ON  (c.yr = a.yr and c.mn = a.mn and c.translations_per_month = a.max_user_translations)

Also, I made a guess that status column is in gb_users table ; if not, the query needs to be modified a bit.

Sql-server – SELECT TOP 1 DATEDIFF for each hour group

Assuming this is SQL Server, you can truncate datetime values to hours using this technique:

DATEADD(HOUR, DATEDIFF(HOUR, 0, @YourDateTimeValue), 0)

Using that as a grouping criterion, you can then use the FIRST_VALUE analytic function (assuming you are on SQL Server 2012 or later version) to get the first DATEDIFF(MINUTE, Created, Updated) per group:

SELECT DISTINCT
  x.CreatedHour,
  DiffMinutes = FIRST_VALUE(DATEDIFF(MINUTE, t.Created, t.Updated))
                OVER (PARTITION BY x.CreatedHour
                      ORDER BY t.Created ASC
                      ROWS UNBOUNDED PRECEDING)
FROM
  dbo.YourTable AS t
  CROSS APPLY
  (
    SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, t.Created), 0)
  ) AS x (CreatedHour)
;

Since the FIRST_VALUE function would return the result for every row of your table, it would return identical values for entries created in the same hour, thus giving you a lot of duplicates. It is to suppress those duplicates that the above query is using DISTINCT.

Best Answer

Related Solutions

Mysql – Getting the top count of records from a table for each month and user

Sql-server – SELECT TOP 1 DATEDIFF for each hour group

Related Question