CODEWITHSUNDEEP
sqlamazon-redshift
janicebaratheon
janicebaratheon

Reputation: 999

How to count distinct in different time window in AWS redshift

SELECT count(DISTINCT(c.visitid)) as count1,
       t.prod
FROM x.t1 c
JOIN y.t1 t
    ON c.headingid = t.prod_heading_id
WHERE
        c.eventtimestamp  BETWEEN '2021-01-01' AND '2021-04-02'
    AND c.evaluation > 0    
GROUP BY t.prod
ORDER BY count1 DESC
LIMIT 100

I have anther time window from '2020-01-01' to '2020-04-02' and I want to do the same counting by group as count2.

Upvotes: 0

Views: 359

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can use conditional aggregation:

SELECT count(DISTINCT c.visitid) filter (where c.eventtimestamp  BETWEEN '2021-01-01' AND '2021-04-02') as cnt1,
       count(DISTINCT c.visitid) filter (where c.eventtimestamp  BETWEEN '2020-01-01' AND '2020-04-02') as cnt2,
       t.prod
FROM x.t1 c JOIN
     y.t1 t
     ON c.headingid = t.prod_heading_id
WHERE c.evaluation > 0 AND
      c.eventtimestamp  BETWEEN '2020-01-01' AND '2021-04-02'
GROUP BY t.prod
ORDER BY cnt1 DESC;

In Redshift (or many other databases), the syntax would be:

SELECT count(DISTINCT case when c.eventtimestamp  BETWEEN '2021-01-01' AND '2021-04-02' then c.visitid end) as cnt1,
       count(DISTINCT case when c.eventtimestamp  BETWEEN '2020-01-01' AND '2020-04-02' then c.visitid end) as cnt2,
       t.prod
FROM x.t1 c JOIN
     y.t1 t
     ON c.headingid = t.prod_heading_id
WHERE c.evaluation > 0 AND
      c.eventtimestamp  BETWEEN '2020-01-01' AND '2021-04-02'
GROUP BY t.prod
ORDER BY cnt1 DESC;

Upvotes: 1

Related Questions