How to pull the count of occurences from 2 SQL tables

I am using python on a SQlite3 DB i created. I have the DB created and currently just using command line to try and get the sql statement correct.

I have 2 tables.

Table 1 - users
user_id, name, message_count

Table 2 - messages
id, date, message, user_id

When I setup table two, I added this statement in the creation of my messages table, but I have no clue what, if anything, it does:

FOREIGN KEY (user_id) REFERENCES users (user_id)

What I am trying to do is return a list containing the name and message count during 2020. I have used this statement to get the TOTAL number of posts in 2020, and it works:

SELECT COUNT(*) FROM messages WHERE substr(date,1,4)='2020';

But I am struggling with figuring out if I should Join the tables, or if there is a way to pull just the info I need. The statement I want would look something like this:

SELECT name, COUNT(*) FROM users JOIN messages ON messages.user_id = users.user_id WHERE substr(date,1,4)='2020';

Solution

One option uses a correlated subquery:

select u.*,
    (
        select count(*) 
        from messages m 
        where m.user_id = u.user_id and m.date >= '2020-01-01' and m.date < '2021-01-01'
    ) as cnt_messages
from users u

This query would take advantage of an index on messages(user_id, date). You could also join and aggregate. If you want to allow users that have no messages, a left join is a appropriate:

select u.name, count(m.user_id) as cnt_messages
from users u
left join messages m 
    on m.user_id = u.user_id and m.date >= '2020-01-01' and m.date < '2021-01-01'
group by u.user_id, u.name

Note that it is more efficient to filter the date column against literal dates than applying a function on it (which precludes the use of an index).