CODEWITHSUNDEEP
sqlgoogle-bigquery
Mohan
Mohan

Reputation: 8843

SQL: condensing repeated COUNTIFs:

In this (BigQuery) query:

SELECT user_first_touch_date, 
   COUNT(*) as total,
   COUNTIF(active_days >= 1) as active_1d,
   COUNTIF(active_days >= 2) as active_2d,
   COUNTIF(active_days >= 3) as active_3d,
   COUNTIF(active_days >= 4) as active_4d,
   COUNTIF(active_days >= 5) as active_5d,
   COUNTIF(active_days >= 6) as active_6d,
   COUNTIF(active_days >= 7) as active_7d
FROM `device_event_count` 
GROUP BY user_first_touch_date
ORDER BY user_first_touch_date

I have a lot of boilerplate COUNTIF statements. (And if I wanted to look out to 14 days or 28 days it would be much worse.) Is there a way of writing this query more compactly?

Upvotes: 0

Views: 201

Answers (2)

AlienDeg
AlienDeg

Reputation: 1329

Inspired by Mikhail Berlyant's answer here I produced a query that produces a query that you need. Standard SQL of course.

with device_event_count as (select * from
(select '2015-08-01' as user_first_touch_date, 1 as active_days) union all
(select '2015-08-01' as user_first_touch_date, 2 as active_days) union all
(select '2015-08-01' as user_first_touch_date, 3 as active_days) union all
(select '2015-08-02' as user_first_touch_date, 1 as active_days) union all
(select '2015-08-02' as user_first_touch_date, 2 as active_days) union all
(select '2015-08-03' as user_first_touch_date, 1 as active_days) union all
(select '2015-08-03' as user_first_touch_date, 2 as active_days) union all
(select '2015-08-01' as user_first_touch_date, 5 as active_days) union all
(select '2015-08-01' as user_first_touch_date, 7 as active_days) union all
(select '2015-08-01' as user_first_touch_date, 3 as active_days) union all
(select '2015-08-02' as user_first_touch_date, 5 as active_days) union all
(select '2015-08-02' as user_first_touch_date, 6 as active_days) union all
(select '2015-08-03' as user_first_touch_date, 7 as active_days) union all
(select '2015-08-03' as user_first_touch_date, 3 as active_days) union all
(select '2015-08-01' as user_first_touch_date, 1 as active_days) union all
(select '2015-08-01' as user_first_touch_date, 2 as active_days) union all
(select '2015-08-01' as user_first_touch_date, 3 as active_days) union all
(select '2015-08-02' as user_first_touch_date, 1 as active_days) union all
(select '2015-08-02' as user_first_touch_date, 2 as active_days) union all
(select '2015-08-03' as user_first_touch_date, 1 as active_days) union all
(select '2015-08-03' as user_first_touch_date, 21 as active_days) union all
(select '2015-08-01' as user_first_touch_date, 53 as active_days) union all
(select '2015-08-01' as user_first_touch_date, 72 as active_days) union all
(select '2015-08-01' as user_first_touch_date, 3 as active_days) union all
(select '2015-08-02' as user_first_touch_date, 11 as active_days) union all
(select '2015-08-02' as user_first_touch_date, 62 as active_days) union all
(select '2015-08-03' as user_first_touch_date, 73 as active_days) union all
(select '2015-08-03' as user_first_touch_date, 34 as active_days) union all
(select '2015-08-03' as user_first_touch_date, 4 as active_days)
)
select concat('select user_first_touch_date, COUNT(*) as total, ' ,
string_agg( concat('COUNTIF(active_days >=',  safe_cast(active_days as string) , ') as active_' , safe_cast(active_days as string),'d'),','),' 
from `device_event_count` group by user_first_touch_date order by user_first_touch_date')
from (select active_days from device_event_count group by active_days order by active_days)

and here is what it returns:

select user_first_touch_date, COUNT(*) as total, COUNTIF(active_days >=1) as active_1d,COUNTIF(active_days >=2) as active_2d,COUNTIF(active_days >=3) as active_3d,COUNTIF(active_days >=4) as active_4d,COUNTIF(active_days >=5) as active_5d,COUNTIF(active_days >=6) as active_6d,COUNTIF(active_days >=7) as active_7d,COUNTIF(active_days >=11) as active_11d,COUNTIF(active_days >=21) as active_21d,COUNTIF(active_days >=34) as active_34d,COUNTIF(active_days >=53) as active_53d,COUNTIF(active_days >=62) as active_62d,COUNTIF(active_days >=72) as active_72d,COUNTIF(active_days >=73) as active_73 from `device_event_count` group by user_first_touch_date order by user_first_touch_date

result enter image description here

Upvotes: 2

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173013

the only workaround I see is to "automate" creation of your query - there are plenty examples here on SO of such - at least I know I provided some answers like this - you can do quick search. Roughly:
Option 1 - semi-manual - have a utility query that generates text of needed query with as many "redundant" lines as you need and then take output and run it as a query;
Option 2 - use client of your choice and have all run in one shot

Upvotes: 1

Related Questions