CODEWITHSUNDEEP
mysqlsqlfiltergoogle-bigquerymode-analytics
Sebastian
Sebastian

Reputation: 967

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.

Upvotes: 0

Views: 416

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

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

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172993

Below is for BigQuery Standard SQL 

#standardSQL
WITH `by_task` AS (
  SELECT '324123ASe1Q' activity_id, 'Demo' task_type, DATE '2017-10-13' created_date , 'John Doe' full_name, 'aa912' account_id UNION ALL
  SELECT '324123ASe3F', 'Demo', DATE '2017-10-13', 'John Doe', 'aa932' UNION ALL
  SELECT '324123ASe8E', 'Demo', DATE '2017-10-09', 'Billy Bob', 'aa933' UNION ALL
  SELECT '324123ASe9A', 'Demo', DATE '2017-10-08', 'Ray Mac', 'aa999' UNION ALL
  SELECT '324123ASe9A', 'Demo', DATE '2017-10-09', 'Ray Mac', 'aa993' 
),
dummy_accounts AS (
  SELECT '1111' account_id UNION ALL 
  SELECT '2222' UNION ALL 
  SELECT '3333'
),
active_sdrs AS (
  SELECT 'John Doe' full_name UNION ALL
  SELECT 'Billy Bob' UNION ALL
  SELECT 'Ray Mac' UNION ALL
  SELECT 'Jose McGuerro' UNION ALL
  SELECT 'Joe MacDonald'
)
SELECT a.full_name, IFNULL(total_demos, 0) total_demos
FROM active_sdrs a 
LEFT JOIN (
  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
) t
ON t.full_name = a.full_name 
ORDER BY 2 DESC

As expected - output is 

full_name        total_demos     
-------------    -----------
John Doe                   2     
Ray Mac                    2     
Billy Bob                  1     
Jose McGuerro              0     
Joe MacDonald              0

Upvotes: 2

Related Questions