Btukfyl

I would like to get a list of all URLs from one table together with two ratings for each URL in second table. The problem is that one rating should be from current month (if it exists), the other rating should be from last month (also if it exists). Is that possible to achieve with MySQL? Both ratings should for each URL row be grouped into one cell.

Database would be populated with new values once or maybe multiple times per month.

Table structure and sample data:

urls 

id | url

1  | google.com

2  | apple.com

3  | bing.com



ratings 

id | rating | url_id | created_at 

1  | 10      | 1      | 2018-10-10

2  | 8       | 2      | 2018-10-10

3  | 11      | 1      | 2018-11-10

4  | 5       | 2      | 2018-11-10

5  | 6       | 3      | 2018-11-25

In the end my goal is to show a simple table of URLs and compare their ratings for each month.

url        | previous rating | current rating

google.com | 10              | 11 (rating has increased)

apple.com  | 8               | 5 (rating has decreased)

bing.com   | -               | 6

This is just a beginning of a query, I don't have idea how would I limit 2 ratings for each url as stated above?

SELECT 

    urls.id, 

    urls.url, 

    ratings.rating



FROM urls



INNER JOIN  ratings 

ON urls.id = ratings.url_id

You can use conditional aggregation to get the result you want. I have assumed you would like to use the average rating for each month for comparison purposes.

SELECT u.url, 

       AVG(CASE WHEN MONTH(r.created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH)

                 AND YEAR(r.created_at)  = YEAR(CURDATE() - INTERVAL 1 MONTH) THEN rating END) AS `previous rating`,

       AVG(CASE WHEN MONTH(r.created_at) = MONTH(CURDATE())

                 AND YEAR(r.created_at) = YEAR(CURDATE()) THEN rating END) AS `current rating`

FROM urls u

LEFT JOIN ratings r

ON r.url_id = u.id

GROUP BY u.url

ORDER BY `current rating` DESC

Output:

url         previous rating     current rating

google.com  10.0000             11.0000

bing.com                        6.0000

apple.com   8.0000              5.0000

Update

To only use the last rating from each month (as requested in comments) is a little more complex. It can be done with two JOINs to tables which contain the last rating for the previous and current months:

SELECT u.url,

       pr.rating AS `previous rating`,

       cr.rating AS `current rating`

FROM urls u

LEFT JOIN (SELECT url_id, rating

           FROM ratings r1

           WHERE created_at = (SELECT MAX(created_at)

                               FROM ratings r2

                               WHERE r2.url_id = r1.url_id AND

                                   MONTH(created_at) = MONTH(CURDATE() - INTERVAL 1 MONTH) AND

                                   YEAR(created_at)  = YEAR(CURDATE() - INTERVAL 1 MONTH)

                               )

           ) pr ON pr.url_id = u.id

LEFT JOIN (SELECT url_id, rating

           FROM ratings r1

           WHERE created_at = (SELECT MAX(created_at)

                               FROM ratings r2

                               WHERE r2.url_id = r1.url_id

                               )

           ) cr ON cr.url_id = u.id

ORDER BY `current rating` DESC

Updated demo on dbfiddle