CODEWITHSUNDEEP
sqlhadoopsinglestore
user6575945
user6575945

Reputation:

Count Distinct Visits per user and grouping them

SELECT user_id, SUM(DISTINCT CASE WHEN click_date BETWEEN '2020-08-01' AND '2020-07-25' THEN 1 ELSE NULL END) total_visits_0to8_days,

SUM(DISTINCT CASE WHEN click_date BETWEEN '2020-07-26' AND '2020-07-18' THEN 1 ELSE NULL END) total_visits_8to14_days,

SUM(DISTINCT CASE WHEN click_date BETWEEN '2020-07-19' AND '2020-07-02' THEN 1 ELSE NULL END) total_visits_14to30_days,

SUM(DISTINCT CASE WHEN click_date BETWEEN '2020-07-03' AND '2020-06-02' THEN 1 ELSE NULL END) total_visits_30to60_days,

SUM(DISTINCT CASE WHEN click_date BETWEEN '2020-06-02' AND '2020-05-01' THEN 1 ELSE NULL END) total_visits_60to90_days

FROM my_table GROUP BY user_id

My query is returning NULL for all the groups where as I want an aggregate of visit counts per user DESIRED OUTPUT:

Sum of times each user id visited withing established time windows.

I am using COUNT DISTINCT in order to count each date only once so there should not be in total more than 93 visits per user from May 1st to Aug.1st if that makes sense? Thank you

Upvotes: 0

Views: 300

Answers (2)

forpas
forpas

Reputation: 164069

When you use BETWEEN a AND b you must make sure that a is <= than b, otherwise BETWEEN always returns FALSE and this is what happens in your code.
So reverse the dates in all cases.
Also use COUNT() instead of SUM() if you want to count the distinct dates:

SELECT user_id, 
  COUNT(DISTINCT CASE WHEN click_date BETWEEN '2020-07-25' AND '2020-08-01' THEN click_date END) total_visits_0to8_days,
  COUNT(DISTINCT CASE WHEN click_date BETWEEN '2020-07-18' AND '2020-07-26' THEN click_date END) total_visits_8to14_days,
  COUNT(DISTINCT CASE WHEN click_date BETWEEN '2020-07-02' AND '2020-07-19' THEN click_date END) total_visits_14to30_days,
  COUNT(DISTINCT CASE WHEN click_date BETWEEN '2020-06-02' AND '2020-07-03' THEN click_date END) total_visits_30to60_days,
  COUNT(DISTINCT CASE WHEN click_date BETWEEN '2020-05-01' AND '2020-06-02' THEN click_date END) total_visits_60to90_days
FROM my_table 
GROUP BY user_id

Upvotes: 1

Rahul Sawant
Rahul Sawant

Reputation: 1264

you should use date conversion for'click_date' is date . for oracle sample below

also notice first variable '2020-07-25' should be less than second '2020-08-01', yours is reverse.

click_date BETWEEN TO_DATE ('2020-07-25', 'yyyy/mm/dd')
               AND TO_DATE ('2020-08-01', 'yyyy/mm/dd');

Upvotes: 0

Related Questions