MySQL sorting/grouping inside JOIN

Question

hi have the following schema

-- Accounts ----
[id]    name
----------------
20    BigCompany
25    SomePerson


-- Followers -------
[id   follower_id]
--------------------
20    25


-- Daily Metrics --------------------------------
[id    date ]       follower_count   media_count
-------------------------------------------------
25    2015-10-07    350              24
25    2015-10-13    500              27
25    2015-10-12    480              26

I would like a list of all followers of a particular account, returning their most up to date follower_count. I've tried JOINs, correlated subqueries etc but none are working for me.

Expected result for followers of BigCompany:

id    username    follower_count   media_count  'last_checked'
---------------------------------------------------------------
25    SomePerson  500              27            2015-10-13

Answer 1

Do some JOIN's, use NOT EXISTS to exclude older metrics:

select a1.id, a1.name, dm.follower_count, dm.media_count, dm.date as "last_checked"
from Accounts a1
  join Followers f on f.follower_id = a1.id
  join Accounts a2 on f.id = a2.id
  join DailyMetrics dm on dm.id = a1.id
where a2.name = 'BigCompany'
  and not exists (select 1 from DailyMetrics
                  where id = dm.id
                    and date > dm.date)