Top 3 services used for each day – SQL [closed]

The query below gives me top 3 services for 6th June 2021.

SELECT products_name AS product,
       COUNT(products_name) AS NumberOfBookings
FROM bookings
WHERE date = "06-06-2021"
GROUP BY products_name,
ORDER BY NumberOfBookings DESC
LIMIT 3;

I want to repeat this for each day of June.

How do I do that? I was thinking of iterating over a date range but that is not working. Is there a way to do it with some nested queries or group by?

Answer

You can use window functions with aggregation. Something like this:

SELECT b.*
FROM (SELECT date, products_name AS product,
             COUNT(*) AS NumberOfBookings,
             ROW_NUMBER() OVER (PARTITION BY date ORDER BY COUNT(*) DESC) as seqnum
      FROM bookings
      WHERE date >= '2021-06-01' AND date < '2021-07-01'
      GROUP BY products_name, date
     ) b
WHERE seqnum <= 3
ORDER BY Date, seqnum;

Note: This uses standard date formats for the date constants.