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.
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
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.