COUNT(*) FILTER (WHERE... In BigQuery

Question

How can I filter in a count in BigQuery?

In Postgres I was doing something like:

COUNT(*) FILTER (WHERE event_date>('{{ report_date }}'::date-INTERVAL '2 WEEK'))

Though this does not work in BigQuery. What't the best alternative? I use standard SQL.

Answer 1

For BigQuery Standard SQL, you can use COUNTIF and DATE_SUB functions as below

COUNTIF(event_date > DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK))  

You can test / play with above using below dummy example

#standardSQL
WITH `project.dataset.yourTable` AS (
  SELECT 1 AS event_id, DATE '2017-11-19' AS event_date UNION ALL
  SELECT 2, DATE '2017-11-18' UNION ALL
  SELECT 3, DATE '2017-10-15' 
)
SELECT 
  COUNT(*) AS all_events,
  COUNTIF(event_date > DATE_SUB(CURRENT_DATE(), INTERVAL 2 WEEK)) AS qualified_events 
FROM `project.dataset.yourTable`   

The output as one would expect is

all_events  qualified_events     
3           2    

Answer 2

Use case:

SUM(CASE WHEN event_date > '{{ report_date }}'::date-INTERVAL '2 WEEK' THEN 1 ELSE 0 END)

This is the "old-fashioned" way, before Postgres implemented the ANSI standard FILTER syntax.