SQL query for this scenario

Question

How would you write the query for this situation?
https://code.sololearn.com/WqSU5jHlcJ9V/?ref=app

Answer 1

I didn't test this but it would be something like:

SELECT
a.Id as Id,
a.Name as Name,
COUNT(b.AchievementId ) as Achievements
FROM
Fox a,
FoxAchievement b
WHERE
a.Id = b.Id
AND a.Gender = 'female'
AND b.AcquireDate <= 20190330;

Note: "b.AcquireDate <= 20190330" <- depends on the format the date field is held in. Most likely UNIX timestamp format so you would need to use a function to convert the value to human readable format, and to whatever appropriate date format you are using.