trying to split count of events before and after a cut off date

Question

I'm hoping someone can check my syntax here. I have a users table subscribers and an events table text_alert.

I would like the total number of text alerts sent to each user split by before and after 2017-07-07.

Here's my query:

  select
    subs.id as subscriber_id,
    count(t.id) as total_alerts_received,
    count(tbefore.id) as before_alerts_received,
    count(tafter.id) as after_alerts_received
  from subscriber subs
  left join text_alert t on t.subscriber_id = subs.id
  left join text_alert tbefore on tbefore.subscriber_id = subs.id and tbefore.create_date_time::date <= '2017-07-07'
  left join text_alert tafter on tafter.subscriber_id = subs.id and tafter.create_date_time::date > '2017-07-07'
  where subs.sms = 0
  group by subs.id

My expectation was that the sum of before and after alerts_received fields would match total_alerts_received. What's happening instead is that the 3 fields have the same value. If subscriber 010 has ten alerts, then both before and after fields also have 10 alerts.

To split before and after I used left joins on the fact table alerts.

Any help much appreciated.

Of course you are getting the same counts for each field (before, after, total). You have no condition on the queries to limit the counts, so all the counts will give you the number of rows returned from the main query. Basically... if you have 10 total alerts, 3 before and 7 after, then the query will give you three counts of the total number of rows (as a result of the left joins). Pseudocode: — Peter Abolins, Aug 09 '17 at 06:29
@PeterAbolins thanks for commenting. If you scroll to the right on my left joins I have conditions filtering them e.g. ```left join text_alert tbefore on tbefore.subscriber_id = subs.id and tbefore.create_date_time::date <= '2017-07-07'``` — Doug Fir, Aug 09 '17 at 06:32
@DougFir I saw that... but exactly what are you filtering? :) The filters on the joins apply to the entire query, not specifically to before_alerts_received or after_alerts_received. That was my point. — Peter Abolins, Aug 09 '17 at 06:34
@PeterAbolins OK, sounds like this is the culprit. Here's my justification, please advise how to correct. By creating a left join filtered on ```create_date_time::date <= '2017-07-07'``` I expected that I would be able to count the IDs of join ```tbefore``` where those ids are only for the specified date range. Since it's a left join I thought that the table being joined onto would be unafected by the filter, which is desirable since I want to retain all subscriber IDs in my output. How would you recommend I approach this? Is my goal clear? — Doug Fir, Aug 09 '17 at 06:38
Putting my goal another way, for each subscriber I would like the count of alerts they received both before and after '2017-07-07' — Doug Fir, Aug 09 '17 at 06:39
I would write this as: SELECT subs.id, subquery for before count (based on subs.id and a date), subquery for after count (based on subs.id and a date), total count. ...rather than a collection of left joins. I can add my suggestion as an answer. — Peter Abolins, Aug 09 '17 at 06:41
HI Peter yes please, I would be grateful if you could answer — Doug Fir, Aug 09 '17 at 06:41

score 2 · Accepted Answer · answered Aug 09 '17 at 07:15

2

select subscriber.id, 
count(text_alert.id) total_alerts_received, 
count(case when text_alert.create_date_time::date <= '2017-07-07' then 1 end) before_alerts_received,
count(case when text_alert.create_date_time::date > '2017-07-07' then 1 end) after_alerts_received
from subscriber
left join text_alert
on subscriber.id = text_alert.subscriber_id
where subscriber.sms = 0
group by subscriber.id

answered Aug 09 '17 at 07:15

Oto Shavadze

40,603
55
152
236

Thanks this does the trick and I can follow the logic of the query. Out of curiosity, did my original query look "close". I'm reading conflicting info from Peter and then this SO post which says you can filter on a table before left joining like I was doing: https://stackoverflow.com/questions/15077053/filter-table-before-applying-left-join – Doug Fir Aug 09 '17 at 07:21
1

@DougFir - I think if you add `distinct` keywords into your `count` functions, your query should also work. just if possible to avoid many times self joining, then better to try avoid. – Oto Shavadze Aug 09 '17 at 07:47

Peter Abolins · Answer 2 · 2017-08-09T07:05:35.063

0

SELECT 
    subscriberId, 
    beforeAlerts, 
    afterAlerts, 
    beforeAlerts + afterAlerts as totalAlerts
FROM (
    SELECT 
        DISTINCT subs.id as subscriberId, 
        (SELECT COUNT(*) FROM text_alert tb WHERE
            tb.subscriber_id = subs.id AND
            tb.create_date_time <= '2017-07-07') as beforeAlerts, 
        (SELECT COUNT(*) FROM text_alert ta WHERE
            ta.subscriber_id = subs.id AND
            ta.create_date_time > '2017-07-07') as afterAlerts
    FROM subscriber subs
) AS results;

edited Aug 09 '17 at 07:05

answered Aug 09 '17 at 06:48

Peter Abolins

1,520
1
11
18

Thanks Peter. This syntax is a little new to me. Right now I'm getting a syntax error, I'm using Postgres ```ERROR: syntax error at or near "SELECT" Position: 181 SELECT COUNT(*) FROM text_alert tb WHERE``` – Doug Fir Aug 09 '17 at 06:59
@DougFir Sorry - I missed the parentheses surrounding the sub-queries. – Peter Abolins Aug 09 '17 at 07:06
1

The query kept timing out, really appreciate you trying to help here. I tried Oto's answer and it works and runs in a few seconds. Thanks all the same – Doug Fir Aug 09 '17 at 07:19
@DougFir At least you got an answer that worked for you, and I got to learn something as well. I am guessing that your tables have a lot of records, which would explain the time-outs per my solution. – Peter Abolins Aug 09 '17 at 07:26

trying to split count of events before and after a cut off date

2 Answers2