How do I include results in my dataset when my WHERE clause filters them out?

I want to make a list of all the salespeople and count how many demos they've done in the last week.

I'm using mode analytics, so the {{@dummy_accounts}} and {{@active_sdrs}} are just shortcuts for subqueries. The first just gets a list of the account IDs that we don't want to count and the second is the SDRs who are currently working.

The challenge is that my filter takes away any SDR who has scheduled 0 demos in the last week. And I still want to include them in my result set, to show that they haven't scheduled any demos.

How do I get it so those SDRs who haven't scheduled demos are included?

SELECT full_name,
       count(activity_id) AS total_demos
FROM by_task
WHERE task_type LIKE 'Demo'
  AND created_date > DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK)
  AND account_id NOT IN
    (SELECT account_id
     FROM {{@dummy_accounts}})
  AND full_name IN
    (SELECT full_name
     FROM {{@active_sdrs}})
GROUP BY 1
ORDER BY 2 DESC

--Edit--

Here's what I want the dataset to look like...

full_name   total_demos
John Doe    5
Billy Bob   3
Ray Mac     2
Jose McGuerro   0
Joe MacDonald   0

But, here's what the query produces...

full_name   total_demos
John Doe    5
Billy Bob   3
Ray Mac     2

And here's a couple of rows that show the data structure...

activity_id task    date    full_name   account_id
324123ASe1Q Demo    2017-10-13  John Doe    aa912
324123ASe3F Demo    2017-10-13  John Doe    aa932
324123ASe8E Demo    2017-10-09  Billy Bob   aa933
324123ASe9A Demo    2017-10-08  Ray Mac aa999
324123ASe9A Demo    2017-10-09  Ray Mac aa993

The challenge is that the people without demos don't show up in this table.

Maybe I could do a union and just give them a task of Demo and just not give them any activity_ids so they don't count in the count? Not sure.

It would seem that you need a left join. I would guess:

SELECT a.full_name, count(t.activity_id) AS total_demos
FROM {{@active_sdrs}} a left join
     by_task t
     on t.full_name = a.full_name and
        t.task_type LIKE 'Demo'
        t.created_date > DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) and
        t.account_id NOT IN (SELECT da.account_id FROM {{@dummy_accounts}} da)
GROUP BY 1
ORDER BY 2 DESC;

The NOT IN is rather suspicious. It is more usually written as:

SELECT a.full_name, count(t.activity_id) AS total_demos
FROM {{@active_sdrs}} a left join
     by_task t
     ON t.full_name = a.full_name and
        t.task_type LIKE 'Demo' and
        t.created_date > DATE_SUB(CURRENT_DATE(), INTERVAL 1 WEEK) LEFT JOIN
     {{@dummy_accounts}} da
     ON t.account_id = da.account_id
WHERE da.account_id IS NULL
GROUP BY 1
ORDER BY 2 DESC