How to use MYSQL to include missing rows in final table with default 0 values for all columns?

Question

This question is part of a bigger mySQL query I have. So I have a table of 'playerIds', 'dates', 'scores' and 'problems'. It's Table T0 in the image attached. I am running a SQL query on it to get the most-recent row for all players where the 'date' is <= (2020-08-14 - 7days). Not all players will have a row with a date that satisfies that condition, so naturally those playerId rows will not appear in the resulting table (Table T1 in the pic).

Now what I want to do is to include those missing rows with 0 values for 'score' and 'problems' in the resulting table (See Table T2 in the pic). I am totally at a loss as to how to go about it since I am very new to SQL queries.

Here's the part of the SQL query which is producing Table T1 from T0, but I want to modify it such that it produces Table T2 from T0:

select *
from (
   select *, row_number() over (partition by playerId order by date desc) as ranking
   from player
   where date<=date_add(date('2020-08-14'),interval -7 day)
) t
where t.ranking = 1

Answer 1

One option uses a subquery to list all the players, and then brings your current resultset with a left join:

select p.playerId, t.date, coalesce(t.score, 0) score, coalesce(t.problem, 0) problem
from (select distinct playerId from player) p
left join (
   select p.*, row_number() over (partition by playerId order by date desc) as rn
   from player p
   where date <= '2020-08-14' - interval 7 day
) t on t.playerId = p.playerId and t.rn = 1

If you have a referential table for all players, you can just replace the select distinct subquery with that table.