Query Optimisation: SUMIF and CASE WHEN

Question

I am writing a query to pull the number of rewarded ads. To do so, I have to sum "rewarded_ad" events from event_name. Then, I am cleaning up null values in a second statement. So currently, my query includes a subquery. However, is there a way to do this without utilising a subquery? I am looking to optimise this process.

My subquery includes:

SUM(IF(LOWER(event_name)= 'rewarded_ad', 1,0)) AS rewarded_ad

The outside query includes:

CASE WHEN rewarded_ad IS NULL THEN 0 ELSE rewarded_ad END AS rv_count

Any help is appreciated. Thank you in advance.

Answer 1

You can replace:

SUM(IF(LOWER(event_name)= 'rewarded_ad', 1,0)) AS rewarded_ad

with:

COUNTIF(LOWER(event_name) = 'rewarded_ad') AS rewarded_ad

This should never return NULL because COUNT() never returns NULL. So, this should answer your question with no subquery. But a NULL values can appear if the subquery returns no rows. To handle that, use COALESCE():

COALESCE(COUNTIF(LOWER(event_name) = 'rewarded_ad'), 0) AS rewarded_ad